Try it
How Can We Help?

Search for answers or browse about Sintel Forms.

Creating Calculated Fields & using formulas

Posted
Updated
ByEoin McMahon
You are here:
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
< All Topics

Sintel Forms allow you to perform calculations using fields on the form such as getting the total value of all individual expenses on an expense claim form or calculating a total value of individual line items and adding sales tax to an invoice.

Field Types That Support Formulas

TextField Types That Can Be Used In Formulas

  • Text

  • Number/ Currency

  • Multiline text

  • Text

  • Number / Currency

  • Multiline text

  • Lookup

  • Choice

  • Yes/No 

Formulas can be added directly to both form fields and on fields within related sublists.

For regular form fields, using this feature requires just two steps.

  1. Step 1 Create a field that will be used as the calculated field (the 3 field types listed above)
  2. Step 2 Enable calculated values setting on that field and enter the formula to be used

Once the calculated values setting is enabled on a field is it will become read-only and this information will be displayed within the Sintel Forms Designer.

Calculated fields are recalculated after every field change on a form, however:

  1. They are not recalculated if the form is in the view mode.
  2. They are not recalculated if they are not present in the form layout. If you want to use calculated fields but not display them on a form we recommend you set the fields to hidden or place them into a hidden section.

Also, any kind of JavaScript expression can be used. For example, you can put a [“Hello”, “World”, “!”].join(” “) expression inside a calculated field. It will populate the field with the result of that expression, which will be “Hello World!”. However, take note that a field that has a calculated formula also has its own type as defined in the list meaning that:

  • If you apply this formula to a text field, it will give “Hello World!”
  • If you apply the same formula to a number/currency field it will give 0 (zero).

If you want to see a list of fields that can be used in a formula simply type a space followed by the @ character into the Formula field on the properties pane i.e. “ @”

refering.gif

Sample Formulas

  • 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

We have provided four functions (sum, avg, min, max) and all of them need to be written using a syntax similar to Microsoft Excel: functionName( <arguments go here> ).

Sample Functions

  • sum( {{Field1Name}}, {{Field2Name}}, {{Field3Name}} )

  • avg( {{Field1Name}}, {{Field2Name}} )

  • min( {{Field1Name}}, {{Field2Name}}, {{Field3Name}}, {{Field4Name}} )

  • max( {{Field1Name}}, {{Field2Name}}, {{Field3Name}} )

Referring to and using fields from a Sub-list

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.

Sublist.png

If your main list (the one in which Sintel Forms has been configured) has a column in which you want to display a calculated value using fields form the sub-list you can do so by using the following format: {{ListName_ColumnName}}

Using the sub-list example above these fields can be referred to using:

{{SubList_Title}}

{{SubList_Quantity}}

{{SubList_Unit_x0020_Price}}

{{SubList_Total}}

{{SubList__Count}} An extra bonus token that returns the number of rows in a sub-list

SubListTokens

[wptb id="3316" not found ]

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

  1. Step 1 Create a related sublist field (see “Configuring Related Sublists” article for details)
  2. Step 2 Make sure that the related sublist has fields that will be used as calculated ones (the 3 field types listed above)
  3. Step 3Enable calculated values on a related sublist column and enter the formula to be used:

    calculatedSublistColumns.jpg

There are slight differences in calculated fields defined on a sublist column level comparing to these defined on form level:

  • You are not able to reference fields from other sublists present on the form.
  • Referencing a field from the current sublist list will resolve it to that field value in each particular row. Example: for the related sub-list mentioned in the previous case, {{SubList_Unit_x0020_Price}} value will be resolved to “1” for the first-row item, “2” for the second-row item and “3” for the third-row item.

What is more, inside formulas for columns in related sublists:

  • You can use aggregate functions,
  • You can reference form-level fields.
Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents