Payable Formulas

Last updated
Save as PDF

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.
Affiliate Id	The Affiliate Id is the Id of the Affiliate that is listed on the Customer Page in the Sale Information Section.
Alt Sales Representative ID	The Alt Sales Representative Id is the Id of the Alt Sales Representative that is listed on the Customer Page in the Sale Information Section.
Bill Type ID	The Bill Type ID is the ID of the Default Payment Method that is listed in the Payment Methods, under the Accounting Section on the Customer.
Contract Term ID	The Contract Term Id is the Id of the Contract Term that is listed on the Customer Page in the Sale Information Section.
Contract Term Months	The Contract Term Months is the number of months designated by the Contract Term that is listed on the Customer Page in the Sale Information Section.
Credit Score	The Credit 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 Par	The Equipment Total Par is the accumulated Par Value of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Par 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 Par Value 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 Retail Value	The Equipment Total Retail Value is the accumulated Retail Values of all Customer Equipment, which is listed on the Customer Page in the Equipment Section. To find the Retail 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 Accounting Information section and you will see Retail 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.
Guard Response	Guard Response is either True or False. This is based on the Guard Response slider, which is located on the Customer Page in the Sale Information Section.
Guard Response Fee	The Guard Response Fee is the Fee associated with the Guard Response. This is located on the Customer Page, in the Contacts section, under the Fee field in Guard portion of Agency Contacts.
Invoicing	Invoicing is either True or False. This is based on the Invoicing slider, which is located on the Customer Page in the Sale Information Section.
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.
Most Recent Funding Amount Expected	The Most Recent Funding Amount Expected is the Amount Expected that is listed under the Most Recent Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the most recent funding on the list.
Most Recent Funding Amount Received	The Most Recent Funding Amount Received is the Amount Received that is listed under the Most Recent Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the most recent funding on the list.
Moist Recent Funding Funder ID	The Most Recent Funding Funder ID is the ID of the Funder that is listed under the Most Recent Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the most recent funding on the list.
Most Recent Funding Funder Reason ID	The Most Recent Funding Funder Reason ID is the ID of the Funder Reason that is listed under the Most Recent Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the most recent funding on the list.
Most Recent Funding Funding Type ID	The Most Recent Funding Funding Type ID is the ID of the Funding Type that is listed under the Most Recent Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the most recent funding on the list.
Office Location ID	The Office Location ID is the ID of the Office Location listed on the Customer, under the Customer page in the Customer Information section.
Oldest Funding Amount Expected	The Oldest Funding Amount Expected is the Amount Expected that is listed under the Oldest Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Oldest Funding Amount Receive	The Oldest Funding Amount Received is the Amount Received that is listed under the Oldest Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Oldest Funding Funder ID	The Oldest Funding Funder ID is the ID of the Funder that is listed under the Oldest Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Oldest Funding Funder Reason ID	The Oldest Funding Funder Reason ID is the ID of the Funder Reason that is listed under the Oldest Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Oldest Funding Funding Type ID	The Oldest Funding Funding Type ID is the ID of the Funding Type that is listed under the Oldest Funding on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Rebate Check Amount	The Rebate Check Amount is the Amount listed in the Rebate Check Amount field listed on the Customer, under 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.
Total Funding Amount Expected	The Total Funding Amount Expected is the accumulated Amount Expected total of all Fundings listed on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Total Funding Amount Received	The Total Funding Amount Received is the accumulated Amount Received total of all Fundings listed on the Customer. This is located on the Customer's Accounting Page in the Payments section under Funding. Once selected, it is the oldest funding on the list.
Warranty Days	The Warranty Days are the number of Days associated with the Warranty Term that is listed on the Customer, under the Customer page in the Sale Information section.
Warranty Term ID	The Warranty Term ID is the ID of the Warranty Term that is listed on the Customer, under 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.
Is Install Appointment	Is Install Appointment is either True of False for the selected Appointment.
Is Service Appointment	Is Service Appointment is either True of False for the selected Appointment.
Travel Time For Appointment	Travel Time For Appointment is the Time in Seconds between when a tech selects "On My Way" and "Checked In" for the selected Appointment.