Calculated Fields in Forms

Calculation formulas can be added to form fields in order to perform mathematical equations, concatenations, and pre-fill data in fields, among other actions that will be discussed in this article.

In this box, you can place a snippet of JavaScript that contains field names (i.e. export labels of other fields within the form) prefixed by "@", and when the form is being filled out, the contents of this field will be automatically calculated based on the formula you enter.

For example, say you have a field on your form with an export label of "number_of_brothers", and another field with an export label of "number_of_sisters".  You can then add a new text field (Number of Siblings) with a Calculation Formula of

@number_of_brothers + @number_of_sisters

and this new field will automatically update with the sum of the other two fields.  It will change in real time as those fields are changed.  (What actually is happening is that we construct a javascript function that applies this formula, and we add it as an event handler to each field that is mentioned in your formula.)  If the field being referenced is a system field, it will have an export label of "sys:field:field_name" (for person-scoped fields) or "sys:app:field_name" (for application-scoped fields).

Important!

If a calculation, like this one, uses numeric operations, then the fields where the values are being entered (e.g. "number_of_brothers" and "number_of_sisters" in the above example) must be given a numeric type (either "Int" or "Real" under Data Type).  If you see your values being treated as strings (e.g. "2" + "3" = "23" instead of 5) then you've probably forgotten this step.

The snippet entered is calculated using JavaScript, so the rules for what type of math you can use will be based on JavaScript math options.  The basic operators will be +, -, * (multiplication), and / (division). You can group operations using parentheses.  For more complicated operations such as rounding or raising a number to a power, you can use the JavaScript Math object. Other JavaScript operators such as the ternary conditional (e,g. "(@score > 10) ? 'You did well' : 'You could do better") will also work. Note that, since the calculation is done in real-time using JavaScript that runs in the user's web browser, a user could inspect the source code of the page and see the formula. If the calculation formula needs to be secret, then a different approach (such as a default value formula or a form rule) would need to be used (but those approaches cannot update in real time as values change in the form).

  What about using a caret (^) to raise a number to a power?

In JavaScript, a caret (^) represents an XOR operation, or an exclusive OR gate. Slate interprets the export key as a binary value and compares it to the value after the caret. To raise a number to a power, use the Math.pow() function instead.

We've recently added an alternate approach to complex calculations, in which you can use hidden form fields combined with conditional logic. 

If you need to do multi-step operations, you may want to add some hidden calculated fields that can act as sub-totals and then be referenced in other calculated fields.

Prompt GUID Example

Calculated fields will work with prompt-based form fields. If the form field is mapped to a system field that uses a prompt, the value returned will be the GUID of the selected prompt. For example, if you have a form field that is mapped to a field called "sys:app:app_major", and you make a calculation formula that uses that field, the value that is substituted will be the GUID. So you could do, for example

(@sys:app:app_major == '5b50b962-d9e6-42a5-95f0-1f48ad4b7eef' ||
@sys:app:app_major == 'f0547208-679f-45b3-aa46-83f5244425a5') ? 'Hard Science' : 'Humanities'

That is, if the select prompt is one of those two GUIDs, the calculated value will be "Hard Science", otherwise it will be "Humanities".

However, this is pretty messy. So we have another option. If you add "_text" to the name of the field in the formula, you'll get the prompt name instead of the GUID. So instead of the above formula, you could do

(@sys:app:app_major_text == 'Physics' ||
@sys:app:app_major_text == 'Chemistry') ? 'Hard Science' : 'Humanities'
Blank Fields

If you use a numeric field in a calculation, and the field is blank, we'll report the value as 0. This is good for many situations because if your formula is, say, @number_of_brothers + @number_of_sisters, and one of those is blank, the calculation would give an error if it tried to calculate 1 + <blank>.

However, there are some situations where you want to know if it's blank. In that case, you can add "_exact" to the end of the field name in your formula, and we'll return the exact value without translating a blank to a zero.

For example, if you're taking the average of a set of fields, you might sometimes want to count a blank as a zero, but for other cases, you might want to leave a blank field out of the average altogether. For that scenario, you can use "_exact" and then check for a non-blank value.

There are a number of ways to calculate an average, but here's one (if we imagine that you have four fields called rating1, rating2, rating3, rating4:

var sum=0; var count=0; $.each([@rating1_exact, @rating2_exact, @rating3_exact, @rating4_exact],
function(){if (!isNaN(this)){sum += this; count += 1}}); sum/count

This is looping through the four fields, accumulating a total sum and a total count of the ones that contain an actual number, and then displaying the sum divided by the count.

Formatted Fields

Forms with formatted fields can be used in calculations as Slate will remove special characters when calculating a field. In this example we will add two fields with a currency format.

Input Fields

Create your input fields on the form that are to be totaled. Be sure to:

  • Export - Assign an export key
  • Data Type - Set the data type to real
  • Format Mask - Create a format mask appropriate for the field. 

Output Field

Create a third field that will calculate these values together to get a total dollar amount. Use the export keys from the previously created fields in the Calculation Formula setting. Be sure to:

  • Export - Assign an export key
  • Data Type - Set the data type to real
  • Format Mask - Create a format mask appropriate for the field. 

Once configured, the calculated results will display as shown below:

form_calc4.png

Multi-Valued Fields


For form fields that can have multiple selected values, such as a list of checkboxes, when multiple values are selected the formula will return a comma-separated list of the items.

mceclip0__2_.png

If I add a field to show Selected Colors with a formula of @colors, it will show the comma-separated list:

However, if I want to use that field in a numeric calculation, often what I care about is the number of selected items. To allow this type of calculation, I can add "_list" to the end of my export key, and the result will be a list of items (in JavaScript terms, it will return an array). That will allow me to get the length.

Here, the calculation formula has been changed to: @colors_list.length

mceclip5.png

This allows a formula, for example, that will calculate a price by multiplying the number of selected items by the price per item.

You can use any JavaScript function that works on arrays, so, for example, to get a list with a pipe separator instead of a comma, you could use a formula of @colors_list.join("|") to get a result of "Green|Purple|Hot Pink".

Date Fields

Current Date

You may wish for a date to default to a specific day like "today." You can accomplish this by using a Default Value Formula.

  1. Edit the form field
  2. Check off/enable Default Value Formula
  3. Click Export
  4. Search for and add "Current Date"
  5. Configure the following:
    • Name - of "today" (you can choose whatever)
    • Format Type - "Date"
    • Format Mask - "yyyyMMdd"

      inline-73543742.png
  6. Click Save
  7. Formula - Type in @today

    inline-925915497.png
Checking if a Field Contains a Value

Static Value

You may wish to check if a form field contains a static value. For instance, checking if someone's email address contains your institution's email domain. If we are collecting email addresses using the Record > Email Address system field setting, which has the export key of sys:email, we would make a check like this:

@sys:email.includes('@technolutions.com')

Dynamic Value

You may wish to check if a form field contains a dynamic value, such as a value in another form field. For instance, checking if someone's email address contains their first name. If we are collecting email addresses using the Record > Email Address system field setting, which has the export key of sys:email, and collecting first names using the Record > First Name system field setting, which has the export key of sys:first, we would make a check like this:

@sys:email.includes(@sys:first)
Grabbing Address Block Data

An address block collects an address' street, city, region, country, and postal code. You can grab those different elements of an address using the following syntax:

Address Element Suffix Example
Street .street
@sys:address_block.street
City .street
@sys:address_block.street
Region .region
@sys:address_block.region

Note. Regions for the United States and Canada will turn the abbreviated region code (e.g., "FL" for Florida and "NB" for New Brunswick ). Regions for other countries will return the region spelt out (e.g., "Milan")
Country .country
@sys:address_block.country

Note. This will return the two character abbreviation for the selected country (e.g., "US" for the United States).
Postal .postal
@sys:address_block.postal

 Tip

We used @sys:address_block in our example above, but you will want to replace this with the export key for the address block you are referencing.

Was this article helpful?
11 out of 19 found this helpful