Skip to main content
SecurityTrax

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]%%)

 

 

  • Was this article helpful?