Payable Formulas
Enable the Payable+ Add-on to utilize a series of easy-to-use tools for creating complex formulas to calculate sales and tech commissions. Contact your SecurityTrax Account Manager for more details.
Payable Formulas provide a way to create specific payable calculations by implementing Excel-like Formulas into the Line Item of a Function within SecurityTrax.
Note: Payable Formulas can be combined with Flat Rate Line Items.
Excel Functions
Excel Functions are used in conjunction with the Payable Values below. Additional Functions will be added as support for them 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. Example:
|
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. The AND function is most often used with the IF function. Example:
|
IF |
The IF function returns one value if the condition is TRUE, or another value if the condition is FALSE. Example:
|
ISNUMBER |
The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Example:
|
OR |
The OR function returns TRUE if any of the conditions are true and returns FALSE if all conditions are false. Example
|
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. Example:
|
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. Example:
|
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.
|
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. Example:
|
Payable Values
Payable Values enable you to pull values found elsewhere in SecurityTrax to be used in your payable calculations. There are two 'sets' of Payable Values content; one set for data related to the Customer, and another set for data related to a Calendar Event.
When building a Payable Formula for a Sales Job Function you will only see Payable Values for Customer. If you are building a Technician type Job Function then you will see Payable Values from both the Customer and Calendar Event.
Payable Values - Customer | Description |
---|---|
Activation Fee Amount |
The Activation Fee Amount is the value of the Activation Fee that is listed on the Customer Page in the Sale Information Section.
|
Beacon Score |
The Beacon Score is the Credit Score listed on the Customer Page in the Customer Information Section.
|
Customer City |
The Customer City is the City listed on the Customer Page in the Customer Information Section.
|
Customer State |
The Customer State is the State listed on the Customer Page in the Customer Information Section.
|
Customer Zip |
The Customer State is the State listed on the Customer Page in the Customer Information Section.
|
Equipment Total Charge |
The Equipment Total Charge is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section.
|
Equipment Total Charge Not Paid For |
The Equipment Total Charge Not Paid For is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section, that has not been marked as Paid.
|
Equipment Total Charge Paid For |
The Equipment Total Charge Paid For is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section, that has been marked as Paid.
|
Equipment Total Cost |
The Equipment Total Cost is the accumulated Per Unit Cost of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Per Unit Cost of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Accounting Information section and you will see Per Unit Cost listed.
|
Equipment Total Devices |
The Equipment Total Devices is the accumulated Device Count of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Device Count of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sale & Installation Information section and you will see Device Count listed.
|
Equipment Total Installation Cost |
The Equipment Total Installation Cost is the accumulated Installation Cost of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Installation Cost of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sales & Installation Information section and you will see Installation Cost listed.
|
Equipment Total Points |
The Equipment Total Points is the accumulated Point Value of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Point Value of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sale & Installation Information section and you will see Point Value listed.
|
Equipment Total Tech Bonus |
The Equipment Total Tech Bonus is the accumulated Installation Bonus of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Installation Bonus of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sales & Installation Information section and you will see Installation Bonus listed.
|
Install Tech ID |
The Install Tech ID is the User ID of the Technician who did the first (or initial) Installation for the Customer. Typically this is the User ID of the Technician who has the oldest Appointment on the Customer.
|
Mmr Amount |
The Mmr Amount is the Monthly Charge listed on the Customer Page in the Sale Information Section.
|
Monitoring Plan ID |
The Monitoring Plan ID is the ID of the Monitoring Plan listed on the Customer Page in the Sale Information Section.
|
Sales Representative ID |
The Sales Representative ID is the the User ID of the Sales Representative listed on the Customer Page in the Sale Information Section.
|
Source Company ID |
The Source Company ID is the ID of the Source Company listed on the Customer Page in the Sale Information Section. To find the Source Company ID you will need to go to the Integrations Section under Company Settings. From there you will select Partner Companies and then select the specific Source Company (Partner Company) you are looking for. Once selected, the ID is listed at the top of the page next to the name of the Source
|
Source Company Campaign ID |
The Source Company Campaign ID is the ID of the Source Company Campaign listed on the Customer Page in the Sale Information Section.
|
Payable Values - Calendar Event | Description |
---|---|
Appointment Duration |
The Appointment Duration is the length of time of the selected Appointment. This is located on the Appointments Page on the Customer. Simply view the Duration from the Appointment preview or select the appointment and view the Duration.
|
Appointment Equipment Total Charge |
The Appointment Equipment Total Charge is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section that is associated with the selected Appointment.
|
Appointment Equipment Total Charge Not Paid For |
The Appointment Equipment Total Charge Not Paid For is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section, that has not been marked as Paid and that is associated with the selected Appointment.
|
Appointment Equipment Total Charge Paid For |
The Appointment Equipment Total Charge Not Paid For is the accumulated Unit Price (or Charge) of all Customer Equipment, which is listed on the Customer Page in the Equipment Section, that has not been as Paid and that is associated with the selected Appointment.
|
Appointment Equipment Total Cost |
The Appointment Equipment Total Cost is the accumulated Per Unit Cost of all Customer Equipment, which is listed on the Customer Page in the Equipment Section and is associated with the selected Appointment. To find the Per Unit Cost of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Accounting Information section and you will see Per Unit Cost listed.
|
Appointment Equipment Total Devices |
The Appointment Equipment Total Devices is the accumulated Device Count of all Customer Equipment, which is listed on the Customer Page in the Equipment Section and is associated with the selected Appointment. To find the Device Count of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sale & Installation Information section and you will see Device Count listed.
|
Appointment Equipment Total Installation Cost |
The Appointment Equipment Total Installation Cost is the accumulated Installation Cost of all Customer Equipment, which is listed on the Customer Page in the Equipment Section and is associated with the selected Appointment. To find the Installation Cost of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sales & Installation Information section and you will see Installation Cost listed.
|
Appointment Equipment Total Points |
The Appointment Equipment Total Points is the accumulated Point Value of all Customer Equipment, which is listed on the Customer Page in the Equipment Section and is associated with the selected Appointment. To find the Point Value of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sale & Installation Information section and you will see Point Value listed.
|
Appointment Equipment Total Tech Bonus |
The Equipment Total Tech Bonus is the accumulated Installation Bonus of all Customer Equipment, which is listed on the Customer Page in the Equipment Section and is associated with the selected Appointment. To find the Installation Bonus of a piece of equipment you will need to go to the Equipment Section under Company Settings. From there you will select Equipment and then select the specific piece you are looking for. Once selected, go to the Sales & Installation Information section and you will see Installation Bonus listed.
|
Appointment Tech ID |
The Appointment Tech ID is the User ID of the Technician who is listed as the Technician on the selected Appointment.
|