Field Formula Syntax

The field formula is an expression that returns a single value. A value can be of various types: number, real, boolean, string, date, date only, time only. It can also be the built-in constant value null to represent an empty field value.

When applying operators on two values of different type one value is converted into a compatible type, e.g. "Label " + 123 results in the string "Label 123" because the second value is convertted to string type before applying the operator.

In case of an error the field value is set according to the user's settings. Otherwise the resulting value is automatically converted to the type of the corresponding field to set the value.

The arithmetic operators are a subset of the ones already known from programming languages such as C, Java, or JavaScript.

Expressions can be separated by comma to evaluate to the last expression of this sequence the same way as in C, Java, or JavaScript. This is useful for preceding the actual expression returning the formula value with function call expressions that set variable values.

Formal Syntax

Here is the syntax in BNF: 
<expression> ::= <conditional expression>

<conditional expression> ::= <conditional or expression> | <conditional or expression> ? <expression> : <conditional expression>

<conditional or expression> ::= <conditional and expression> | <conditional or expression> || <conditional and expression>

<conditional and expression> ::= <equality expression> | <conditional and expression> && <equality expression>

<equality expression> ::= <relational expression> | <equality expression> == <relational expression> | <equality expression> != <relational expression>

<relational expression> ::= <additive expression> | <relational expression> < <additive expression> | <relational expression> > <additive expression> | <relational expression> <= <additive expression> | <relational expression> >= <additive expression>

<additive expression> ::= <multiplicative expression> | <additive expression> + <multiplicative expression> | <additive expression> - <multiplicative expression>

<multiplicative expression> ::= <unary expression> | <multiplicative expression> * <unary expression> | <multiplicative expression> / <unary expression> | <multiplicative expression> % <unary expression>

<unary expression> ::= + <unary expression> | - <unary expression> | <primary>

<primary> ::= <literal> | ( <expression> ) | <function invocation>

<function invocation> ::= <function name> ( <argument list>? )

<argument list> ::= <expression> | <argument list> , <expression>

Functions

The formula syntax support a number of functions to be integrated into the expression.
Each function returns a value.
The function name is matching ignoring the case so you can call a function itembyindex() or - for better readability - itemByIndex()

General Functions

min (a, b [, c [ ,d ...]])
Returns the minimum value of the parameters given which need to be of a comparable type.
You can pass two or more parameters to be compared.
See min() for another usage of this function to aggregate field values of sub-table items.

Examples:
 min(fieldValue("Image Width"), fieldValue("Image Height")) returns the length of the smaller dimension of the image size.

max (a, b [, c [ ,d ...]])
Returns the maximum value of the parameters given which need to be of a comparable type.
You can pass two or more parameters to be compared.
See max() for another usage of this function to aggregate field values of sub-table items.

Examples:
 max(fieldValue("Image Width"), fieldValue("Image Height")) returns the length of the larger dimension of the image size.
 max(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Download"), "Date") returns the date of the last time this asset was downloaded.

sum (a, b [, c [ ,d ...]])
Returns the sum of all parameters passed which need to be integer or real numbers.
You can pass two or more parameters to be summed up.
See sum() for another usage of this function to aggregate field values of sub-table items.

var (string [, value])
Gets or sets the value of a variable. The first parameter is the name of the variable.
To get the value you just specify the variable name and the function will return the variable's value or null if it is not yet set.
You set the variable to a new value by specifying the value as the second parameter. The function then also returns this new value.
Typically variable setter functions are preceding the actual formula value expression using commas a separators.
You can use variables to store values that you want to use multiple times in the formula. This will improve the readability as well as the performance if the variable stores a value that would be expensve to generate like a field value.

Examples:
 var("notes", fieldValue("Notes")), var("status", fieldValue("Status", true)), var("notes") + "\n" + var("status")  sets two variables before using their values for the formula.
 var("status", fieldValue("Status")), var("status") == 1 ? "open" : var("status") == 2 ? "closed" : "unknown"  sets a variable before using it multiple times.  var("comments", subTableItems("AssetRecords/{04a4080f-fec0-4e01-822f-b9125c0b5ac9}")), count(var("comments")) == 0 ? "no comment" : "number of comments: " + count(var("comments"))  sets a variable before using it multiple times.

Integer Functions

All of the following functions return an integer value.
integer (value)
Converts the given value to an integer and returns it.A real value will be truncated.

Examples:
 integer(12.3456)  returns the integer value 12.

String Functions

All of the following functions return a string value.
string (value [, format])
Returns the given value as a string. The optional format parameter string can be used to specify the format.Depending on the value type the format string can contain the following:

Integer and Real: a format specifier as it is used for the POSIX printf function.

Examples:
 string(1234, "%8d") returns "    1234"
 string(1.2345678, "%8.2f") returns "    1.23"

Date: a format string to convert to local time of the server with the following placeholders:
 YYYY  the year in 4 digits
 YY    the year in 2 digits
 MM    the month in 2 digits
 M     the month in 1 or 2 digits
 DD    the day in 2 digits
 D     the day in 1 or 2 digits
 hh    the hour in 2 digits
 h     the hour in 1 or 2 digits
 mm    the minute in 2 digits
 m     the minute in 1 or 2 digits
 ss    the second in 2 digits
 s     the second in 1 or 2 digits
 AM/PM use 12 hour conversion adding "AM" or "PM" as appropriate
 am/pm use 12 hour conversion adding "am" or "pm" as appropriate

Examples:
 string(now(), "MM/DD/YYYY hh:mm:ss AM/PM")  could convert to "04/17/2007 07:25:34 AM"

length (string)
Returns the number of characters in the string parameter given.

subString (string, startIndex [, count])
Returns a part of the string given. The part is specified by the zero-based starting index and an optional count for the number of characters. If the count is not given or exceeds the number of characters in the string at the starting index the function returns all characters beginning at the starting index.

Examples:
 subString(fieldValue("Record Name"), 1)  returns the field value of the given field starting with the second character.

indexOf (string, subString, [, startIndex])
Searches in the given string for the given sub string and returns the zero-based position of it.
The start of the search can be specified by the optional zero-based start index.
If the sub string is not contained in the string the function returns -1.

Examples:
 indexOf(fieldValue("Version History"), "Final") != -1 ? 1 : 0  returns a value of 1 if the record name contains the string "Final". Otherwise it returns the value 0.

lastIndexOf (string, subString, [, startIndex])
Searches in the given string backwards for the given sub string and returns the zero-based position of it.
The start of the search can be specified by the optional zero-based start index.
If the sub string is not contained in the string the function returns -1.

Examples:
 lastIndexOf(fieldValue("Asset Name"), ".") != -1 ? subString(fieldValue("Asset Name"), lastIndexOf(fieldValue("Asset Name"), ".") + 1) : ""  returns the file name extension of the asset (without the period).

replace (string, oldstring, newstring)
Replaces all occurrences of one string with another in a given string and returns the result.
In the given string every occurrence of oldstring is replaced by the given newstring.
The case that oldstring is contained in newstring is handled correctly.

toLower (string)
Returns the given string converted to all lower case.

toUpper (string)
Returns the given string converted to all upper case.

Date Functions

All of the following functions return a date value.
now ()
Returns the current date and time as a date value.

date (string)
Converts the given string parameter into a date value and returns it.
The string parameter needs to be of the format 
"YYYY-MM-DD hh:mm:ss"
where
YYYY  is the year given in four digits
MM    is the month given in two digits (01..12)
DD    is the day of month given in two digits (01..31)
hh    is the hour given in two digits of a 24-hour-clock (00..23)
mm    are the minutes given in two digits (00..59)
ss    are the seconds given in two digits (00..59).

Examples:
 date("2006-08-24 17:34:12") 

startOfMinute ([date])
Return the date value that represents the start of the minute of a given date.
If the optional date parameter is missing the current date and time is used.

startofhour ([date])
Return the date value that represents the start of the hour of a given date.
If the optional date parameter is missing the current date and time is used.

startOfDay ([date])
Return the date value that represents the start of the day of a given date.
If the optional date parameter is missing the current date and time is used.

startOfWeek ([date])
Return the date value that represents the start of the week of a given date.
If the optional date parameter is missing the current date and time is used.

startOfMonth ([date])
Return the date value that represents the start of the month of a given date.
If the optional date parameter is missing the current date and time is used.

startOfYear ([date])
Return the date value that represents the start of the year of a given date.
If the optional date parameter is missing the current date and time is used.
Examples:
 startOfYear () + years (1)  returns the date value representing 1st of January of next year 00:00:00.

Time Span Functions

All of the following functions return a time span value that can be used to be added to or subtracted from a date value to form another date value.
seconds (number)
Return the time span that represents the given number of seconds.
This function is most useful to do calculations on date field values to add or subtract a given number of seconds.

Examples:
 now() - seconds(30)  returns a date value representing 30 seconds back from now.

minutes (number)
Return the time span that represents the given number of minutes.
This function is most useful to do calculations on date field values to add or subtract a given number of minutes.

Examples:
 now() - minutes(30)  returns a date value representing 30 minutes back from now.

hours (number)
Return the time span that represents the given number of hours.
This function is most useful to do calculations on date field values to add or subtract a given number of hours.

Examples:
 now() - hours(6)  returns a date value representing 6 hours back from now.

days (number)
Return the time span that represents the given number of days.
This function is most useful to do calculations on date field values to add or subtract a given number of days.

Examples:
 now() - days(2)  returns a date value representing 2 days back from now.

weeks (number)
Return the time span that represents the given number of weeks.
This function is most useful to do calculations on date field values to add or subtract a given number of weeks.

Examples:
 now() - weeks(1)  returns a date value representing 1 week back from now.

months (number)
Return the time span that represents the given number of months.
This function is most useful to do calculations on date field values to add or subtract a given number of months.
When used in a date calculation this function respects the different number of days per month.

Examples:
 now() - months(6)  returns a date value representing 6 months back from now.

years (number)
Return the time span that represents the given number of years.
This function is most useful to do calculations on date field values to add or subtract a given number of years.
When used in a date calculation this function respects the leap years.

Examples:
 now() - years(1)  returns a date value representing 1 year back from now.

Date Only Functions

The following function returns date only value.
dateOnly (string)
Converts the given string parameter into a DateOnly value as used by the corresponding field type in Cumulus.

The string parameter must be of the format YYYY-MM-DD where

YYYY  is the year given in four digits (e.g. 2006)
MM    is the month given in 1 or two digits (e.g. 12). If the given month is 0 the DateOnly value is interpreted as the whole given year.
DD    is the day of moth given in one or two digits (e.g. 1). If the given day is 0 the DateOnly value is interpreted as the whole given month or year.

For DateOnly value calculation you can also use DateOnly values. For example you can add a whole month to a DateOnly value using the following:

  dateOnly("2006-12-22") + dateOnly("0-1-0") will result in the DateOnly value 2007-01-22

You can also add or subtract a number of days like this:

  dateOnly("2006-12-22") + 100 will result in the DateOnly value 2007-04-01.

Sub-Table Functions

To work on sub-table items Cumulus provides functions to get collections of items as well as an item in a collection.
With a collection of items you can then call other functions to aggregate the data, e.g. min(), max(), sum(), average(), median(), or count().
subTableItems (table_name [, query [, sort_field_list [, sort_directions_list [, startIndex [, count]]]]])
Returns a collection value containing sub-table items of the item where this formula is defined.
The table_name specifies the name of the sub-table to use.
You can specify a query to find the items you want to work on. An empty string finds all items that depend on the current item where the formula is defined.
The sort_field_list is a string that contains the fields to sort the collection by, separated by comma. Passing an empty string does not sort the collection.
The sort_directions_list is also a string that contains the sort directions for each of the fields specified in sort_field_list. You can specify a list of "ascending" or "descending" separated by comma. The default is "ascending".
Specifying startIndex returns a collection that starts at the given offset. The default is 0 which starts at the beginning.
Specifying count returns a collection that only contains a number of items up to the given limit. The default is not to limit the number of items.
The value of this function cannot be directly used as a field value. You need to call one of the aggregating functions to get a proper field value.

Examples:
 fieldValue("User", itemByIndex(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Download", "Date", "descending"), 0)) returns the user ID of the user who downloaded this asset last.
 count(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == 'Check Out'")) returns the number of checkout operations performed with this asset.
min (collection, field_specifier)
Returns the minimum of the field values of the items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
The field_specifier parameter determines the field to process. It uses the same syntax as described in the fieldValue() function.
See min() for another usage of this function to determine the minimum of values given directly as parameters.

Examples:
 min(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Preview"), "Date") returns the first date that this asset was previewed.

max (collection, field_specifier)
Returns the maximum of the field values of the items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
The field_specifier parameter determines the field to process. It uses the same syntax as described in the fieldValue() function.
See max() for another usage of this function to determine the maximum of values given directly as parameters.

Examples:
 max(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Download"), "Date") returns the date of the last time this asset was downloaded.

sum (collection, field_specifier [, separator])
Returns the sum of the field values of the items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
The field_specifier parameter determines the field to process. It uses the same syntax as described in the fieldValue() function.
The optional separator parameter allows to specify a separator to be automatically inserted between two string values.
See sum() for another usage of this function to determine the sum of values given directly as parameters.

Examples:
 sum(subTableItems("AssetRecords/{aceea098-8984-4e07-8868-417ccddeaddf}"), "Price") may calculate the sum amount earned by selling this asset (assuming the sub-table exists and has the corresponding values).

average (collection, field_specifier)
Returns the average value of the field values of the items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
The field_specifier parameter determines the field to process. It uses the same syntax as described in the fieldValue() function.

Examples:
 average(subTableItems("AssetRecords/{aceea098-8984-4e07-8868-417ccddeaddf}"), "Price") may calculate the average amount earned by selling this asset (assuming the sub-table exists and has the corresponding values).

median (collection, field_specifier)
Returns the median value of the field values of the items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
The field_specifier parameter determines the field to process. It uses the same syntax as described in the fieldValue() function.

count (collection)
Returns the number of items in the collection given as parameter.
Use the subTableItems() function to get a collection of items to process.
Examples:
 count(subTableItems("AssetRecords/{04a4080f-fec0-4e01-822f-b9125c0b5ac9}")) returns the number of comments made to this asset.

itemByIndex (collection, index)
Returns an item of the collection given. The item is specified by index.
The collection object is returned by the subTableItems() function.
The index can be specified as a zero-based positive integer specifying items from the start of the collection (0 = first item, 1 = second items, etc.).
A negative index specifies items from the end of the collection (-1 = last item). You can get the same using the reverse sort direction on the collection and specifying a non-negative index.

Examples:
 fieldValue("User", itemByIndex(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Download", "Date", "descending"), 0)) returns the user ID of the user who downloaded this asset last.

fieldValue (field_specifier, item [, as_string])
Returns the value of a field of the given item.
If the field does not have any value the function returns null
The field_specifier parameter determines the field. It uses the same syntax as described in the fieldValue() function.
Use the itemByIndex() function to get an item out of a collection.
See fieldValue() for another usage of this function to get a field value for the current item or an item in a super table of the current item.

Examples:
 fieldValue("User", itemByIndex(subTableItems("AssetRecords/{a89b908c-e98e-413b-ac4d-50668b2fafbc}", "'{40f05fd7-9647-4662-a589-7b2be4034f12}:Asset Usage' == Download", "Date", "descending"), 0)) returns the user ID of the user who downloaded this asset last.

fieldHasValue (field_specifier, item)
Returns whether the given field of the given item has a value.
The field_specifier parameter uses the syntax defined in the fieldValue() function.
Use the itemByIndex() function to get an item out of a collection.
See fieldHasValue() for another usage of this function to check for a field value for the current item or an item in a super table of the current item.

Field Value Functions

The following functions access field values of a Cumulus catalog item or the user context.
The types depend on the field specified.

fieldValue (field_specifier [, as_string])
Returns the value of a field of the current item or an item above the current item in the table hierarchy.
If the field does not have any value the function returns null

The field_specifier parameter can be one of the following:
- A field name in any of the supported languages (e.g. "Record Name").
- A field unique ID (e.g. "{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}").
- A field unique ID followed by a field name separated by colon for readability: "{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}:Record Name".
- A field short ID which is only valid for a single catalog (e.g. 1382239853 or 0x52634e6d)
- A table name and a field name, unique ID or short field ID separated by slash or colon (e.g. "AssetRecords/Record Name" or "AssetRecords:Record Name").
  If specified the table needs to name a table above the current item's table in the hierarchy.
  You can for example reference the "AssetRecords" table's host item from a formula field in the "Asset Usage History" table to store asset record field values in "Asset Usage" items.
  The field value of the "Categories" field needs the as_string to be true and returns the assigned categories of the record. Each category is returned as the complete path (e.g. "Top Level:Next Level:The Assigned Category") with a "\n" character at the end. A ":" in the category name is escaped using "::" to make it differ from the path separator. You can check whether a record has a specific category assigned by using  indexOf(fieldValue("Categories"), "Top Level:Next Level:The Assigned Category\n") == -1 ? "Not assigned" : "assigned" 
The second variant of the function gets an explicit item as a parameter. This is returned by the itemByIndex().
The optional as_string parameter is a boolean specifying whether the field value should be converted into a string when returned.
This can be used for string list fields to return the localized name of the value instead of the ID.
See fieldValue() for another usage of this function to get a field value for an item of a collection created for a sub-table.

Examples:
 fieldValue("Record Name") returns the record name.
 fieldValue("{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}") also returns the record name.
 fieldValue("{af4b2e00-5f6a-11d2-8f20-0000c0e166dc}:Record Name") also returns the record name.
 fieldValue("Color Mode") returns the value of the "Color Mode" field (e.g. 1).
 fieldValue("Color Mode", true) returns the value of the "Color Mode" field in a localized string (e.g. "Grayscale").
 fieldValue("AssetRecords/{af4b2e07-5f6a-11d2-8f20-0000c0e166dc}:Status", true) can be used in a field of the "Asset Usage History". It returns the value of the "Status" field of the host item in a localized string (e.g. "Approved").
userFieldValue (field_specifier [, as_string])
Returns the value of a field of the current user context.
If the field does not have any value the function returns null
The optional as_string parameter is a boolean specifying whether the field value should be converted into a string when returned.
This can be used for string list fields to return the localized name of the value instead of the ID.

The field_specifier parameter can be one of the following:
- A field name in any of the supported languages (e.g. "Last Name")
- A field unique ID passed as a string (e.g. "{7c43714f-daa4-11d6-b6be-0050baeba6c7}" for the user's e-mail address)

Examples:
 userFieldValue("Last Name")  returns the last name of the current user.
 userFieldValue("{7c43714f-daa4-11d6-b6be-0050baeba6c7}")  returns the e-mail address of the current user.
fieldHasValue (field_specifier)
Returns whether the given field of the current item has a value.
The field_specifier parameter uses the syntax defined in the fieldValue() function.

userFieldHasValue (field_specifier)
Returns whether the given field of the current user context has a value.
The field_specifier parameter uses the syntax defined in the userFieldValue() function.