Payable Formulas
Payable Formulas provide a way to create more specific type of payable calculations by implementing Excel-like formulas as a Line Item within SecurityTrax. Payable Formulas can be combined with Flat Rate Line Items.
Supported Excel Functions
Supported Excel Functions are used in conjunction with the Dynamic Payroll Content list below. Additional Functions will be added as support becomes available.
| Function | Description |
|---|---|
| ABS |
The ABS function returns the absolute value of a number. In other words, the ABS function removes the minus sign (-) from a negative number, making it positive.
|
| AND |
The AND function tests the condition that is specified and returns TRUE, if conditions are met as TRUE, or it returns FALSE if conditions are not met.
|
| IF |
The IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.
|
| IFERROR |
The IFERROR function is designed to trap and manage errors in formulas and calculations. More specifically, IFERROR checks a formula, and if it evaluates to an error, returns another value you specify; otherwise, returns the result of the formula.
|
| IFNA |
The IFNA function returns a custom result when a formula generates the #N/A error, and a standard result when no error is detected. IFNA is an elegant way to trap and handle #N/A errors specifically without catching other errors.
|
| ISNUMBER |
The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not.
|
| OR |
The OR function returns TRUE if any of the conditions are true and returns FALSE if all conditions are false.
|
| ROUND |
The ROUND function returns a number rounded to a given number of digits. The ROUND function can round to the right or left of the decimal point.
|
| ROUNDDOWN |
The ROUNDDOWN function returns a number rounded down to a given number of places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDDOWN rounds all numbers down.
|
| ROUNDUP |
The ROUNDUP function returns a number rounded up to a given number of decimal places. Unlike standard rounding, where numbers less than 5 are rounded down, ROUNDUP rounds all numbers up.
|
| SEARCH |
The SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
|
| SUMPRODUCT |
SUMPRODUCT is a function that multiplies range of cells or arrays and returns the sum of products. It first multiplies then adds the values of the input arrays.
|
Dynamic Payroll Content
Using Dynamic Payroll Content enables you to pull values found elsewhere in SecurityTrax to be used in your payable calculations. There are two 'sets' of Dynamic Payroll Content; one set for data related to the Customer, and another set for data related to a Calendar Event.
The table immediately below provides all of the supported Dynamic Payroll Content for Customers.
Example:
=IF(%%[customer][getActivationFeeAmount]%%) >= 50, (%%[customer][getEquipmentTotalTechBonus]%%) * 1.3, (%%[customer][getEquipmentTotalTechBonus]%%))
This formula says IF the Activation Fee for the Customer is greater than or equal to $50 then multiple the Total Equipment Tech Bonus by 1.3. If the Activation Fee is less than 50 then just use the Total Equipment Tech Bonus.
| Dynamic Content for Customer | Description |
|---|---|
| (%%[customer][getActivation_fee_id]%%) |
|
| (%%[customer][getActivationFeeAmount]%%) |
|
| (%%[customer][getCity]%%) |
|
| (%%[customer][getEquipmentTotalCharge]%%) |
|
| (%%[customer][getEquipmentTotalChargeNotPaidFor]%%) |
|
| (%%[customer][getEquipmentTotalChargePaidFor]%%) |
|
| (%%[customer][getEquipmentTotalCost]%%) |
|
| (%%[customer][getEquipmentTotalDevices]%%) |
|
| (%%[customer][getEquipmentTotalInstallationCost]%%) |
|
| (%%[customer][getEquipmentTotalItemsSoldByUserId]%%) |
|
| (%%[customer][getEquipmentTotalPoints]%%) |
|
| (%%[customer][getEquipmentTotalTechBonus]%%) |
|
| (%%[customer][getInstallTechID]%%) |
|
| (%%[customer][getLead_company_campaign_id]%%) |
|
| (%%[customer][getLead_company_id]%%) |
|
| (%%[customer][getMmr_id]%%) |
|
| (%%[customer][getMmrAmount]%%) |
|
| (%%[customer][getMonitoring_plan_id]%%) |
|
| (%%[customer][getRep_id]%%) |
|
| (%%[customer][getState]%%) |
|
| (%%[customer][getZip]%%) |
|
The table immediately below provides all of the supported Dynamic Payroll Content for Calendar Events.
Example:
=IF(%%[customer][getActivationFeeAmount]%%) >= 50, AND (%%[calendar_event][isTechAppointment]%%) = "Tuesday", (%%[customer][getEquipmentTotalTechBonus]%%) * 1.3, (%%[customer][getEquipmentTotalTechBonus]%%)))
This formula says IF the Activation Fee for the Customer is greater than or equal to $50 AND it's a Tuesday, then multiple the Total Equipment Tech Bonus by 1.3. If the Activation Fee is less than 50 AND it's not a Tuesday, then just use the Total Equipment Tech Bonus.
| Dynamic Content for Calendar Event | Description |
|---|---|
| (%%[calendar_event][getDurationAtAppointment]%%) |
|
| (%%[calendar_event][getEquipmentTotalCharge]%%) |
|
| (%%[calendar_event][getEquipmentTotalChargeNotPaidFor]%%) |
|
| (%%[calendar_event][getEquipmentTotalChargePaidFor]%%) |
|
| (%%[calendar_event][getEquipmentTotalCost]%%) |
|
| (%%[calendar_event][getEquipmentTotalDevices]%%) |
|
| (%%[calendar_event][getEquipmentTotalInstallationCost]%%) |
|
| (%%[calendar_event][getEquipmentTotalItemsSoldByUserId]%%) |
|
| (%%[calendar_event][getEquipmentTotalPoints]%%) |
|
| (%%[calendar_event][getEquipmentTotalTechBonus]%%) |
|
| (%%[calendar_event][getUser_id]%%) |
|
| (%%[calendar_event][isSalesAppointment]%%) |
|
| (%%[calendar_event][isTechAppointment]%%) |
|