Calculations can be made from dynamic values, static values and values from other fields. Calculations do not always have to be math equations. Just using values from other fields, or from global items like the current date or user name, is considered a calculation.


When doing calculations with fields, use simple JavaScript in place of the field's Default Value. To use the value from another field in your calculation, use the field name (case sensitive) enclosed in curly braces - { and }. For instance, if you have 2 text fields you wish to join - VAL1 and VAL2 - use this:


{{VAL1} {VAL2}}


This results in a single piece of text where the value from VAL1 is placed one space from the value in VAL2.


Performing Math

To do math with numbers, add operators between the values. For instance, if VAL1 and VAL2 hold numbers, you can do this to add them together:


{{VAL1}+{VAL2}}


If you are doing math, you can also use some built-in functions:


{cleanNumericValue('{VAL1}')} removes currency formatting characters and treats a blank value as the number 0.


{cleanDivision('{VAL1}', '{VAL2}')} does not error out when either value is blank or 0, but returns 0 instead.


Combine the 2 numeric functions for truly safe computations:


{cleanDivision(cleanNumericValue('{VAL1}'), cleanNumericValue('{VAL2}'))}


Supported Functions

  • Be sure to use { and } around all these function calls. For example, {replaceAll('abc', 'b', 'd')} is the proper way to use the replaceAll function.
  • Put single quotes around every value you pass to a function, including field values such as '{VAL1}' and lists such as '1^2^3'. Only a plain number that stands alone, such as a number of decimal places, goes without quotes. PROP and DSCALC are the exceptions and never use quotes.
  • These functions are run using JavaScript, so everything is case sensitive. Make sure to use the proper casing for your names.
  • All arrays are 0-based (zero-based), so the first item is number 0, the 2nd is number 1, and so on.


String Functions

replaceAll('instr', 'find', 'replace')
Replaces all instances of find with replace in instr. This function is NOT case sensitive.


stringContains('instr', 'containsStr', 'yesval', 'noval')
Returns yesval (or true) if instr contains containsStr, and noval (or false) if not. This function is NOT case sensitive. To see whether 2 fields are both filled in, check whether this returns no:
{stringContains('~{field1}~{field2}~', '~~', 'yes', 'no')}


stringLength('instr')
Returns the length of the text.


Number Functions

getNumValue('inval')
If inval is a number, it is returned. If not, inval is sent into the cleanNumericValue function to be cleaned to a number.


cleanNumericValue('instr')
Returns the number in instr, with currency symbols ($, £, €, ¥ and ¤) and thousands separators removed. A blank value, or text that does not start with a number, returns 0.


cleanDivision('val1', 'val2')
Divides val1 by val2, and returns 0 if either value is blank or 0. Text that is not a number gives NaN rather than 0, so pass field values through cleanNumericValue first.


tryParseFloat('value', 'default')
Tries to convert value to a number, ignoring $, £, €, ¥ and ¤ signs, thousands separators and percent signs. If it cannot, it returns default.


getFormattedNumber('value', places)
Formats value as a number with the given number of decimal places.


formatCurrency('value', places)
Formats value using USD currency formatting.


calcSum('Values', 'Delimiter')
Calculates the sum of the values in the incoming text.


    • Values - the text that holds the values to calculate, separated by a delimiter character.
    • Delimiter - the character to split the Values text on to get the numbers. The default is ^, because the {Table| |Column} syntax returns values separated by ^.


Examples:

{calcSum('1^2^3','^')} = 6

{calcSum('1^2^3')} = 6 (works since the default delimiter is ^)


calcAvg('Values', 'Delimiter')
Calculates the average of the values in the incoming text.


calcMax('Values', 'Delimiter')
Calculates the maximum of the values in the incoming text.


calcMin('Values', 'Delimiter')
Calculates the minimum of the values in the incoming text.


calcCount('Values', 'Delimiter')
Calculates the count of the values in the incoming text.


Date Functions

PLEASE NOTE: Date fields do not trigger changes until they lose focus, so if you are testing these functions with a date field, make sure to move your cursor out of the date field to see the results.


dateYear('date')
Returns the YEAR portion of the date.


dateMonth('date')
Returns the MONTH portion of the date.


dateDay('date')
Returns the DAY portion of the date.


dateAdd('date', years, months, days)
Adds the given number of years, months and days to the incoming date and returns the new date. Use 0 for any part you are not changing, and negative numbers to go backwards. Adding months to a day the new month does not have runs on into the month after, so January 31 plus one month lands in early March.


dateDiff('datefrom', 'dateto', 'interval')
Computes the number of intervals between datefrom and dateto. Allowed interval values are:

    • s, sec, second, seconds - number of seconds
    • n, min, minute, minutes - number of minutes
    • h, hour, hours - number of hours
    • d, day, days - number of days
    • m, mon, mons, month, months - number of months
    • y, yr, year, years - number of years

    • NOTE: For new calculations use dateDiff2, which counts whole intervals and is not thrown off by daylight saving time. dateDiff is kept exactly as it was so existing forms keep their results.


dateDiff2('datefrom', 'dateto', 'interval', partial)
Computes the number of whole intervals between datefrom and dateto. It takes the same interval values as dateDiff.

partial is optional. Leave it off to count only complete intervals, or use true to count an interval that has only started as a whole one. January 15 to March 20 is 2 months, or 3 with true.

The result is negative when datefrom is later than dateto, and is the same size either way.

Days, months and years are not affected by daylight saving time. Hours, minutes and seconds count the actual time that has passed.

A month counts once the same day comes round in the next month. When that month is too short for the day, its last day counts, so January 31 to February 28 is 1 month.

Years count complete years, so {dateDiff2('{BirthDate}', '[DATE]', 'y')} is an age that goes up on the birthday.


timeDiff('timefrom', 'timeto', 'interval')
Computes the number of intervals between timefrom and timeto. Allowed interval values are:

    • m - number of minutes
    • h - number of hours
    • anything else returns the time between them formatted as HH:MM

formatTime(hours, minutes, seconds)
Returns a formatted time. If the seconds value is included, the format is h:mm:ss. If it is not included, the format is h:mm.

JSON Functions

PROP(fieldname,property,operation)
NOTE: This function DOES NOT take quotes around its values. Parses a JSON value and returns the given property value.

    • fieldname - the name of the field that holds the JSON data. Do not use { } around it here or that will break the parsing - just put the name of the field.
    • property - the property of the JSON item to return. If the JSON is an object, just put the name here. If it is an array of items, use the expanded notation shown below.
    • operation - optional operation to take on the result. The available operation is length, which returns the length of the resulting text or array instead.


Single property: {PROP(JsonField,FirstName)} returns the value in the FirstName property of the JSON data.


Array property: {PROP(JsonField,2.FirstName)} returns the value in the FirstName property of the 3rd item in the array.


Advanced array property: {PROP(JsonField,People.1.FirstName)} returns the value in the FirstName property of the 2nd item in the People array.


DSCALC(Source,Function,Column,Filters)


Computes sum, count, avg, min or max over the rows of a Data Source field, optionally only the rows that match the filters. For example, {DSCALC(Invoices,sum,Amount,Paid=NO)} totals the unpaid invoices. See Calculating a statistic with DSCALC in the Data Source Field topic.


Field-Specific Variables

Some field types have a specific variable syntax to work with them.

Fields with Options

Fields that have options (Option List, Multi-Option, Radio Button and so on) have extra data available. Each option can have a Value, and can also have an Amount and a Text.

  • To get the VALUE, use {FIELDNAME}
  • To get the AMOUNT, use {FIELDNAME:AMOUNT}
  • To get the TEXT, use {FIELDNAME:TEXT}
  • To get the COUNT of options, use {FIELDNAME:COUNT}
  • To get the COUNT of SELECTED options, use {FIELDNAME:COUNTSELECTED}
  • To get the OPTIONS available, use {FIELDNAME:OPTIONS}
  • To get the INDEX (number offset) of an option by its value, use {FIELDNAME:INDEXOF:optionvalue}
  • To get the VALUE of an option by index (offset), use {FIELDNAME:VALUE:index}
  • To get the TEXT of an option by index (offset), use {FIELDNAME:TEXT:index}


For instance, if an option list named DeliveryCharge has Amount values, you can get those amounts using {DeliveryCharge:Amount}. To get a count of the delivery charge items, use {DeliveryCharge:Count}. To get the offset (index) of the option that has a value of 23, use {DeliveryCharge:INDEXOF:23}.


You can combine some of these calls. For instance, to get the text of the option with a value of 123, you could use this:

{DeliveryCharge:TEXT:{DeliveryCharge:INDEXOF:123}}


You can even use math in your calls. For instance, to get the value of the option AFTER the one selected, you could do this:

{DeliveryCharge:VALUE:{{DeliveryCharge:INDEXOF:{DeliveryCharge}}+1}}


NOTE: Amount and Text return a comma-separated list of the Amount and Text values for Multi-Option fields. To add up all the amounts, combine the Amount call with calcSum and give the comma as the delimiter - {calcSum('{DeliveryCharge:Amount}', ',')}. Without the comma the amounts are read as one run-together number.


Converting Option Lists to Table Data

The options can be converted to table data using the {FIELDNAME|table|^^^} syntax.

For instance, if you have an option list like this:

  • option 1: Value=1, Text=John Smith, Filter=X, Amount=100
  • option 2: Value=2, Text=John Adams, Filter=Y, Amount=200
  • option 3: Value=3, Text=Thomas Jefferson, Filter=Z, Amount=300


And a table with columns in this order:

  • Name
  • Number
  • Order


You could get the option list data extracted out into a format that you could use to add to the table directly. Just use {OptionField|table|Text^Amount^Value} and the result would look like this:

0| |John Smith^100^1^~0| |John Adams^200^2^~0| |Thomas Jefferson^300^3^~


You can use that text with the Set Field Value action to add those 3 options as rows to the end of the table.


MULTI-OPTION TIP: Multi-Option fields can be controlled with buttons and other action-oriented fields. Set the value of the Multi-Option field to the following values to check and uncheck items and more:

  • *ALL* = check all options in the list
  • *NONE* = uncheck all options in the list
  • *INVERT* = invert the checked and unchecked options (check all unchecked and uncheck all checked)
  • *CLEAR* = clear all options from the list


Multi-Option Buttons - You can turn on Buttons mode for a Multi-Option field in the Display settings. This changes the text next to the check boxes into buttons. If you have buttons turned on, you can also change how the Multi-Option operates. The button click can check the check box on and off like normal, or it can trigger a Multi-Action field, sending that button's value into the Multi-Action as the [DataValue]. You can configure the button action type in the Multi-Option's Advanced settings.


Built-In E-form Variables

There are several built-in variables you can use within E-forms. These MUST be capitalized to work properly.

  • {SCREENHEIGHT} - returns the height of the user's current view port. This is very useful for adapting container fields to the viewable area of the user's browser.
  • {PAGENUMBER} - returns the number of the currently displayed page of the E-form. Always returns 1 on a single-page form.
  • {TOTALAMOUNT} - returns the total amount for the E-form when the fields in the form come to a total amount, like an invoice or receipt. Does not include tax.
  • {TOTALTAXABLEAMOUNT} - returns the total amount for the E-form. If the tax value is set to 0 (zero), this is the same as the {TOTALAMOUNT} value.
  • {TOTALTAX} - returns the total amount of the tax for the E-form. If the tax value is set to 0 (zero), this is always 0.
  • {TOTALWITHTAX} - returns the total amount for the E-form including the tax.
  • {TAXRATE} - returns the rate used in the form for computing tax.
  • {WFID} - returns the ID of the current work item.
  • {WFTRIGGERNAME} or {WORKTRIGGERNAME} - returns the Name of the current work item's work trigger.
  • {WFTRIGGERID} or {WORKTRIGGERID} - returns the ID of the current work item's work trigger.

IMPORTANT NOTE: Be sure to use { and } around any text that needs to be calculated!

Checking Field Validation Status

If a field has validation rules like REGEX applied, you can check whether it passes its rules by using {Field:Valid}. This returns "true" if valid and "false" if not.

Using Computed Values in Saved Fields

IMPORTANT: If you want to compute a value in a field type that saves data (Text, Text Area, Number and so on), set its Where to Get Initial/Latest Value setting in the Data settings to Do Not Set Value / Compute Value. Otherwise the computation is lost while generating a new form or when using the Use Latest Template options.

3. E-form Calculation Examples — one paragraph under Things to watch with math

Current text:

cleanDivision returns 0 if either number is 0, blank or not a number. That is what makes it safe, but it also means a division that legitimately involves 0 shows 0 rather than an error.

Replacement:

cleanDivision returns 0 if either number is 0 or blank. That is what makes it safe, but it also means a division that legitimately involves 0 shows 0 rather than an error. Text that is not a number gives NaN instead, which is one more reason to pass every field through cleanNumericValue first.