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 + .

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:

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.

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

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.

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:

✨ 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.

Calculating Age from Date of Birth

If you are looking to determine the age based on the Date of Birth field inputted on a form, you can do so by adding in a calculation formula to a new Text Box

• Export - Add in an export key

• Data Type - Set the data type to Int

• Calculation Formula - Add the following to the Calculation Formula row

(@sys:birthdate ? (Math.floor(((new Date())-@sys:birthdate)/(365.25*24*60*60*1000))) : 0);