A field can be calculated based on dynamic values and other fields. Calculations do not always have to be math equations. Just using values form other fields or form global items like the current date or username is considered a calculation.


When doing calculations with fields, you need to use simple JavsaScript in place of the default value property. If you want to use the values from other fields in your calculation then use the field name (case sensitive) enclosed in curly braces - { and }. For instance, if you have 2 string fields you wish to concatenate - VAL1 and VAL2 - use this:


{{VAL1} {VAL2}}


This will result in a single string where the value from VAL1 is placed one space from the value in VAL2.



Fields with Validation

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




Performing Math

To do math with numbers, you can add operators to your value. For instance, if VAL1 and VAL2 are number values you can to this to add them together:


{{VAL1}+{VAL2}}



If you are doing math then you can also leverage some built-in functions:


{cleanNumericValue(strValue)} = This function will remove currency formatting characters and will treat a blank/null value as the number 0.


{cleanDivision(val1, val2)} = This function will not error out when using 0 in either value but will instead return a 0 value.



Combine the 2 numeric functions for truly safe computations:

{cleanDivision(cleanNumericValue(val1), cleanNumericValue(val2))}




Supported Functions:


* NOTE: 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.


* NOTE: These functions are run using JavaScript so there are a few things to keep in mind:

  • Everything is case sensitive so 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
  • NOTE: Notice that you have to enclose the parameters in single quotes


stringContains('instr', 'containsStr', 'yesval', 'noval') 

  • Returns true or yesval if "instr" contains containsStr and false or noval if not
  • This function is NOT case sensitive
  • NOTE: Notice that you have to enclose the parameters in single quotes


USE: To see if 2 fields are filled in use {stringContains('~{field1}~{field2}~', '~~', 'yes', 'no')} equals no.


stringLength('instr') 

  • Returns the length of the string
  • NOTE: Notice that you have to enclose the parameters in single quotes



Number Functions


getNumValue ('inval') 

  • If "inval" is a number then it is returned. If not, instr is sent into cleanNumericValue function to be cleaned to a number


cleanNumericValue ('instr') 

  • Removes all non-numeric characters from the incoming string


cleanDivision('val1', 'val2') 

  • Divides val1 by val2 and if either value is null, 0 or non-numeric, it returns 0


tryParseFloat('value', 'default') 

  • Tries to convert value to floating point number and if it cannot then it returns the default value


getFormattedNumber('value', places) 

  • Formats the incoming value as a number with the specified number of decimal places


formatCurrency('value',places) 

  • Formats the value using USD currency formatting 


calcSum('Values, 'Delimiter') 

  • Calculates the Sum of the values in the incoming string
      • Values - The string that has the values to calculate. Must be delimited by a specific character.
      • Delimiter - The character to split the Values string on to get the numbers. Default is ^ because using the {Table| |Column} syntax returns values separated by ^.
      • Examples:
        • {calcSum('1^2^3','^')} = 6
        • {calcSum('1^2^3')} = 6 (works since default delimiter is ^)

calcAvg('Values, 'Delimiter') 

  • Calculates the Average of the values in the incoming string

calcMax('Values, 'Delimiter') 

  • Calculates the Maximum of the values in the incoming string

calcMin('Values, 'Delimiter') 

  • Calculates the Minimum of the values in the incoming string

calcCount('Values, 'Delimiter') 

  • Calculates the Count of the values in the incoming string




Date Functions

PLEASE NOTE: Date fields do not 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 "days" days to the incoming date and returns the


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



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 a string formatted as HH:MM


formatTime(hours, minutes, seconds) 

  • Returns a formatted time string
    • If the "seconds" parameter is included then the format is h:mm:ss
    • If the "seconds" parameter is NOT included then the format is h:mm




JSON Functions


PROP(fieldname,property,operation) 

  • NOTE: This function DOES NOT require quotes around its parameters! 
  • Parses a JSON value and returns the given property value
      • fieldname - The name of the field that holds the JSON data. DO NOT use { } syntax here or that will break the parsing. Just put the name of the field here.
      • property - This is property of the JSON item to return. If the JSON is an object then you can just put the name here. If it is an array of items then you can use an expanded notation.
        • Single property: {PROP(JsonField,FirstName)} will return the value in the 'FirstName' property of the JSON data.
        • Array property: {PROP(JsonField,2.FirstName)} will return the value in the 'FirstName' property of the 3rd item in the array.
        • Advanced array property: {PROP(JsonField,People.1.FirstName)} will return the value in the 'FirstName' property of the 2dn item from the 'People' array.
      • operation - Optional operation to take on the result of the parse. Possible operations are:
        • length - Changes the PROP command to return the length of the resulting string or array.





Field-Specific Variables:

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



Fields with Options

Fields that support single-entry options (Option List, Multi-Option, Radio Button, etc.) have extra data available. Each option can have a Value but can also have an Amount or Text. To get the VALUE, use the {FIELDNAME} syntax as normal.


    • 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}. If you want 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, if you want 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 will return comma-delimited list of Amount and Text values for Multi-Option fields. You can combine the Amount call with calcSum to get the sum of all amounts. 




Converting Option Lists to Table Data

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


For instance, of 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 string 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 un-check items and more:

    • *ALL* = Check all options in the list
    • *NONE* = Uncheck all options in the list
    • *INVERT* = Invert the checked/unchecked options (i.e., 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-Action field in the Display settings. This will change the text next to the check boxes into buttons. If you ave buttons turned on, you can also change how the multi-option operates. The button click can check the checkbox on and off like normal. Or it can trigger a multi-action field, send 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. Will always return 1 on a single-page form.


{TOTALAMOUNT}: Returns the total amount for the E-form when using the fields in the form to 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) then this will be 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) then this will always be 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 need calculated!




IMPORTANT NOTE ABOUT USING COMPUTED VALUES IN SAVED FIELDS

If you want to compute a value in a field type that saves data (i.e. Text, Text Area, Number, etc.) then you need to make sure to set its "Data" / "Where to Get Initial/Latest Value" option to "Do Not Set Value / Compute Value" otherwise the computation will be lost while generating a new form or using the Use Latest Template options.