Functions

Rules > Rules manager > Rule items > Set actions > Functions

Function Description Formula type Notes and examples

Function	Description	Formula type	Notes and examples
abs	Returns the absolute value of param1, which may be a Number field or a constant number, i.e., `abs(NumberField)` or `abs(500)`, respectively.	Numeric
acos	Returns the inverse cosine of param1, which may be a Number field or a constant number, i.e., `acos(NumberField)` or `acos(500)`, respectively.	Numeric
acosh	Returns the inverse hyperbolic cosine of param1, which may be a Number field or a constant number, i.e., `acosh(NumberField)` or `acosh(500)`, respectively.	Numeric
asin	Returns the inverse sine of param1, which may be a Number field or a constant number, i.e., `asin(NumberField)` or `asin(500)`, respectively.	Numeric
asinh	Returns the inverse hyperbolic sine of param1, which may be a Number field or a constant number, i.e., `asinh(NumberField)` or `asinh(500)`, respectively.	Numeric
atan	Returns the inverse tangent of param1, which may be a Number field or a constant number, i.e., `atan(NumberField)` or `atan(500)`, respectively.	Numeric
atanh	Returns the inverse hyperbolic tangent of param1, which may be a Number field or a constant number, i.e., `atanh(NumberField)` or `atanh(500)`, respectively.	Numeric
avg	Returns the average of any number of parameters, which may be Number fields and/or constant numbers, i.e., `avg(NumberField1,NumberField2, 500)`	Numeric	Example: `avg(VALUE(Subtotal1), VALUE(Subtotal2), 100)`
avgFields	For related tables only. Returns to a Number field of a parent table the average based on a Number field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., `avgFields(ChildRelationField,ChildNumberField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = avgFields(REF(MyChildRelationField), REF(MyChildNumberField))` This function can optionally be used with up to six parameters: - param3 and param4 together work as lower and upper bound filter condition; i.e., `avgFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5)`, meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account. - param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; i.e., `avgFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc")` , meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.	Numeric	Example 1: `avgFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder total" is a Number field of the Suborder table too. The result of this formula will be set on "Suborder total average", which is a Number field of the parent Order table. Order A. Suborder total average=4 Suborder A1. Suborder total=5 Suborder A2. Suborder total=3 Example 2, using param3 and param4: `avgFields(REF(Order), REF(Suborder Total), 2, 3)`, where the third and fourth parameters bound the "Suborder Total" to have a value between 2 and 3; i.e., the formula will calculate the average of the total figures considering only those suborders that have such figures within this range. Example 3, using param5 and param6: `avgFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087))`, where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate the average of the total figures considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed. Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. Example 4, using all 6 parameters: `avgFields(REF(Order), REF(Suborder Total), 2, 3, REF(Suborder manager), REF(P101087))`, where the formula will calculate the average of the total figures considering only those suborders that have such figures between 2 and 3 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.
ceil	The ceiling function returns a number rounded up to the next full integer (e.g., 10.1 → 11) of param1, which may be a Number field or a constant number, i.e., `ceil(NumberField)` or `ceil(10.1)`, respectively.	Numeric
clearField	Clears the field value of the field supplied in param1, which may be any type of field except for Drop-down. Returns 1 for success or 0 for failure (that can be targeted to a Number or Checkbox field), i.e., `TableCheckbox = clearField(TextField)`.	Numeric	It is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. Example: `clearField(REF(Comments))`, where "Comments" will be cleared, i.e., set to blank, if the rule’s criteria is fulfilled. If so, the "Dummy Checkbox" will be automatically ticked (set to 1). It is very useful to have this function as nested as well (see example of match function). Note: Another way to clear a field is to simply type the constant `NULL` in the formula box.
concat	Returns to a Text or Note field a string of param1 concatenated with param2, param3, etc., which may be fields of any type and/or strings entered within quotation marks, i.e., `concat(TextField, " is concatenated to this string")`	Text/Note	Example 1: `concat("The total amount is ", VALUE(Order total))`, where the first parameter is a string, and the second one is a Number field. The result of this formula will be set on "Comments", which is a Note field of the Order table. Important note: Concatenated values that exceed the maximum length of the destination Text/Note field will be truncated. Example 2: use this function to customize the Record name, e.g., concatenate the Customer ID plus a consecutive number, like "ACME-1001": `Record name = concat(VALUE(Customer), "-", str(GetRecordName()))`
cos	Returns the cosine of param1, which may be a Number field or a constant number, i.e., `cos(NumberField)` or `cos(500)`, respectively.	Numeric
cosh	Returns the hyperbolic cosine of param1, which may be a Number field or a constant number, i.e., `cosh(NumberField)` or `cosh(500)`, respectively.	Numeric
countFields	For related tables only. Returns to a Number field of a parent table the total count of a specific non-empty field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., `countFields(ChildRelationField, ChildAnyTypeOfField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField))` This function can optionally be used with up to six parameters: -param3, as standalone, is interpreted as a filter condition made on param2; e.g., `countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField), 12)`, meaning that only those records having the field "MyChildAnyTypeOfField" with value "12" will be taken into account. -param3 and param4 together work as lower and upper bound filter condition; e.g., `countFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5)`, meaning that only those records having the field "ChildNumberField" with value between 1 and 5 will to be taken into account. -param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., `countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField), notused, notused, REF(MyChildTextField), "abc")`, meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.	Numeric	Example 1: `countFields(REF(Order), REF(Suborder completed))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder completed" is a Checkbox field of the Suborder table too. The result of this formula will be set on "Number of suborders completed", which is a Number field of the parent Order table. Order A. Number of suborders completed=1 Suborder A1. Suborder completed=True Suborder A2. Suborder completed=False Note: If is also very common to use this function to count the total records that depends on a main one, e.g., `countFields(REF(Order), REF(Order))`, where the same Relation field is used as param1 and param2, to simply count all suborders that belong to an order. Example 2, using param3: `countFields(REF(Order), REF(Suborder expire year), 2013)`, where the second parameter is a number field, and the third parameter is a constant value. The formula will only calculate how many suborders have an expiration year of 2013. Example 3, using param3 and param4: `countFields(REF(Order), REF(Suborder expire year), 2013, 2015)`, where the third and fourth parameters bound the "Suborder expire date" to have a value between 2013 and 2015. The formula will only calculate how many suborders have an expiration year of 2013, 2014 or 2015. Example 4, using param5 and param6: `countFields(REF(Order), REF(Suborder completed), notused, notused, REF(Suborder manager), REF(P101087))`, where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate how many suborders are completed considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed. Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. Example 5, using all 6 parameters: `countFields(REF(Order), REF(Suborder expire year), 2013, 2015, REF(Suborder manager), REF(P101087))`, where the formula will only calculate how many suborders have expiration date between 2013 and 2015 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.
currentDate	Returns to a Date field today’s date. No parameter is needed.	Numeric	See an example in the getDays function.
currentDateTime	Returns to a Date Time field today’s date and time. No parameter is needed.	Numeric
dayOfMonth	Returns the day of the month of a Date, Date Time or strings in param1, i.e., `dayOfMonth(DateField)`.	Usually Numeric, but it can also be Text/Note
decimals	Rounds the Number field in param1 to the number of decimals in param2, e.g., `decimals(NumberField, 2)`, where, for instance, the number 10.4362 would be displayed as 10.44	Numeric	Example: `decimals(VALUE(Order total), 2)`, meaning that the "Order total" Number field would show its value with 2 decimals, like 3,034.50
exp	Returns the exponential of param1, which may be a Number field or a constant number, i.e., exp(NumberField) or exp(500), respectively.	Numeric
fieldCreated	Returns the date when the field content was created. Param1 is the ID of the field, and must be given as a reference, i.e., fieldCreated(REF(AnyTypeOfField)). Note: No Date will be shown for deleted data.	Usually Numeric, but it can also be Text/Note	Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function. Example: `toDateString(fieldCreated(REF(Order))`
fieldCreator	Returns USERNAME or USERID of field content creator. Param1 is the ID of the field, and must be given as a reference, REF(). Param2 can be "1" or "0", where 1 displays the USERID and 0 displays the USERNAME, i.e., `fieldCreator(REF(AnyTypeOfField , "1"))`. Note: No information is returned for deleted data.	Text/Note	Example: `fieldCreator( REF(Order) , "1" )`
fieldCreatorEmail	Returns email address of field content creator, or an empty string if missing. Param1 is the ID of the field, and must be given as a reference, i.e., `fieldCreatorEmail(REF(AnyTypeOfField))`.	Text/Note
fieldCreatorName	Returns full name of field content creator. Param1 is the ID of the field, and must be given as a reference. Param2 can be 1 or 0, where 1 displays the last name first (Smith, John) and 0 displays the first name first (John Smith).	Text/Note	Example: `fieldCreatorName( REF(Order) , 0 )`
fieldUpdated	Returns the date when the field content was updated. Param1 is the ID of the field, and must be given as a reference, REF().No information is returned for deleted data.	Numeric
fieldUpdator	Returns USERNAME or USERID of field content update performer. Param1 is the ID of the field, and must be given as a reference, REF(). If param2 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned. No information is returned for deleted data.	Text/Note	See an example of the parameters in the fieldCreator function.
fieldUpdatorEmail	Returns email address of field content update performer, or an empty string if missing. Param1 is the ID of the field, and must be given as a reference, REF().No information is returned for deleted data.	Text/Note
fieldUpdatorName	Returns full name of field content update performer. Param1 is the field ID of the field, and must be given as a reference, REF(). If param2 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith). No information is returned for deleted data.	Text/Note
floor	Returns the largest integer not greater than param1 (e.g., 10.1 → 10), which may be a Number field or a constant number, i.e., `floor(NumberField)` or floor(10.1)`, respectively.	Numeric
formLink	Returns to a Text or Note field, the link string (the URL) of a Form specified as a reference (REF()) in param1, i.e., `formLink(REF(FormID))`. It can be very useful to get the URL of a form to use it, for example, when sending an e-mail, so the recipient can refer to that form with the current record shown. Note that the recipient using the link will normally be asked for authentication on the login page before the chosen form and record are shown. Note: Form IDs can be found as fields in the Field drop-down of the Formula editor. The chosen form must have the same main table as the current Trigger table of the rule.	Text/Note	Example: `formLink(REF(Order information form))`
getDays	Returns the number of days of param1, which must be a Date field or a substraction of Dates, i.e., `getDays(DateField)` or `getDays(DateField2-DateField1)`, respectively.	Numeric	Example: `getDays(VALUE(Installation finish date)-VALUE(Installation start date))` or `getDays(currentDate()-VALUE(Installation start date))`, in order to know how many days an installation took to be ready, or how many days is taking until the current day, respectively. Note that the substraction of dates can also be done without this function: `(VALUE(Installation finish date)-VALUE(Installation start date))/D`, where D is a Date constant (D=day).
getMobilePhone	Returns the mobile phone number of the user (registered in User administration) with the username or userId supplied in param1. If no such user exists, it returns an empty string ("").	Text/Note	Examples: `getMobilePhone("MANAGER")` `getMobilePhone(23)` `getMobilePhone(fieldCreator(REF(100100), true/false))` `getMobilePhone(recordCreator(true/false))`
getRecordGroup	Returns the name or Id (depending on if Formula type is Text/Note or Numeric, respectively) of the group the record in param1 belongs to. Param1 can be: - A constant value (i.e., the record name or record Id), e.g., `getRecordGroup("MyRecord1", 20)` or `getRecordGroup(100103, notused), or - A variable value (i.e., the Relation field pointing to the Parent record), e.g., `getRecordGroup(RelationFieldToParent, 20)`, where Param2 is only needed for Text/Note and is the Table Id* of the record named by Param1. For Numeric type, Param2 can be set to null or notused.	Usually Text/Note, but it can also be Numeric.	Mostly used in combination with the function `moveToRecordGroup(param1)` which is a very effective way to move a child record to whatever group the parent record belongs to. Example: `moveToRecordGroup(getRecordGroup(RelationFieldToParent, 20))`, where the target record will be moved to the group of its parent record, following the relation and the table id specified in param1 and param2 of the getRecordGroup function. For this, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the record will be moved to the parent’s group and the "Dummy Checkbox" will be automatically ticked. Read more in the moveToRecordGroup function’s notes. If the function is used alone: Example: `getRecordGroup(RelationFieldToParent, 20)` or `getRecordGroup(100103, notused)`, with target field as Text, Note, Number or Checkbox. Table Id can be obtained from the URL of Open table records icon in Data model editor (looks like istools/apps/2063/forms/magical/2?view=window, where the number before "?" is the Table Id. Note*: The result of using this function with an invalid input will be 0.
getRecordName	Returns to a Text or Note field the name of the target record. No parameter is needed. In case the Record name is a number, this function must be converted to a string, i.e., `str(getRecordName())`.	Text/Note
getFieldLabel	Returns to a Text or Note field the name of the field in parameter1, i.e., `getFieldLabel(AnyTypeOfField)`.	Text/Note
getUserEmail	Returns to a Text or Note field the email address of the user with username supplied in param1 within quotation marks, e.g., `getUserEmail("SMITH")`.	Text/Note
hasRole	Checks whether the user executing the function has the role mentioned in param1, within quotation marks. e.g., `hasRole("MANAGER")`. Returns 1 if the user has the role, else 0.	Numeric	If this function is used alone, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. Based on the example above, if the rule’s criteria is fulfilled and the user doing so has the "MANAGER" role, then the "Dummy Checkbox" will be automatically ticked (set to 1). But if this function is used as nested in other function, it can be very useful and no need of a Checkbox is required. Example: if( hasRole("MANAGER") , `concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), "Awaiting for manager’s revision" ), where if the role MANAGER fulfills the rule’s criteria, then the "Comments" Note field will get the value "Reviewed by the manager on 15-12-2012 15:41:54". But if any other role fulfills the rule’s criteria, "Comments" will get the value "Awaiting for manager’s revision".
hasValue	Checks whether the field with field id param1 has a value assigned to it, returns 1 if a value exists, 0 otherwise. Param1 must be a reference to a field, REF(), e.g., `hasValue(REF(AnyTypeOfField))`.	Any	Example: `Best quotation = if(hasValue("Quotation1") AND hasValue("Quotation2"), if("Quotation2">"Quotation1", "Quotation1", "Quotation2"), "Waiting for both quotations to be filled in")`, would first check if the "Quotation" fields have a value before taking the action in the nested if.
hour	Returns the hour of the day (24 hours) of a Date, Date time or strings in param1.	Usually Numeric, but it can also be Text/Note
if	Returns the value of param2 only if param1 is positive, otherwise returns the value of param3, i.e., `if(condition here is True, do this, else do that)`. Optionally, other functions can be nested. Remember that both the rule’s criteria and the first parameter need to be fulfilled in order to return a result.	Any, depending on the type of target field.	Example 1: `if( hasRole("MANAGER") , concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), "Awaiting for manager’s revision")`, where if the role MANAGER fulfills the rule’s criteria, then the "Comments" Note field will get the value "Reviewed by the manager on 15-12-2012 15:41:54". But if any other role fulfills the rule’s criteria, "Comments" will get the value "Awaiting for manager’s revision". Note: In case no action is wished, i.e, no value needs to be returned by the formula, the constant IGNORE can be typed as the proper parameter in the formula box. For instance, `if( hasRole("MANAGER"), concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), IGNORE)`, where if the role MANAGER does not fulfill the rule’s criteria, then the "Comments" Note field will not get a value (not even "0", which would be the case if typing 0 or False as param3 instead). Example 2: `if( Order total > 1000, Requires approval from Line Manager = 1, 0)`, where if the rule’s criteria is fulfilled together with the "Order total" greater than 1000, then the "Requires approval from Line Manager" Checkbox will be ticked, otherwise it will remain as false.
isLeapYear	Returns 1/True if the year represented by param1 (Date or Date time) is a Leap year; otherwise returns 0/False.	Any, depending on the type of target field.	Example 1: `isLeapYear(Order requested on)` Example 2: `isLeapYear(parseDate("2012-05-30"))`, where the result would be 1/True since 2012 was a leap year. Example 3: `isLeapYear(parseDateString("31-02-2011", "dd-MM-yyyy"))`, where the result would be 0/False since 2011 was not a leap year. Example 4: `if(isLeapYear(Order requested on), "Customer has 1 additional day in February to pay", 0)`
lastFieldModifiedDate	Returns the latest date upon which the data of the field in param1 was modified, i.e., `lastFieldModifiedDate(AnyTypeOfField)`. Note: No Date will be shown for deleted data.	Usually Numeric, but it can also be Text/Note.	Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function. Example: `toDateString(lastFieldModifiedDate( REF(Order)))`.
lastFieldModifiedName	Returns the name of the last user who modified the field in param1. Param2 can be 1 or 0, where 1 displays the user’s last name first (Smith, John) and 0 displays it as normal (John Smith), i.e., lastFieldModifiedName(AnyTypeOfField, 1). Note: No name will be shown for deleted data.	Text/Note	Example: `lastFieldModifiedName(REF(Order), 0)`.
lastFieldModifiedUserName	Returns the username of the last user who modified the field in param1, i.e., `lastFieldModifiedUserName(AnyTypeOfField)`. Note: No username will be shown for deleted data.	Text/Note
lastRecordModifiedDate	Returns the latest date upon which the data of the record was modified. No parameter is needed.	Usually Numeric, but it can also be Text/Note.	Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function. Example: `toDateString(lastRecordModifiedDate())`.
lastRecordModifiedName	Returns the name of the last user who modified the target record. Param1 can be 1 or 0, where 1 displays the user’s last name first (Smith, John) and 0 displays it as normal (John Smith), e.g., `lastRecordModifiedName( 0)`.	Text/Note
lastRecordModifiedUserName	Returns the username of the last user who modified the target record. No parameter is needed.	Text/Note
length	Returns the number of characters of param1, i.e., `length(AnyTypeOfField)`.	Numeric
ln	Returns the natural logarithm of param1, which may be a Number field or a constant number, i.e., `ln(NumberField)` or `ln(500)`, respectively.	Numeric
log	Returns the common logarithm of param1, which may be a Number field or a constant number, i.e., `log(NumberField)` or `log(500)`, respectively.	Numeric
match	Checks if the string in param1 matches the regular expression pattern in param2, i.e., `match(AnyTypeOfField, expression to match)`. Optionally, this function can be nested. Remember that both the rule’s criteria and the match function need to be fulfilled in order to return a result. Returns 1 if match is found, else 0.	Text/Note	In this case, the Formula type must be Text/Note, due to the regular expression, even if the target field is a Checkbox or a Number. Regular expressions can be obtained at regular-expressions.info and quotation marks must be used to enter them in the formula. This function is rarely used alone, but if that is the case, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. Example 1: `match(VALUE(Email address), "\b[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}\b")`, meaning that if the rule’s criteria is fulfilled and the value entered by the user in the "Email address" Text field matches the format asdf@asdf.asdf (as interpreted by the regular expression in param2), then the "Dummy Checkbox" will be automatically ticked. Also, this function can be very useful if it’s used as nested in other function. Example 2: `if( (match(VALUE(Email address), "\b[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}\b")), "Email format validated", "-Email format is not valid, please provide a valid format" + clearField(REF(Email address)))`, meaning that if the rule’s criteria is fulfilled and the value entered by the user in the "Email address" Text field matches the format asdf@asdf.asdf (as interpreted by the regular expression), then the "Comments" field will be set with the text "Email format validated", otherwise (if it does not match) two actions will be performed: another text will be set on "Comments" and the invalid value in the "Email address" field will be removed.
max	Returns the maximum value of a variable number of parameters, i.e., the largest of those values (usually Number or Dates fields), `max(NumberField1, NumberField2, NumberField3)`.	Numeric
maxFields	For related tables only. Returns to a Number field of a parent table the maximum value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., `maxFields(ChildRelationField, ChildNumberField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = maxFields(REF(MyChildRelationField), REF(MyChildNumberField))` This function can optionally be used with up to six parameters: -param3 and param4 together work as lower and upper bound filter condition; e.g., `maxFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5)`, meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account. -param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., `maxFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc")`, meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.	Numeric	Example 1: `maxFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Maximum amount in Suborders", which is a Number field of the parent Order table. Order A. Maximum amount in Suborders=150 Suborder A1. Suborder total=150 Suborder A2. Suborder total=120 Example 2, using param5 and param6: `maxFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087))`, where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will return the maximum value only among those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed. Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. To set a null value as param6, use the keyword null or 0.
median	Returns the median of a variable number of parameters, usually Number or Dates fields, `median(NumberField1, NumberField2, NumberField3)`.	Numeric
medianFields	For related tables only. Returns to a Number field of a parent table the median value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., `medianFields(ChildRelationField, ChildNumberField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = medianFields(REF(MyChildRelationField), REF(MyChildNumberField))`	Numeric	Example: `medianFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Suborders median amount", which is a Number field of the parent Order table.Order A. Suborders median amount=8 Suborder A1. Suborder total=2 Suborder A2. Suborder total=10 Suborder A3. Suborder total=8
min	Returns the minimum value of a variable number of parameters, i.e., the smallest of those values (usually Number or Dates fields), `min(NumberField1, NumberField2, NumberField3)`.	Numeric
minFields	For related tables only. Returns to a Number field of a parent table the minimum value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., `minFields(ChildRelationField, ChildNumberField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = minFields(REF(MyChildRelationField), REF(MyChildNumberField))` This function can optionally be used with up to six parameters: -param3 and param4 together work as lower and upper bound filter condition; e.g., minFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account. -param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., `minFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc")`, meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.	Numeric	Example 1: minFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Minimum amount in Suborders", which is a Number field of the parent Order table. Order A. Minimum amount in Suborders=150 Suborder A1. Suborder total=150 Suborder A2. Suborder total=120 Example 2, using param5 and param6: `minFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087))`, where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will return the minimum value only among those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed. Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. To set a null value as param6, use the keyword null or 0.
minute	Returns the minutes of a Date Time field in param1, i.e., `minute(DateTimeField)`.	Numeric
mod	Returns param1 modulus param2.	Numeric
month	Returns the month of the year of a Date, Date time or strings in param1. January is 0 and December is 11.	Usually Numeric, but it can also be Text/Note
moveRelatedRecords	For related tables only. Can move the target record and related records at once (relation specified in param2) to a certain record group (specified in param1). Param2 is a structure that describes which relation field id is used; `moveRelatedRecords(VALUE(Group name), "[{'relation':<ChildRelationFieldId>}]")`. Returns 1 if successfully moved, else 0. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentCheckboxField = moveRelatedRecords( "Group 1", "[{'relation':100656}]")`	Any	It is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the parent record and its child records will be moved to the specified group and the "Dummy Checkbox" will be automatically ticked. It’s very useful to have this function nested and/or used in combination with other functions. Example: `if(hasRole("CLIENT"), moveRelatedRecords("CLIENT DATA", "[{'relation':100656}]"), 0)`, which will allow the user to update certain information based on his role. In this case, a Client can trigger a Work record to move it together with all Activity records to the Record group Client data. The Field Id "100656" is the relation field between Activity and Work (i.e., Child table → Parent table). getRecordGroup(RelationFieldToParent, 20) Example 2: `moveRelatedRecords(getRecordGroup(RelationFieldToParent,8), "[{'relation':100656, 'children':[{'relation':100670}]}]")`, where the child records of the triggered record and the child records of the first ones (i.e., records 2 levels below the parent table: GrandChild table → Child table → Parent table) will be moved to the Record group of the parent record (due to the getRecordGroup function). Let’s say that triggering a Work record will move not only its Activity records but the Task records or those Activies too, to whatever group the Work belongs to. Note: The characters in red are part of the syntax of the structure, and therefore must be always written in this way.
moveToRecordGroup	Moves the target record to the record group with the value specified in param1. If the value is a constant, type it within quotation marks, e.g., `moveToRecordGroup("CLIENT RECORD GROUP")`; for a variable value, use a Drop-down or Text field where a user can enter the group name in a form or in an import, e.g., `moveToRecordGroup(VALUE(Client))` where Client in this example is a Drop-down field which list’s values should match the record group names. Returns 1 if successfully moved, else 0.	Text/Note	It is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the current record will be moved to the specified group and the "Dummy Checkbox" will be automatically ticked. Example 1: Criteria → Client = CHANGED Action → Set value of a field with a formula → Formula type = Text/Note `DummyCheckbox = moveToRecordGroup(VALUE(Client))` Rule name → Move orders to proper record group Description → The target order will be moved to the proper record group, depending on the value chosen in the Client drop-down, e.g., if Client is changed to "Industrix", the order will be moved to the Record group of the same name. It’s very useful to have this function nested and/or used in combination with other functions. Example 2: `if( hasRole("CLIENT"), moveToRecordGroup("CLIENT DATA"), 0)`, which will allow the user to update certain information based on his role.
padLeft	Returns the string of param1 left padded to the length of param2 with pad character param3, useful to standardize strings by filling out its left part. For instance, `padLeft(VALUE(Bank account number), 17, 0)`, would add a 0 to those numbers that have less than 17 digits to make them all the same length, like 00024856468576379, 00000000087654365, etc.	Text/Note
padRight	Returns the string of param1 right padded to the length of param2 with pad character param3. See padLeft.	Text/Note
parseDate	Exclusively used to compare dates in other functions (e.g., in if, match and as param5 in countFields, sumFields, etc.), it validates a string (param1, within quotations) as Date or Date time field using the date/time format of the triggering user, e.g., `if(DateField== parseDate("2013-09-24"), 1, 0)`. Note: The function will give different results or fail, for users with different/incompatible date/time formats. Consider using ParseDateFormat(param1, param2, param3) instead.	Numeric
parseDateFormat	Exclusively used to compare dates in other functions (e.g., in if, match and as param5 in countFields, sumFields, etc.), it validates a Date or Date time field (param1) using a specific format for the date (param2, within quotations) and a specific format for the time (param3, within quotations), e.g., `if(parseDateFormat(VALUE(P100588), "2013-09-24", "12:00:00"), 1, 0)`. In case time is not needed, set param3 to an empty string.	Numeric	You can also try this function nested as follows: `if(hasValue(REF(P100588)), parseDateFormat(VALUE(P100588), "YYYY-MM-DD", "HH:MM:SS"), 0)`
pathValue	For related tables only. Returns the value of a field from a related record, following the specified path; i.e., can get a parent’s field value or a child’s field value, or a more complex path like getting a parent’s parent field value. -For a simple direct relation to get a field value from a parent record, use: `pathValue("0_FChildRelationField_P0_ParentAnyTypeOfField_0")`, which will return to the target field of the child record the value in the "ParentAnyTypeOfField", using the "ChildRelationField" that relates towards the parent. Note that quotation marks and 0_F, _P0_, _0 keywords are needed. -For a complex direct relation to get a field value from a parent record, use: `pathValue("0_FChildRelationFieldToParent1_FParent1RelationFieldToParent2_P0_Parent2AnyTypeOfField_0")`, which will return to the target field of the child record the value in the "Parent2AnyTypeOfField", following first the path to the parent using "ChildRelationFieldToParent1" and then using "Parent1RelationFieldToParent2" that relates towards the parent of the first parent. Note that quotation marks and 0_F, 0_F, _P0_, _0 keywords are needed. -For a simple reverse relation to get a field value from a child record, use: `pathValue("0_RChildRelationField_P0_ChildAnyTypeOfField_0")`, which will return to the target field of the parent record the value in the "ChildAnyTypeOfField", using the "ChildRelationField" that relates towards the parent. Note that quotation marks and 0_R, P0, 0 keywords are needed. Note also that the resulting value will come from any child record, i.e., random selection (to get the field value from the specific triggered child record, use the _triggerRecordValue function).	Any, depending on the type of target field.	For instance, having the model Region ← Country ← City (where Country is parent of City, and Region is parent of Country), in the "Country" form, pathValue makes it possible for the record "Sweden" to fetch the value in the field called "Region responsible" that is part of the "Region" table. Since the country "Sweden" is related to the region "Northern Europe", the result would be "Erik Svensson" that can be place in a field called "Responsible for all countries in the region" as part of "Country" table located in the "Country" form. Looking at the Example 1 below, 100301 is the ID of the relation between Country and Region, and 100200 is the ID of the "Region responsible" field of Region. The field at the top of the Formula Editor which will get the result is "Responsible for all countries in the region" of Country. The formula is Text/Note type. Example 1: `pathValue("0_F100301_P0_1002000")` Example 2: `pathValue("0_F100301_F100303_P0``1002010")`, for direct relations, will return to the target field of the child record using the relation with ID "100301" (to a parent table), and from there, the relation with ID "100303" (to the parent’s parent table), the value in the field with ID "100201" of the parent’s parent table. Based on the model above, the value of the field "Region responsible" can be got from a "City" record, i.e., from the record "Stockholm" in the "City" form, meaning that it will follow the relation to the parent, then follow the relation to the parent’s parent to finally get the value. Example 3: `pathValue("0_R100302_P0_100300_0")`, for reverse relation, will return to the target field of the parent record using the relation with ID "100302" (from the child table), the value in the field with ID "100300" of a child record (_randomly, only in case no filter is set, as in example4). The Example3 makes it possible to follow a path downwards, to get values from one child record. For instance, Country can get a value from a City: e.g., "Sweden" can get a value from the record "Stockholm". Example 4: pathValue("0_R100302(100058!2!>!#8)_P0_100300_0"), for reverse relation, will return to the target field of the parent record using the relation with ID "100302" (from a child table), the value in the field with ID "100300" of the child record matching the filter (randomly, if resulted in several) made on its field with ID "100058", of type Number, larger than 8. For reverse relation, the triggerRecordValue function can be used instead, where the value of the triggered child record is returned. Notes: The characters in red are part of the syntax of the path, and therefore must be always written in this way (F for direct relations and R for reverse relations). Request help to the IS Tools support team for complex filter syntax (1 Text, 2 Number, 3 Date, 4 Note, 5 Drop-down, 6 Checkbox, 7 Relation, 8 File, 9 Link, 11 Date-time). Field Id’s can be found via the Field usage report in the Data model editor.
pathValueExists	To be used in combination with the if and pathValue functions, pathValueExists makes sure that the relations used in the path and the value to be fetched exist. `if(pathValueExists("0_F100301_P0_100200_0"), pathValue("0_F100301_P0_100200_0"), IGNORE)` If pathValueExists returns 1/True (i.e., the path is valid), pathValue is executed. Othewise, returns the value in param3, which is usually 0/False, or returns nothing if the constant IGNORE is used, as in the example above.	Any, depending on the type of target field.
rand	Returns a random number between 0 and 1. No parameter is needed.	Numeric
randomString	Returns a random string. The length can be set with param1 (if not set the length is 8).	Text/Note	Both rand and randomString can be used, for instance, to create unique concatenated alphanumeric identificators.
recordCreated	Returns the date when the record was created.	Usually Numeric, but it can also be Text/Note	Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function. Example: `toDateString(recordCreated())`.
recordCreator	Returns USERNAME or USERID of record creator. If param1 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned.	Text/Note	Example: `recordCreator("0")`.
recordCreatorEmail	Returns email address of record creator, or an empty string if missing.	Text/Note
recordCreatorName	Returns full name of record creator. If param1 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith).	Text/Note
recordId	Returns the record Id of the target record.	Text/Note or Numeric	Can be used as nested in other functions like getRecordGroup.
recordUpdated	Returns the date when the record was updated, or 0/’’ (empty string) if missing. No information is returned for deleted data.	Usually Numeric, but it can also be Text/Note
recordUpdator	Returns USERNAME or USERID of record update performer. If param1 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned. No information is returned for deleted data.	Text/Note	Example: `recordUpdator("0")`
recordUpdatorEmail	Returns email address record update performer, or an empty string if missing. No information is returned for deleted data.	Text/Note
recordUpdatorName	Returns full name of record update performer. If param1 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith). No information is returned for deleted data.	Text/Note
relatedValue	For related tables only. Returns to a field of a child table the field value of a parent table (param2), using the relation (param1) between these tables, i.e., "copies" the field value of a parent record to a field of all related child records; `relatedValue(ChildRelationField, ParentAnyTypeOfField)`. Target: Child table Trigger: Child table or Parent table Criteria: Any Action: `ChildField = relatedValue(REF(MyChildRelationField), REF(MyParentField))`	Any, depending on the type of target field.	This is a function that can perform an action on the child table from the parent table (i.e., has the target on the child); the opposite as in other functions for related tables such as maxField, minFields, etc. Example: `relatedValue(REF(Order), REF(Order requested by))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Order requested by" is a Text field of the parent table. The result of this formula will be set on "Main order requested by", which is a Text field of the child Suborder table; meaning that if the rule’s criteria is fulfilled, the name of the person requesting the order, will be copied to all suborders that belong to that order.
replace	If the value in param2 is found in the value in param1, then it replaces that string for the one in param3, i.e., `replace(Text/NoteField, "String to be found and replaced", "Replacement string")`. Note that param2 and param3 can be either fields or strings; for the latter one, quotation marks are needed.	Text/Note	Example: `replace(VALUE(Full address), "PC", "Postal code:")`, meaning that if the text "PC" is found in any part of the value of the "Full address" Text field, then it will be replaced with "Postal code" instead. In this case the target field is the same as param1. Based on the example above, the original value in "Full address" being "6300 Legacy Drive, PC: 75024, Plano, TX", would be replaced for "6300 Legacy Drive, Postal Code: 75024, Plano, TX" after this formula is applied.
round	Returns param1 rounded to the closest integer, e.g., 10.3 → 10, 10.5 → 11; `round(NumberField)`.	Numeric
second	Returns the seconds of a Date time field in param1, i.e., `second(DateTimeField)`.	Numeric
sin	Returns the sine of param1, which may be a Number field or a constant number, i.e., `sin(NumberField)` or `sin(500)`, respectively.	Numeric
sinh	Returns the hyperbolic sine of param1, which may be a Number field or a constant number, i.e., `sinh(NumberField)` or `sinh(500)`, respectively.	Numeric
sqrt	Returns the square root of param1, which may be a Number field or a constant number, i.e., `sqrt(NumberField)` or `sqrt(500)`, respectively.	Numeric
str	Converts param1 to text and returns it. Useful to get a string after nesting other functions, e.g., `str(getRecordName())`.	Text/Note
substring	Returns the part of the string in param1 between the index (position) specified in param2 and param3 (strings are 0 indexed).	Text/Note	Example: `substring(Serial number, 0, 2)`, where having "ABC123" as Project number this function would return "ABC".
sum	Returns the sum of a variable number of parameters, which may be Number fields or constant numbers, i.e., `sum(NumberField, NumberField2, 4500)`.	Numeric
sumFields	For related tables only. Returns (to a Number field of a parent table) the sum of a Number field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., `sumFields(ChildRelationField, NumberField)`. Target: Parent table Trigger: Child table or Parent table Criteria: Any Action: `ParentNumberField = sumFields(REF(MyChildRelationField), REF(MyChildNumberField))` This function can optionally be used with up to six parameters: -param3, as standalone, is interpreted as a filter condition made on param2; e.g., `sumFields(REF(MyChildRelationField), REF(MyChildNumberField), 12)`, meaning that only those records having the field "MyChildNumberField" with value "12" will be taken into account. -param3 and param4 together work as lower and upper bound filter condition; e.g., `sumFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5)`, meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account. -param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., `sumFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc"))`, meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.	Numeric	Example 1: `sumFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder total" is a Number field of the Suborder table too. The result of this formula will be set on "Order grand total", which is a Number field of the parent Order table. Order A. Order grand total=4 Suborder A1. Suborder total=5 Suborder A2. Suborder total=3 Example 2, using param3 and param4: `sumFields(REF(Order), REF(Suborder Total), 2, 3)`, where the third and fourth parameters bound the "Suborder Total" to have a value between 2 and 3; i.e., the formula will calculate the sum of the total figures considering only those suborders that have such figures within this range. Example 3, using param5 and param6: `sumFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087))`, where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate the sum of the total figures considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed. Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. Example 4, using all 6 parameters: `sumFields(REF(Order), REF(Suborder Total), 2, 3, REF(Suborder manager), REF(P101087))`, where the formula will calculate the sum of the total figures considering only those suborders that have such figures between 2 and 3 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.
tan	Returns the tangent of param1, which may be a Number field or a constant number, i.e., `tan(NumberField)` or `tan(500)`, respectively.	Numeric
tanh	Returns the hyperbolic tangent of param1, which may be a Number field or a constant number, i.e., `tanh(NumberField)` or `tanh(500)`, respectively.	Numeric
toDatePattern	Returns (to a Text or Note field) the value of a Date or Date time field in param1 as a string formatted with pattern specified in param2 within quotation marks, e.g.., `toDatePattern(DateTimeField, "yyyy-MM-dd KK:mm:ss")`. The following pattern characters are supported: y = Year, M = Month, w = Week in year (number), W = Week in month (number), D = Day in year (number), d = Day in month(number), F = Day of week in month (number), E = Day in week (text), a = Am/Pm marker (text), H = Hour in day (0-23), k = Hour in day (1-24), K = Hour in day am/pm (0-11), h = Hour in day am/pm (1-12), m = Minute in hour, s = Second in minute.	Numeric	Note that the case of the pattern is sensitive; for instance, is not the same to type w or W.
toDateString	Returns (to a Text or Note field) the value of a Date field in param1 as a string, i.e., `toDateString(DateField)`.	Numeric	See examples of use in lastFieldModifiedDate and lastRecordModifiedDate functions.
toDateTimeString	Returns (to a Text or Note field) the value of a Date Time field in param1 as a string, i.e., `toDateTimeString(DateTimeField)`.	Numeric
toLower	Returns param1 as a string in lower case, i.e., `toLower(TextOrNoteField)`.	Text/Note
toUpper	Returns param1 as a string in upper case, i.e., `toUpper(TextOrNoteField)`.	Text/Note
triggerRecordValue	For related tables only. Returns to a field of the target parent table, the value of the field in param1 from the triggered child record, i.e.: Target: Parentgger: Child Criteria: ChildField = <any condition> Action: `ParentField = triggerRecordValue(ChildField)`´	Text/Note	Example: If a Drop-down field on a child record is changed, then set the value of that child’s drop-down on a Text field of its parent record. Having Project as parent table and Stage as child table (i.e., Project as target table and Stage as trigger table), the criteria would be "Stage status" drop-down == CHANGED, then set the value of "Project latest status" with the formula `triggerRecordValue(Stage status)`, i.e., `triggerRecordValue(REF(P100451))` Project A. Project latest status = Approved by customer Stage A1. Stage status = Sent to customer Stage A2. Stage status = Approved by customer
userLatitude	Returns user latitude if available.	Numeric	Mostly used in combination with other functions. See an example in the userLocationTimestamp function.
userLocationAccuracy	Accuracy in meters for userLatitude() and userLongitude().	Numeric	Mostly used in combination with other functions. See an example in the userLocationTimestamp function.
userLocationTimestamp	Timestamp when userLatitude() and userLongitude() were recorded.	Numeric	Mostly used in combination with other functions. Example: if( userLocationAccuracy() < 90 && userLocationTimestamp() < currentDateTime() - 10/1440 , userLatitude() , 0), which will get the latitude position of a user if no more than 10 minutes have passed and precision is closer than 90 meters. Note that there are also useful functions for geolocation via validations and IQL.
userLongitude	Returns user longitude if available.	Numeric	Mostly used in combination with other functions.
week	Returns the week of year of param1, which may be a Date, Date Time field or strings, i.e., `week(DateOrDateTimeField)`. The week is defined as a 7-day-period having as first day the one set by the application administrator in the Application preferences form (otherwise, Monday is the default value).	Numeric
year	Returns the year of param1, which may be a Date, Date Time or strings, i.e., `year(DateOrDateTimeField)`.	Usually Numeric, but it can also be Text/Note

abs

Returns the absolute value of param1, which may be a Number field or a constant number, i.e., abs(NumberField) or abs(500), respectively.

Numeric

acos

Returns the inverse cosine of param1, which may be a Number field or a constant number, i.e., acos(NumberField) or acos(500), respectively.

Numeric

acosh

Returns the inverse hyperbolic cosine of param1, which may be a Number field or a constant number, i.e., acosh(NumberField) or acosh(500), respectively.

Numeric

asin

Returns the inverse sine of param1, which may be a Number field or a constant number, i.e., asin(NumberField) or asin(500), respectively.

Numeric

asinh

Returns the inverse hyperbolic sine of param1, which may be a Number field or a constant number, i.e., asinh(NumberField) or asinh(500), respectively.

Numeric

atan

Returns the inverse tangent of param1, which may be a Number field or a constant number, i.e., atan(NumberField) or atan(500), respectively.

Numeric

atanh

Returns the inverse hyperbolic tangent of param1, which may be a Number field or a constant number, i.e., atanh(NumberField) or atanh(500), respectively.

Numeric

avg

Returns the average of any number of parameters, which may be Number fields and/or constant numbers, i.e., avg(NumberField1,NumberField2, 500)

Numeric

Example: avg(VALUE(Subtotal1), VALUE(Subtotal2), 100)

avgFields

For related tables only. Returns to a Number field of a parent table the average based on a Number field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., avgFields(ChildRelationField,ChildNumberField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = avgFields(REF(MyChildRelationField), REF(MyChildNumberField))

This function can optionally be used with up to six parameters:

- param3 and param4 together work as lower and upper bound filter condition; i.e., avgFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account.

- param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; i.e., avgFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc") , meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.

Numeric

Example 1: avgFields(REF(Order), REF(Suborder Total)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder total" is a Number field of the Suborder table too. The result of this formula will be set on "Suborder total average", which is a Number field of the parent Order table.

Order A. Suborder total average=4

Suborder A1. Suborder total=5

Suborder A2. Suborder total=3

Example 2, using param3 and param4: avgFields(REF(Order), REF(Suborder Total), 2, 3), where the third and fourth parameters bound the "Suborder Total" to have a value between 2 and 3; i.e., the formula will calculate the average of the total figures considering only those suborders that have such figures within this range.

Example 3, using param5 and param6: avgFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087)), where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate the average of the total figures considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed.

Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively.

Example 4, using all 6 parameters: avgFields(REF(Order), REF(Suborder Total), 2, 3, REF(Suborder manager), REF(P101087)), where the formula will calculate the average of the total figures considering only those suborders that have such figures between 2 and 3 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.

ceil

The ceiling function returns a number rounded up to the next full integer (e.g., 10.1 → 11) of param1, which may be a Number field or a constant number, i.e., ceil(NumberField) or ceil(10.1), respectively.

Numeric

clearField

Clears the field value of the field supplied in param1, which may be any type of field except for Drop-down. Returns 1 for success or 0 for failure (that can be targeted to a Number or Checkbox field), i.e., TableCheckbox = clearField(TextField).

Numeric

Example: clearField(REF(Comments)), where "Comments" will be cleared, i.e., set to blank, if the rule’s criteria is fulfilled. If so, the "Dummy Checkbox" will be automatically ticked (set to 1).

It is very useful to have this function as nested as well (see example of match function).

Note: Another way to clear a field is to simply type the constant NULL in the formula box.

concat

Returns to a Text or Note field a string of param1 concatenated with param2, param3, etc., which may be fields of any type and/or strings entered within quotation marks, i.e., concat(TextField, " is concatenated to this string")

Text/Note

Example 1: concat("The total amount is ", VALUE(Order total)), where the first parameter is a string, and the second one is a Number field. The result of this formula will be set on "Comments", which is a Note field of the Order table.

Important note: Concatenated values that exceed the maximum length of the destination Text/Note field will be truncated.

Example 2: use this function to customize the Record name, e.g., concatenate the Customer ID plus a consecutive number, like "ACME-1001":

Record name = concat(VALUE(Customer), "-", str(GetRecordName()))

cos

Returns the cosine of param1, which may be a Number field or a constant number, i.e., cos(NumberField) or cos(500), respectively.

Numeric

cosh

Returns the hyperbolic cosine of param1, which may be a Number field or a constant number, i.e., cosh(NumberField) or cosh(500), respectively.

Numeric

countFields

For related tables only. Returns to a Number field of a parent table the total count of a specific non-empty field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., countFields(ChildRelationField, ChildAnyTypeOfField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField))

This function can optionally be used with up to six parameters:

-param3, as standalone, is interpreted as a filter condition made on param2; e.g., countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField), 12), meaning that only those records having the field "MyChildAnyTypeOfField" with value "12" will be taken into account.

-param3 and param4 together work as lower and upper bound filter condition; e.g., countFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "ChildNumberField" with value between 1 and 5 will to be taken into account.

-param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., countFields(REF(MyChildRelationField), REF(MyChildAnyTypeOfField), notused, notused, REF(MyChildTextField), "abc"), meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.

Numeric

Example 1: countFields(REF(Order), REF(Suborder completed)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder completed" is a Checkbox field of the Suborder table too. The result of this formula will be set on "Number of suborders completed", which is a Number field of the parent Order table.

Order A. Number of suborders completed=1

Suborder A1. Suborder completed=True

Suborder A2. Suborder completed=False

Note: If is also very common to use this function to count the total records that depends on a main one, e.g., countFields(REF(Order), REF(Order)), where the same Relation field is used as param1 and param2, to simply count all suborders that belong to an order.

Example 2, using param3: countFields(REF(Order), REF(Suborder expire year), 2013), where the second parameter is a number field, and the third parameter is a constant value. The formula will only calculate how many suborders have an expiration year of 2013.

Example 3, using param3 and param4: countFields(REF(Order), REF(Suborder expire year), 2013, 2015), where the third and fourth parameters bound the "Suborder expire date" to have a value between 2013 and 2015. The formula will only calculate how many suborders have an expiration year of 2013, 2014 or 2015.

Example 4, using param5 and param6: countFields(REF(Order), REF(Suborder completed), notused, notused, REF(Suborder manager), REF(P101087)), where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate how many suborders are completed considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed.

Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively.

Example 5, using all 6 parameters: countFields(REF(Order), REF(Suborder expire year), 2013, 2015, REF(Suborder manager), REF(P101087)), where the formula will only calculate how many suborders have expiration date between 2013 and 2015 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.

currentDate

Returns to a Date field today’s date. No parameter is needed.

Numeric

See an example in the getDays function.

currentDateTime

Returns to a Date Time field today’s date and time. No parameter is needed.

Numeric

dayOfMonth

Returns the day of the month of a Date, Date Time or strings in param1, i.e., dayOfMonth(DateField).

Usually Numeric, but it can also be Text/Note

decimals

Rounds the Number field in param1 to the number of decimals in param2, e.g., decimals(NumberField, 2), where, for instance, the number 10.4362 would be displayed as 10.44

Numeric

Example: decimals(VALUE(Order total), 2), meaning that the "Order total" Number field would show its value with 2 decimals, like 3,034.50

exp

Returns the exponential of param1, which may be a Number field or a constant number, i.e., exp(NumberField) or exp(500), respectively.

Numeric

fieldCreated

Returns the date when the field content was created. Param1 is the ID of the field, and must be given as a reference, i.e., fieldCreated(REF(AnyTypeOfField)).

Note: No Date will be shown for deleted data.

Usually Numeric, but it can also be Text/Note

Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function.

Example: toDateString(fieldCreated(REF(Order))

fieldCreator

Returns USERNAME or USERID of field content creator. Param1 is the ID of the field, and must be given as a reference, REF(). Param2 can be "1" or "0", where 1 displays the USERID and 0 displays the USERNAME, i.e., fieldCreator(REF(AnyTypeOfField , "1")).

Note: No information is returned for deleted data.

Text/Note

Example: fieldCreator( REF(Order) , "1" )

fieldCreatorEmail

Returns email address of field content creator, or an empty string if missing. Param1 is the ID of the field, and must be given as a reference, i.e., fieldCreatorEmail(REF(AnyTypeOfField)).

Text/Note

fieldCreatorName

Returns full name of field content creator. Param1 is the ID of the field, and must be given as a reference. Param2 can be 1 or 0, where 1 displays the last name first (Smith, John) and 0 displays the first name first (John Smith).

Text/Note

Example: fieldCreatorName( REF(Order) , 0 )

fieldUpdated

Returns the date when the field content was updated. Param1 is the ID of the field, and must be given as a reference, REF().No information is returned for deleted data.

Numeric

fieldUpdator

Returns USERNAME or USERID of field content update performer. Param1 is the ID of the field, and must be given as a reference, REF(). If param2 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned. No information is returned for deleted data.

Text/Note

See an example of the parameters in the fieldCreator function.

fieldUpdatorEmail

Returns email address of field content update performer, or an empty string if missing. Param1 is the ID of the field, and must be given as a reference, REF().No information is returned for deleted data.

Text/Note

fieldUpdatorName

Returns full name of field content update performer. Param1 is the field ID of the field, and must be given as a reference, REF(). If param2 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith). No information is returned for deleted data.

Text/Note

floor

Returns the largest integer not greater than param1 (e.g., 10.1 → 10), which may be a Number field or a constant number, i.e., floor(NumberField) or floor(10.1)`, respectively.

Numeric

formLink

Returns to a Text or Note field, the link string (the URL) of a Form specified as a reference (REF()) in param1, i.e., formLink(REF(FormID)). It can be very useful to get the URL of a form to use it, for example, when sending an e-mail, so the recipient can refer to that form with the current record shown. Note that the recipient using the link will normally be asked for authentication on the login page before the chosen form and record are shown.

Note: Form IDs can be found as fields in the Field drop-down of the Formula editor. The chosen form must have the same main table as the current Trigger table of the rule.

Text/Note

Example: formLink(REF(Order information form))

getDays

Returns the number of days of param1, which must be a Date field or a substraction of Dates, i.e., getDays(DateField) or getDays(DateField2-DateField1), respectively.

Numeric

Example: getDays(VALUE(Installation finish date)-VALUE(Installation start date)) or getDays(currentDate()-VALUE(Installation start date)), in order to know how many days an installation took to be ready, or how many days is taking until the current day, respectively.

Note that the substraction of dates can also be done without this function: (VALUE(Installation finish date)-VALUE(Installation start date))/D, where D is a Date constant (D=day).

getMobilePhone

Returns the mobile phone number of the user (registered in User administration) with the username or userId supplied in param1. If no such user exists, it returns an empty string ("").

Text/Note

Examples:

getMobilePhone("MANAGER")

getMobilePhone(23)

getMobilePhone(fieldCreator(REF(100100), true/false))

getMobilePhone(recordCreator(true/false))

getRecordGroup

Returns the name or Id (depending on if Formula type is Text/Note or Numeric, respectively) of the group the record in param1 belongs to.

Param1 can be:

- A constant value (i.e., the record name or record Id), e.g., getRecordGroup("MyRecord1", 20) or `getRecordGroup(100103, notused), or

- A variable value (i.e., the Relation field pointing to the Parent record), e.g., getRecordGroup(RelationFieldToParent, 20),

where Param2 is only needed for Text/Note and is the Table Id* of the record named by Param1. For Numeric type, Param2 can be set to null or notused.

Usually Text/Note, but it can also be Numeric.

Mostly used in combination with the function moveToRecordGroup(param1) which is a very effective way to move a child record to whatever group the parent record belongs to.

Example: moveToRecordGroup(getRecordGroup(RelationFieldToParent, 20)), where the target record will be moved to the group of its parent record, following the relation and the table id specified in param1 and param2 of the getRecordGroup function.

For this, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the record will be moved to the parent’s group and the "Dummy Checkbox" will be automatically ticked.

Read more in the moveToRecordGroup function’s notes.

If the function is used alone:

Example: getRecordGroup(RelationFieldToParent, 20) or getRecordGroup(100103, notused), with target field as Text, Note, Number or Checkbox.

*Table Id can be obtained from the URL of Open table records icon in Data model editor (looks like istools/apps/2063/forms/magical/2?view=window, where the number before "?" is the Table Id.

Note: The result of using this function with an invalid input will be 0.

getRecordName

Returns to a Text or Note field the name of the target record. No parameter is needed. In case the Record name is a number, this function must be converted to a string, i.e., str(getRecordName()).

Text/Note

getFieldLabel

Returns to a Text or Note field the name of the field in parameter1, i.e., getFieldLabel(AnyTypeOfField).

Text/Note

getUserEmail

Returns to a Text or Note field the email address of the user with username supplied in param1 within quotation marks, e.g., getUserEmail("SMITH").

Text/Note

hasRole

Checks whether the user executing the function has the role mentioned in param1, within quotation marks. e.g., hasRole("MANAGER"). Returns 1 if the user has the role, else 0.

Numeric

If this function is used alone, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. Based on the example above, if the rule’s criteria is fulfilled and the user doing so has the "MANAGER" role, then the "Dummy Checkbox" will be automatically ticked (set to 1).

But if this function is used as nested in other function, it can be very useful and no need of a Checkbox is required.

Example: if( hasRole("MANAGER") , `concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), "Awaiting for manager’s revision" ), where if the role MANAGER fulfills the rule’s criteria, then the "Comments" Note field will get the value "Reviewed by the manager on 15-12-2012 15:41:54". But if any other role fulfills the rule’s criteria, "Comments" will get the value "Awaiting for manager’s revision".

hasValue

Checks whether the field with field id param1 has a value assigned to it, returns 1 if a value exists, 0 otherwise. Param1 must be a reference to a field, REF(), e.g., hasValue(REF(AnyTypeOfField)).

Any

Example: Best quotation = if(hasValue("Quotation1") AND hasValue("Quotation2"), if("Quotation2">"Quotation1", "Quotation1", "Quotation2"), "Waiting for both quotations to be filled in"), would first check if the "Quotation" fields have a value before taking the action in the nested if.

hour

Returns the hour of the day (24 hours) of a Date, Date time or strings in param1.

Usually Numeric, but it can also be Text/Note

Returns the value of param2 only if param1 is positive, otherwise returns the value of param3, i.e., if(condition here is True, do this, else do that).

Optionally, other functions can be nested. Remember that both the rule’s criteria and the first parameter need to be fulfilled in order to return a result.

Any, depending on the type of target field.

Example 1: if( hasRole("MANAGER") , concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), "Awaiting for manager’s revision"), where if the role MANAGER fulfills the rule’s criteria, then the "Comments" Note field will get the value "Reviewed by the manager on 15-12-2012 15:41:54". But if any other role fulfills the rule’s criteria, "Comments" will get the value "Awaiting for manager’s revision".

Note: In case no action is wished, i.e, no value needs to be returned by the formula, the constant IGNORE can be typed as the proper parameter in the formula box. For instance, if( hasRole("MANAGER"), concat("Reviewed by the manager on ", toDateTimeString(currentDateTime()) ), IGNORE), where if the role MANAGER does not fulfill the rule’s criteria, then the "Comments" Note field will not get a value (not even "0", which would be the case if typing 0 or False as param3 instead).

Example 2: if( Order total > 1000, Requires approval from Line Manager = 1, 0), where if the rule’s criteria is fulfilled together with the "Order total" greater than 1000, then the "Requires approval from Line Manager" Checkbox will be ticked, otherwise it will remain as false.

isLeapYear

Returns 1/True if the year represented by param1 (Date or Date time) is a Leap year; otherwise returns 0/False.

Any, depending on the type of target field.

Example 1: isLeapYear(Order requested on)

Example 2: isLeapYear(parseDate("2012-05-30")), where the result would be 1/True since 2012 was a leap year.

Example 3: isLeapYear(parseDateString("31-02-2011", "dd-MM-yyyy")), where the result would be 0/False since 2011 was not a leap year.

Example 4: if(isLeapYear(Order requested on), "Customer has 1 additional day in February to pay", 0)

lastFieldModifiedDate

Returns the latest date upon which the data of the field in param1 was modified, i.e., lastFieldModifiedDate(AnyTypeOfField).

Note: No Date will be shown for deleted data.

Usually Numeric, but it can also be Text/Note.

Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function.

Example: toDateString(lastFieldModifiedDate( REF(Order))).

lastFieldModifiedName

Returns the name of the last user who modified the field in param1. Param2 can be 1 or 0, where 1 displays the user’s last name first (Smith, John) and 0 displays it as normal (John Smith), i.e., lastFieldModifiedName(AnyTypeOfField, 1).

Note: No name will be shown for deleted data.

Text/Note

Example: lastFieldModifiedName(REF(Order), 0).

lastFieldModifiedUserName

Returns the username of the last user who modified the field in param1, i.e., lastFieldModifiedUserName(AnyTypeOfField).

Note: No username will be shown for deleted data.

Text/Note

lastRecordModifiedDate

Returns the latest date upon which the data of the record was modified. No parameter is needed.

Usually Numeric, but it can also be Text/Note.

Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function.

Example: toDateString(lastRecordModifiedDate()).

lastRecordModifiedName

Returns the name of the last user who modified the target record. Param1 can be 1 or 0, where 1 displays the user’s last name first (Smith, John) and 0 displays it as normal (John Smith), e.g., lastRecordModifiedName( 0).

Text/Note

lastRecordModifiedUserName

Returns the username of the last user who modified the target record. No parameter is needed.

Text/Note

length

Returns the number of characters of param1, i.e., length(AnyTypeOfField).

Numeric

Returns the natural logarithm of param1, which may be a Number field or a constant number, i.e., ln(NumberField) or ln(500), respectively.

Numeric

log

Returns the common logarithm of param1, which may be a Number field or a constant number, i.e., log(NumberField) or log(500), respectively.

Numeric

match

Checks if the string in param1 matches the regular expression pattern in param2, i.e., match(AnyTypeOfField, expression to match).

Optionally, this function can be nested. Remember that both the rule’s criteria and the match function need to be fulfilled in order to return a result.

Returns 1 if match is found, else 0.

Text/Note

In this case, the Formula type must be Text/Note, due to the regular expression, even if the target field is a Checkbox or a Number.

Regular expressions can be obtained at regular-expressions.info and quotation marks must be used to enter them in the formula.

This function is rarely used alone, but if that is the case, it is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users.

Example 1: match(VALUE(Email address), "\b[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}\b"), meaning that if the rule’s criteria is fulfilled and the value entered by the user in the "Email address" Text field matches the format asdf@asdf.asdf (as interpreted by the regular expression in param2), then the "Dummy Checkbox" will be automatically ticked.

Also, this function can be very useful if it’s used as nested in other function.

Example 2: if( (match(VALUE(Email address), "\b[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}\b")), "Email format validated", "-Email format is not valid, please provide a valid format" + clearField(REF(Email address))), meaning that if the rule’s criteria is fulfilled and the value entered by the user in the "Email address" Text field matches the format asdf@asdf.asdf (as interpreted by the regular expression), then the "Comments" field will be set with the text "Email format validated", otherwise (if it does not match) two actions will be performed: another text will be set on "Comments" and the invalid value in the "Email address" field will be removed.

max

Returns the maximum value of a variable number of parameters, i.e., the largest of those values (usually Number or Dates fields), max(NumberField1, NumberField2, NumberField3).

Numeric

maxFields

For related tables only. Returns to a Number field of a parent table the maximum value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., maxFields(ChildRelationField, ChildNumberField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = maxFields(REF(MyChildRelationField), REF(MyChildNumberField))

This function can optionally be used with up to six parameters:

-param3 and param4 together work as lower and upper bound filter condition; e.g., maxFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account.

-param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., maxFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc"), meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.

Numeric

Example 1: maxFields(REF(Order), REF(Suborder Total)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Maximum amount in Suborders", which is a Number field of the parent Order table.

Order A. Maximum amount in Suborders=150

Suborder A1. Suborder total=150

Suborder A2. Suborder total=120

Example 2, using param5 and param6: maxFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087)), where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will return the maximum value only among those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed.

Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively. To set a null value as param6, use the keyword null or 0.

median

Returns the median of a variable number of parameters, usually Number or Dates fields, median(NumberField1, NumberField2, NumberField3).

Numeric

medianFields

For related tables only. Returns to a Number field of a parent table the median value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., medianFields(ChildRelationField, ChildNumberField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = medianFields(REF(MyChildRelationField), REF(MyChildNumberField))

Numeric

Example: medianFields(REF(Order), REF(Suborder Total)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Suborders median amount", which is a Number field of the parent Order table.Order A. Suborders median amount=8

Suborder A1. Suborder total=2

Suborder A2. Suborder total=10

Suborder A3. Suborder total=8

min

Returns the minimum value of a variable number of parameters, i.e., the smallest of those values (usually Number or Dates fields), min(NumberField1, NumberField2, NumberField3).

Numeric

minFields

For related tables only. Returns to a Number field of a parent table the minimum value of a field (param2, usually Number or Date fields) placed in the records of a table with relation (param1) to the parent table, i.e., minFields(ChildRelationField, ChildNumberField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = minFields(REF(MyChildRelationField), REF(MyChildNumberField))

This function can optionally be used with up to six parameters:

-param3 and param4 together work as lower and upper bound filter condition; e.g., minFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account.

-param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., minFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc"), meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.

Numeric

Example 1: minFields(REF(Order), REF(Suborder Total))`, where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder Total" is a Number field of the Suborder table too. The result of this formula will be set on "Minimum amount in Suborders", which is a Number field of the parent Order table.

Order A. Minimum amount in Suborders=150

Suborder A1. Suborder total=150

Suborder A2. Suborder total=120

Example 2, using param5 and param6: minFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087)), where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will return the minimum value only among those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed.

minute

Returns the minutes of a Date Time field in param1, i.e., minute(DateTimeField).

Numeric

mod

Returns param1 modulus param2.

Numeric

month

Returns the month of the year of a Date, Date time or strings in param1. January is 0 and December is 11.

Usually Numeric, but it can also be Text/Note

moveRelatedRecords

For related tables only. Can move the target record and related records at once (relation specified in param2) to a certain record group (specified in param1). Param2 is a structure that describes which relation field id is used; moveRelatedRecords(VALUE(Group name), "[{'relation':<ChildRelationFieldId>}]"). Returns 1 if successfully moved, else 0.

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentCheckboxField = moveRelatedRecords( "Group 1", "[{'relation':100656}]")

Any

It is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the parent record and its child records will be moved to the specified group and the "Dummy Checkbox" will be automatically ticked.

It’s very useful to have this function nested and/or used in combination with other functions.

Example: if(hasRole("CLIENT"), moveRelatedRecords("CLIENT DATA", "[{'relation':100656}]"), 0), which will allow the user to update certain information based on his role. In this case, a Client can trigger a Work record to move it together with all Activity records to the Record group Client data. The Field Id "100656" is the relation field between Activity and Work (i.e., Child table → Parent table).

getRecordGroup(RelationFieldToParent, 20)

Example 2: moveRelatedRecords(getRecordGroup(RelationFieldToParent,8), "[{'relation':100656, 'children':[{'relation':100670}]}]"), where the child records of the triggered record and the child records of the first ones (i.e., records 2 levels below the parent table: GrandChild table → Child table → Parent table) will be moved to the Record group of the parent record (due to the getRecordGroup function). Let’s say that triggering a Work record will move not only its Activity records but the Task records or those Activies too, to whatever group the Work belongs to.

Note: The characters in red are part of the syntax of the structure, and therefore must be always written in this way.

moveToRecordGroup

Moves the target record to the record group with the value specified in param1. If the value is a constant, type it within quotation marks, e.g., moveToRecordGroup("CLIENT RECORD GROUP"); for a variable value, use a Drop-down or Text field where a user can enter the group name in a form or in an import, e.g., moveToRecordGroup(VALUE(Client)) where Client in this example is a Drop-down field which list’s values should match the record group names. Returns 1 if successfully moved, else 0.

Text/Note

It is recommended that the target field (where the result of the formula, i.e, 1 or 0, will be set) is a Checkbox created only for this purpose. Such a Checkbox does not need to be shown in forms, to avoid confusion to users. This way, if the rule’s criteria is fulfilled, then the current record will be moved to the specified group and the "Dummy Checkbox" will be automatically ticked.

Example 1:

Criteria → Client = CHANGED

Action → Set value of a field with a formula →

Formula type = Text/Note

DummyCheckbox = moveToRecordGroup(VALUE(Client))

Rule name → Move orders to proper record group

Description → The target order will be moved to the proper record group, depending on the value chosen in the Client drop-down, e.g., if Client is changed to "Industrix", the order will be moved to the Record group of the same name.

It’s very useful to have this function nested and/or used in combination with other functions.

Example 2: if( hasRole("CLIENT"), moveToRecordGroup("CLIENT DATA"), 0), which will allow the user to update certain information based on his role.

padLeft

Returns the string of param1 left padded to the length of param2 with pad character param3, useful to standardize strings by filling out its left part. For instance, padLeft(VALUE(Bank account number), 17, 0), would add a 0 to those numbers that have less than 17 digits to make them all the same length, like 00024856468576379, 00000000087654365, etc.

Text/Note

padRight

Returns the string of param1 right padded to the length of param2 with pad character param3. See padLeft.

Text/Note

parseDate

Exclusively used to compare dates in other functions (e.g., in if, match and as param5 in countFields, sumFields, etc.), it validates a string (param1, within quotations) as Date or Date time field using the date/time format of the triggering user, e.g., if(DateField== parseDate("2013-09-24"), 1, 0).

Note: The function will give different results or fail, for users with different/incompatible date/time formats. Consider using ParseDateFormat(param1, param2, param3) instead.

Numeric

parseDateFormat

Exclusively used to compare dates in other functions (e.g., in if, match and as param5 in countFields, sumFields, etc.), it validates a Date or Date time field (param1) using a specific format for the date (param2, within quotations) and a specific format for the time (param3, within quotations), e.g., if(parseDateFormat(VALUE(P100588), "2013-09-24", "12:00:00"), 1, 0). In case time is not needed, set param3 to an empty string.

Numeric

You can also try this function nested as follows:

if(hasValue(REF(P100588)), parseDateFormat(VALUE(P100588), "YYYY-MM-DD", "HH:MM:SS"), 0)

pathValue

For related tables only. Returns the value of a field from a related record, following the specified path; i.e., can get a parent’s field value or a child’s field value, or a more complex path like getting a parent’s parent field value.

-For a simple direct relation to get a field value from a parent record, use: pathValue("0_FChildRelationField_P0_ParentAnyTypeOfField_0"), which will return to the target field of the child record the value in the "ParentAnyTypeOfField", using the "ChildRelationField" that relates towards the parent. Note that quotation marks and 0_F, _P0_, _0 keywords are needed.

-For a complex direct relation to get a field value from a parent record, use: pathValue("0_FChildRelationFieldToParent1_FParent1RelationFieldToParent2_P0_Parent2AnyTypeOfField_0"), which will return to the target field of the child record the value in the "Parent2AnyTypeOfField", following first the path to the parent using "ChildRelationFieldToParent1" and then using "Parent1RelationFieldToParent2" that relates towards the parent of the first parent. Note that quotation marks and 0_F, 0_F, _P0_, _0 keywords are needed.

-For a simple reverse relation to get a field value from a child record, use: pathValue("0_RChildRelationField_P0_ChildAnyTypeOfField_0"), which will return to the target field of the parent record the value in the "ChildAnyTypeOfField", using the "ChildRelationField" that relates towards the parent. Note that quotation marks and 0_R, P0, 0 keywords are needed. Note also that the resulting value will come from any child record, i.e., random selection (to get the field value from the specific triggered child record, use the _triggerRecordValue function).

Any, depending on the type of target field.

For instance, having the model Region ← Country ← City (where Country is parent of City, and Region is parent of Country), in the "Country" form, pathValue makes it possible for the record "Sweden" to fetch the value in the field called "Region responsible" that is part of the "Region" table. Since the country "Sweden" is related to the region "Northern Europe", the result would be "Erik Svensson" that can be place in a field called "Responsible for all countries in the region" as part of "Country" table located in the "Country" form. Looking at the Example 1 below, 100301 is the ID of the relation between Country and Region, and 100200 is the ID of the "Region responsible" field of Region. The field at the top of the Formula Editor which will get the result is "Responsible for all countries in the region" of Country. The formula is Text/Note type.

Example 1: pathValue("0_F100301_P0_1002000")

Example 2: pathValue("0_F100301_F100303_P01002010"), for direct relations, will return to the target field of the child record using the relation with ID "100301" (to a parent table), and from there, the relation with ID "100303" (to the parent’s parent table), the value in the field with ID "100201" of the parent’s parent table.

Based on the model above, the value of the field "Region responsible" can be got from a "City" record, i.e., from the record "Stockholm" in the "City" form, meaning that it will follow the relation to the parent, then follow the relation to the parent’s parent to finally get the value.

Example 3: pathValue("0_R100302_P0_100300_0"), for reverse relation, will return to the target field of the parent record using the relation with ID "100302" (from the child table), the value in the field with ID "100300" of a child record (_randomly, only in case no filter is set, as in example4).

The Example3 makes it possible to follow a path downwards, to get values from one child record. For instance, Country can get a value from a City: e.g., "Sweden" can get a value from the record "Stockholm".

Example 4: pathValue("0_R100302(100058!2!>!#8)_P0_100300_0"), for reverse relation, will return to the target field of the parent record using the relation with ID "100302" (from a child table), the value in the field with ID "100300" of the child record matching the filter (randomly, if resulted in several) made on its field with ID "100058", of type Number, larger than 8.

For reverse relation, the triggerRecordValue function can be used instead, where the value of the triggered child record is returned.

Notes: The characters in red are part of the syntax of the path, and therefore must be always written in this way (F for direct relations and R for reverse relations).

Request help to the IS Tools support team for complex filter syntax (1 Text, 2 Number, 3 Date, 4 Note, 5 Drop-down, 6 Checkbox, 7 Relation, 8 File, 9 Link, 11 Date-time).

Field Id’s can be found via the Field usage report in the Data model editor.

pathValueExists

To be used in combination with the if and pathValue functions, pathValueExists makes sure that the relations used in the path and the value to be fetched exist.

if(pathValueExists("0_F100301_P0_100200_0"), pathValue("0_F100301_P0_100200_0"), IGNORE)

If pathValueExists returns 1/True (i.e., the path is valid), pathValue is executed. Othewise, returns the value in param3, which is usually 0/False, or returns nothing if the constant IGNORE is used, as in the example above.

Any, depending on the type of target field.

rand

Returns a random number between 0 and 1. No parameter is needed.

Numeric

randomString

Returns a random string. The length can be set with param1 (if not set the length is 8).

Text/Note

Both rand and randomString can be used, for instance, to create unique concatenated alphanumeric identificators.

recordCreated

Returns the date when the record was created.

Usually Numeric, but it can also be Text/Note

Usually the target field is a Date. But if the resulting value is desired to be stored in a Text or Note field, then this function must be nested in a toDateString function.

Example: toDateString(recordCreated()).

recordCreator

Returns USERNAME or USERID of record creator. If param1 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned.

Text/Note

Example: recordCreator("0").

recordCreatorEmail

Returns email address of record creator, or an empty string if missing.

Text/Note

recordCreatorName

Returns full name of record creator. If param1 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith).

Text/Note

recordId

Returns the record Id of the target record.

Text/Note or Numeric

Can be used as nested in other functions like getRecordGroup.

recordUpdated

Returns the date when the record was updated, or 0/’’ (empty string) if missing. No information is returned for deleted data.

Usually Numeric, but it can also be Text/Note

recordUpdator

Returns USERNAME or USERID of record update performer. If param1 is given as "true" or "1", USERID is returned. Otherwise USERNAME is returned. No information is returned for deleted data.

Text/Note

Example: recordUpdator("0")

recordUpdatorEmail

Returns email address record update performer, or an empty string if missing. No information is returned for deleted data.

Text/Note

recordUpdatorName

Returns full name of record update performer. If param1 is given as ‘true’ or 1, the last name is displayed first (Smith, John). Otherwise the first name is displayed first (John Smith). No information is returned for deleted data.

Text/Note

relatedValue

For related tables only. Returns to a field of a child table the field value of a parent table (param2), using the relation (param1) between these tables, i.e., "copies" the field value of a parent record to a field of all related child records; relatedValue(ChildRelationField, ParentAnyTypeOfField).

Target: Child table

Trigger: Child table or Parent table

Criteria: Any

Action: ChildField = relatedValue(REF(MyChildRelationField), REF(MyParentField))

Any, depending on the type of target field.

This is a function that can perform an action on the child table from the parent table (i.e., has the target on the child); the opposite as in other functions for related tables such as maxField, minFields, etc.

Example: relatedValue(REF(Order), REF(Order requested by)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Order requested by" is a Text field of the parent table. The result of this formula will be set on "Main order requested by", which is a Text field of the child Suborder table; meaning that if the rule’s criteria is fulfilled, the name of the person requesting the order, will be copied to all suborders that belong to that order.

replace

If the value in param2 is found in the value in param1, then it replaces that string for the one in param3, i.e., replace(Text/NoteField, "String to be found and replaced", "Replacement string"). Note that param2 and param3 can be either fields or strings; for the latter one, quotation marks are needed.

Text/Note

Example: replace(VALUE(Full address), "PC", "Postal code:"), meaning that if the text "PC" is found in any part of the value of the "Full address" Text field, then it will be replaced with "Postal code" instead. In this case the target field is the same as param1. Based on the example above, the original value in "Full address" being "6300 Legacy Drive, PC: 75024, Plano, TX", would be replaced for "6300 Legacy Drive, Postal Code: 75024, Plano, TX" after this formula is applied.

round

Returns param1 rounded to the closest integer, e.g., 10.3 → 10, 10.5 → 11; round(NumberField).

Numeric

second

Returns the seconds of a Date time field in param1, i.e., second(DateTimeField).

Numeric

sin

Returns the sine of param1, which may be a Number field or a constant number, i.e., sin(NumberField) or sin(500), respectively.

Numeric

sinh

Returns the hyperbolic sine of param1, which may be a Number field or a constant number, i.e., sinh(NumberField) or sinh(500), respectively.

Numeric

sqrt

Returns the square root of param1, which may be a Number field or a constant number, i.e., sqrt(NumberField) or sqrt(500), respectively.

Numeric

str

Converts param1 to text and returns it. Useful to get a string after nesting other functions, e.g., str(getRecordName()).

Text/Note

substring

Returns the part of the string in param1 between the index (position) specified in param2 and param3 (strings are 0 indexed).

Text/Note

Example: substring(Serial number, 0, 2), where having "ABC123" as Project number this function would return "ABC".

sum

Returns the sum of a variable number of parameters, which may be Number fields or constant numbers, i.e., sum(NumberField, NumberField2, 4500).

Numeric

sumFields

For related tables only. Returns (to a Number field of a parent table) the sum of a Number field (param2) placed in the records of a table with relation (param1) to the parent table, i.e., sumFields(ChildRelationField, NumberField).

Target: Parent table

Trigger: Child table or Parent table

Criteria: Any

Action: ParentNumberField = sumFields(REF(MyChildRelationField), REF(MyChildNumberField))

This function can optionally be used with up to six parameters:

-param3, as standalone, is interpreted as a filter condition made on param2; e.g., sumFields(REF(MyChildRelationField), REF(MyChildNumberField), 12), meaning that only those records having the field "MyChildNumberField" with value "12" will be taken into account.

-param3 and param4 together work as lower and upper bound filter condition; e.g., sumFields(REF(MyChildRelationField), REF(MyChildNumberField), 1, 5), meaning that only those records having the field "MyChildNumberField" with value between 1 and 5 will to be taken into account.

-param5 and param6 together work as a specific filter condition, using a field reference (Text, Date, Number, Checkbox or Drop-down) and its value; e.g., sumFields(REF(MyChildRelationField), REF(MyChildNumberField), notused, notused, REF(MyChildTextField), "abc")), meaning that only those records having the field "MyChildTextField" with value "abc" will be taken into account. In case param3 and param4 are not used, type keyword notused.

Numeric

Example 1: sumFields(REF(Order), REF(Suborder Total)), where "Order" is the Relation field of the Suborder table pointing to the parent Order table, and "Suborder total" is a Number field of the Suborder table too. The result of this formula will be set on "Order grand total", which is a Number field of the parent Order table.

Order A. Order grand total=4

Suborder A1. Suborder total=5

Suborder A2. Suborder total=3

Example 2, using param3 and param4: sumFields(REF(Order), REF(Suborder Total), 2, 3), where the third and fourth parameters bound the "Suborder Total" to have a value between 2 and 3; i.e., the formula will calculate the sum of the total figures considering only those suborders that have such figures within this range.

Example 3, using param5 and param6: sumFields(REF(Order), REF(Suborder Total), notused, notused, REF(Suborder manager), REF(P101087)), where the fifth parameter is a drop-down field and the sixth parameter is the undefined field "John Smith" belonging to the field group "Lists of managers", which is assigned to the drop-down "Suborder manager" of the Suborder table. The formula will calculate the sum of the total figures considering only those suborders that have John Smith as manager. Note the keyword notused must be typed as param4 and param5, in case those parameters are not needed.

Also note that Text, Date, Number and Checkbox fields can be used as param5, while param6 can be typed, for example, as "Approved", parseDate("2013/06/13"), 550, 1, respectively.

Example 4, using all 6 parameters: sumFields(REF(Order), REF(Suborder Total), 2, 3, REF(Suborder manager), REF(P101087)), where the formula will calculate the sum of the total figures considering only those suborders that have such figures between 2 and 3 and that also have John Smith as manager. To set a null value as param6, use the keyword null or 0.

tan

Returns the tangent of param1, which may be a Number field or a constant number, i.e., tan(NumberField) or tan(500), respectively.

Numeric

tanh

Returns the hyperbolic tangent of param1, which may be a Number field or a constant number, i.e., tanh(NumberField) or tanh(500), respectively.

Numeric

toDatePattern

Returns (to a Text or Note field) the value of a Date or Date time field in param1 as a string formatted with pattern specified in param2 within quotation marks, e.g.., toDatePattern(DateTimeField, "yyyy-MM-dd KK:mm:ss").

The following pattern characters are supported: y = Year, M = Month, w = Week in year (number), W = Week in month (number), D = Day in year (number), d = Day in month(number), F = Day of week in month (number), E = Day in week (text), a = Am/Pm marker (text), H = Hour in day (0-23), k = Hour in day (1-24), K = Hour in day am/pm (0-11), h = Hour in day am/pm (1-12), m = Minute in hour, s = Second in minute.

Numeric

Note that the case of the pattern is sensitive; for instance, is not the same to type w or W.

toDateString

Returns (to a Text or Note field) the value of a Date field in param1 as a string, i.e., toDateString(DateField).

Numeric

See examples of use in lastFieldModifiedDate and lastRecordModifiedDate functions.

toDateTimeString

Returns (to a Text or Note field) the value of a Date Time field in param1 as a string, i.e., toDateTimeString(DateTimeField).

Numeric

toLower

Returns param1 as a string in lower case, i.e., toLower(TextOrNoteField).

Text/Note

toUpper

Returns param1 as a string in upper case, i.e., toUpper(TextOrNoteField).

Text/Note

triggerRecordValue

For related tables only. Returns to a field of the target parent table, the value of the field in param1 from the triggered child record, i.e.:

Target: Parentgger: Child

Criteria: ChildField = <any condition>

Action: ParentField = triggerRecordValue(ChildField)´

Text/Note

Example: If a Drop-down field on a child record is changed, then set the value of that child’s drop-down on a Text field of its parent record. Having Project as parent table and Stage as child table (i.e., Project as target table and Stage as trigger table), the criteria would be "Stage status" drop-down == CHANGED, then set the value of "Project latest status" with the formula triggerRecordValue(Stage status), i.e., triggerRecordValue(REF(P100451))

Project A. Project latest status = Approved by customer

Stage A1. Stage status = Sent to customer

Stage A2. Stage status = Approved by customer

userLatitude

Returns user latitude if available.

Numeric

Mostly used in combination with other functions. See an example in the userLocationTimestamp function.

userLocationAccuracy

Accuracy in meters for userLatitude() and userLongitude().

Numeric

Mostly used in combination with other functions. See an example in the userLocationTimestamp function.

userLocationTimestamp

Timestamp when userLatitude() and userLongitude() were recorded.

Numeric

Mostly used in combination with other functions.

Example: if( userLocationAccuracy() < 90 && userLocationTimestamp() < currentDateTime() - 10/1440 , userLatitude() , 0), which will get the latitude position of a user if no more than 10 minutes have passed and precision is closer than 90 meters.

Note that there are also useful functions for geolocation via validations and IQL.

userLongitude

Returns user longitude if available.

Numeric

Mostly used in combination with other functions.

week

Returns the week of year of param1, which may be a Date, Date Time field or strings, i.e., week(DateOrDateTimeField).

The week is defined as a 7-day-period having as first day the one set by the application administrator in the Application preferences form (otherwise, Monday is the default value).

Numeric

year

Returns the year of param1, which may be a Date, Date Time or strings, i.e., year(DateOrDateTimeField).

Usually Numeric, but it can also be Text/Note