Creating Calculated Fields & Using Formulas

Sintel Forms allows you to perform calculations using form fields, such as summing individual expenses in a claim form or calculating totals with sales tax on an invoice.

Fields That Can Contain Formulas

• Text
• Number / Currency
• Multiline Text

Fields That Can Be Used in Formulas

• Text
• Number / Currency
• Multiline Text
• Lookup
• Choice
• Yes/No

Formulas can be applied to both standard form fields and fields within related sublists.

Setting Up a Calculated Field

To use a calculated field, follow these steps:

1. Create a field that will store the calculated value (must be Text, Number, or Currency field type).
2. Enable the calculated values setting on that field and enter the required formula.

Once a field is set as a calculated field, it becomes read-only, and this will be indicated within the Sintel Forms Designer.

How Calculated Fields Behave

• They update automatically whenever a field on the form changes.
• They do not update when the form is in view mode.
• They do not update if they are not included in the form layout. If you need to use calculated fields without displaying them, set them as hidden or place them in a hidden section.

You can also use JavaScript expressions in formulas. For example:

[“Hello”, “World”, “!”].join(” “)

This will populate the field with “Hello World!”.

⚠️ Important: The calculated field’s type affects the formula’s output. If you apply the above formula to a text field, it returns “Hello World!”, but if applied to a number/currency field, it returns 0.

To see a list of available fields while writing a formula, type a space followed by @ in the Formula field on the properties panel.

Sample Formulas

Basic Arithmetic

• Adding Fields: {{Field1Name}} + {{Field2Name}}
• Subtracting Fields: {{Field1Name}} – {{Field2Name}}
• Multiplying Fields: {{Field1Name}} * {{Field2Name}}
• Dividing Fields: {{Field1Name}} / {{Field2Name}}
• Calculating Percentages: {{Field1Name}} * 0.23

Example

Using the ‘Yes/No’ field type in the calculation formula.

( {{YesNoFieldName}} ? "This is True value" : "This is False value")

Aggregate Functions

Sintel Forms provides four aggregate functions: sum, avg, min, and max.  These follow a syntax similar to Microsoft Excel:

• Sum: sum( {{Field1Name}}, {{Field2Name}}, {{Field3Name}} )
• Average: avg( {{Field1Name}}, {{Field2Name}} )
• Minimum: min( {{Field1Name}}, {{Field2Name}}, {{Field3Name}}, {{Field4Name}} )
• Maximum: max( {{Field1Name}}, {{Field2Name}}, {{Field3Name}} )

Using Fields from a Sub-list in Formulas

So far all the examples refer to fields on the form but you can also refer to fields on a related sub-list.

Example

Consider a form that contains a related sub-list with the following columns Title, Quantity, Unit Price and Total.

 

Enabling Formulas on a Sub-list

To set up calculated fields within a sub-list:

1. Create a related sub-list field (see the “Configuring Related Sublists” article for details).
2. Ensure the sub-list has fields that will be used as calculated fields (Text, Number, or Currency field types).
3. Enable calculated values on a sub-list column and enter the formula.

Enabling the formulas on related sublist level requires the following steps: Formulas on related sublist level

Differences Between Form-Level and Sublist-Level Calculations

• Sub-list formulas cannot reference fields from other sub-lists on the form.
• Sub-list fields resolve per row, meaning:
  • {{SubList_Unit_x0020_Price}} in Row 1 will return 1.
  • The same field in Row 2 will return 2.
  • The same field in Row 3 will return 3.

Additionally, within a sub-list formula:

• Aggregate functions can be used.
• Form-level fields can be referenced.