Overview
Formulas define how your Spec Based Estimate Template (SBET) calculates materials, labor, and costs. This article outlines the supported form field types, formulas and how they operate, common errors you may encounter, and best practices to help you create accurate and reliable formulas.
Who uses this feature
Administrators, managers, and field managers
Primarily for Residential Roofing Service & Replacement
Feature configuration
Account configuration is required to use this feature. Please contact Technical Support for details.
Things to know
The column headers cannot contain spaces. Use the original import template as a reference when preparing your file. For example, you can write the ParentTag column exactly as "ParentTag" (no space). If you enter it as "Parent Tag" with a space, it won't be recognized and the logic won't apply correctly.
In the QuantityFormulaByName column, put form field names inside brackets [ ]. For example, if the form field is called Squares, write it as [Squares]. This column is where you set up the calculation for how many items go on the estimate. For example, if you use [Squares]*3, the system takes the number entered in Squares and multiplies it by three to get the quantity.
Formulas are not case-sensitive. Uppercase and lowercase letters are treated the same. For example, IF, if, If, and iF all work the same way.
Formula and Operations
In step 4 of the Spec Based Template creation flow, you need to set up your estimates using formulas. The table below lists the available formulas and operations you can use. These pre-set options let you build calculations that determine quantities and pricing automatically.
Section | Description | Examples |
|---|---|---|
Supported Calculations | Addition | + |
Subtraction | - | |
Multiplication | * | |
Division | / | |
IF Function | IF ([form field name]. value if true, value if false) | Example: IF([Partial Replacement] = 1, 0) |
Nummerical Comparisons in IF Function | Greater than | > |
Less than | < | |
Equal to | = | |
Not equal to | ! = | |
Less than or equal to | < = | |
Greater than or equal to | > = | |
Rounding with ROUNDUP | ROUNDUP ([form field name], decimal space) | Example: ROUNDUP([Ridge Vent]/30, 0) |
Supported Decimal Space | 0 decimal place | Example: ROUNDUP(5.75, 0) → 6 |
1 decimal place | Example: ROUNDUP(5.75, 1) → 5.8 | |
Between Function | Checks if a value is between two numbers, including the numbers themselves. | Example: Between([Partial Replacement], 5, 10) → true if [Partial Replacement] is 5, 6, 7, 8, 9, or 10 |
Null Function (Only for dropdown/ radio buttons) | Checks if a value is empty or has no data. | Example:
|
More Complex formulas | Customers can create complex conditional formulas. | Example:
|
Supporting Quotation Answers in Dropdown & Radio Button | This is for dropdown & radio button text answers | Examples:
|
Supported Form Field Types
Within the formula builder, there are four supported form field types that you can use to create your formulas:
Number: Field responses must be numeric. For example, 300 or 500.
Text: You can enter text and it can be used in formulas but only within IF statements.
Dropdown: You can select a dropdown answer that is a text-based option. These new fields can be used in formulas, but only within IF statements. For example, IF([drip edge] = "yes", 14, IF([drip edge] = "no", 10, 10 + 10)).
Radio Button: You can select a radio button answer that is text. These new fields can be used in formulas, but only within IF statements. For example, IF([drip edge] = "yes", 14, IF([drip edge] = "no", 10, IF([drip edge] = "", 0, 20))).
Ways to create SBET
There are three different options of creating Spec Based Estimate Templates (SBETs) from the office side or the mobile side. These are the following options:
Option 1: Create SBET Estimate from the Job screen
Option 2: Create SBET Estimate from the Project screen
Option 3: Create SBET Estimate from Mobile
Option 1: Create SBET estimate from the Job screen
Search for the job number and open the job page.
On the job screen, click Job Actions > Add an Estimate.
In the Build Estimate(s) pop-up, click Spec Based Template.
Select the template from the list and click Next.
From the Select Measurement Option pop-up, select an existing measurement or manual input and click Next.
Note: If you select Existing Measurements, some measurements pre-populate on your form. If you select Manual Input, you need to input all measurements manually.
On the Form, fill out the relevant data and click Save.
Option 2: Create Spec Based Estimate from the Project screen
Open the project screen and click Actions > Build Estimate.
From the Build Estimate pop-up, select an existing opportunity or click Create New Opportunity.
In the Create New Opportunity screen, select the details, click Spec Based Template to select one and click Done.
On the Form, fill out the relevant data and click Save.
Option 3: Create SBET Estimate from Mobile
Tap Overview on the dashboard, and then tap View Current Job.
Tap Forms to select the form and fill out the measurements on the form.
When finished, tap Save.
Error Code list
See common errors you may encounter when creating formulas. Each message explains what went wrong and how to fix it before trying again.
Invalid SKU Code Name
Code not found: On the (Rules) tab, in row ({row.Row}) the value ‘{data.SkuCodeName}’ in SkuCodeName is not recognized in the system. Check the CodeName or add the item to the Pricebook before trying again.
Invalid SKU ID
[{data.SkuId.Value}] does not exist in Pricebook or the code/name does not match Pricebook code/name. Make sure to add the item first to Pricebook before using it in Roofing Templates.
Proposal Type Not Found
ProposalType not found: On the (Rules) tab, in row ({row.Row}) the value ‘{data.ProposalType}’ in ProposalType is not used for this Input Based Proposal. Check the spelling before importing again.
Duplicate Section/Proposal Type
Too many entries for this section/proposal type: On the (Rules) tab for the ({data.SectionName}) section and ({data.ProposalType}) proposal type. We only support 1 item per proposal type and section combination. Please update your file and try again.
Duplicate SKU Type
Too many sku types: On the (Rules) tab for the ({data.SectionName}) section and ({rule.LineItemType.Value}) sku type. We only support 1 sku type per section. Please update your file and try again.
Missing Proposal Type
Proposal Type is required: On the (Rules) tab, in row ({row.Row}).
Missing Quantity Formula Name
QuantityFormulaByName is required: On the (Rules) tab, in row ({row.Row}).
Input Field Not Found
Input Field not found: On the (Rules) tab, in row ({row.Row}) the value ‘{variableName}’ in QuantityFormulaByName is not in the input source. Check the spelling before importing again.
Input Field Not Unique
Input Field is not unique: On the (Rules) tab, in row ({row.Row}) the value ‘{variableName}’ in QuantityFormulaByName can be referring to more than one item in the input source. Use QuantityFormulaById to refine to the specific field.
Unsupported Calculation
Calculation not recognized: On the (Rules) tab, in row ({row.Row}) in QuantityFormulaByName, there are calculations that are not supported. The only formulas supported are +, -, *, /, roundup(), if().
Unsupported Field Type in Forms
EX: Row: 21 Form Field “SQ Low Slope” form field has an incorrect field type.
When a price modifier is in an item group
Price modifiers cannot be added to item groups: On the (Rules) tab, in row (3). SkuCodeName (percentdiscount) SkuId () ItemGroupName (GroupContainingPriceModifier).
When the parent of an item group isn't a service/equipment
Only service and equipment items can be parents of an item group: On the (Rules) tab, in row (4). SkuCodeName (percentdiscount) SkuId () ItemGroupName (GroupWithPriceModifierAsRoot).
When no ParentTag is specified for a group and there are no services or equipment in the item group
No valid item group parent found: On the (Rules) tab, the group ‘GroupWithNoValidParentIdentified’ has no service or equipment item which could be the item group parent.