Creating Calculated Fields & using formulas

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 total value of individual line items and adding sales tax to an invoice.

Field Types That Support Formulas Field Types That Can Be Used In Formulas
  1. Text
  2. Number / Currency
  3. Multiline text
  1. Text
  2. Number / Currency
  3. Multiline text
  4. Lookup
  5. Choice

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 will be displayed within the Sintel Forms Designer as shown.

FieldWithFormula

 

  Note

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. “ @”

 

Sample Formulas
  1. Adding Fields

    {{Field1Name}} + {{Field2Name}}

  2. Subtracting Fields

    {{Field1Name}} - {{Field2Name}}

  3. Multiplying Fields

    {{Field1Name}} * {{Field2Name}}

  4. Dividing Fields

    {{Field1Name}} / {{Field2Name}}

  5. Calculating Percentages

    {{Field1Name}} * 0.23

 

Aggregate functions

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

Sample Functions
  1. sum( {{Field1Name}}, {{Field2Name}}, {{Field3Name}} )
  2. avg( {{Field1Name}}, {{Field2Name}} )
  3. min( {{Field1Name}}, {{Field2Name}}, {{Field3Name}}, {{Field4Name}} )
  4. 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

  Note

If you wish to refer to a sub-list field in a formula you must wrap it in quotation marks.

Sample Sub-List Formulas
  1. To sum values from the “Total” column

    sum( {{SubList_Total}} )

  2. To get the min/max value from the “Unit Price” column

    min( {{SubList_UnitPrice} ) or max( {{SubList_UnitPrice} )

  3. To sum values from two different lists

    sum( {{SubList_Total}} ) + sum ({{SubList2_OtherColumn}})

Formulas on related sublist level

Enabling the formulas on related sublist level requires following steps:

  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 3

    Enable 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 is 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 0 found this helpful