Skip to main content
SecurityTrax

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: 

Screen Shot 2020-08-04 at 5.18.25 PM.png

 

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:

Screen Shot 2020-08-05 at 1.59.45 PM.png

 

IF

The IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.

Example:

Screen Shot 2020-08-05 at 2.14.13 PM.png

 

ISNUMBER

The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not.

Example:
Screen Shot 2020-08-05 at 3.19.30 PM.png

 

OR

The OR function returns TRUE if any of the conditions are true and returns FALSE if all conditions are false.

Example

Screen Shot 2020-08-05 at 3.25.21 PM.png

 

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:

Screen Shot 2020-08-05 at 3.47.44 PM.png

 

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:
Screen Shot 2020-08-05 at 3.48.43 PM.png

 

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.

Screen Shot 2020-08-05 at 3.49.15 PM.png

 

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:
Screen Shot 2020-08-05 at 3.13.35 PM.png

 

 

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. 

 

  • Was this article helpful?