As part of our import template capability, a field value can be filled with a function calculated property
in the form =<function>(parameters). So for instance, to get a lowercase value of the column C,
the expression =lower(C) could be used.
This document details the available functions and their use.
| Function | Description | Output Type | Example |
| -------- | -------------- | ----------- | ------------------------------------------ |
| + | Add two values | Numeric | If C = 2, then =C + 1 evaluates to 3 |
| - | Subtraction | Numeric | If C is 6, then =C - 4 evaluates to 2 |
| * | Multiplication | Numeric | If C is 4, then =C * 2 evaluates to 8 |
| / | Division | Numeric | If C is 12, then =C / 2 evaluates to 6 |
| % | Modulus | Numeric | If C is 9, then =C % 5 evaluates to 4 |
| ^ | Bitwise XOR | Numeric | |
| (Pipe) | Bitwise OR | Numeric | |
| & | Bitwise AND | Numeric | |
| Function | Description | Output Type | Example |
| -------- | ---------------------- | ----------- | ------------------------------------------ |
| < | Less than | Boolean | |
| > | Greater Than | Boolean | |
| <= | Less than or equal | Boolean | |
| >= | Greather than or equal | Boolean | |
| <> | Not Equal | Boolean | |
| != | Not Equal | Boolean | |
| = | Equal | Boolean | |
| Function | Description | Output Type | Example |
| ------------------------- | --------------------------------------------------- | ---------------------------------------- | --------------------------------------------------------- |
| IF(v,if_true,if_false) | Return if_true if v is true, if_false otherwise | Depends on if_true or if_false types | =IF(true,"A","B") is "A", =IF(false,"A","B") is "B" |
| AND(a,b) | Return true if both values are true | Boolean | |
| OR(a,b) | Return true if either value is true | Boolean | |
| NOT(x) | Return the inverse of value | Boolean | =NOT(true) is false, =NOT(false) is true |
Selections: SWITCH, CASE (syntax see spec)
| Function | Description | Output Type | Example |
| -------------- | ---------------------- | ----------- | ------------------------------------------ |
| MIN(a,b,...) | |
| MAX(a,b,...) | |
| SUM(a,b,...) | |
| AVG(a,b,...) | |
| COUNT |
| ROUND(x) |
| ROUNDDOWN(x) |
| ROUNDUP(x) |
| Function | Description | Output Type | Example |
| ------------------------------- | --------------------------------------- | ----------- | ------------------------------------------------------------------------ |
| LEFT(s, n) | Takes leftmost characters | String | =LEFT("abcd", 2) evaluates to "ab" |
| RIGHT(s, n) | Takes rightmost characters | String | =RIGHT("abcd", 2) evaluates to "cd" |
| MID(s,start,count) | Takes characters from middle | String | =MID("abcd", 1, 2) evaluates to "bc" |
| LEN(s) | Length of string | Integer | =LENGTH("abcdef") evaluates to 6 |
| FIND(substring,s) | Find position in string for a substring | Integer | =FIND("ab", "xabc") evaluates to 1 |
| SUBSTITUTE(s,old,new) | Replaces old with new in string | String | =SUBSTITUTE("abcdef", "cd", "11") evaluates to ab11ef |
| CONCAT(s1,s2) | Joins two strings | String | =CONCAT("ab", "cd") evaluates to "abcd" |
| CONTAINS(s, substring) | Finds if string contains substring | Boolean | =CONTAINS("abcdef", "b") evaluates to true |
| LOWER(s) | Convert string to lowercase | String | =LOWER("ABCeFF") evaluates to "abdeff" |
| UPPER(s) | Convert string to uppercase | String | =UPPER("abcEEff") evaluates to ABCEEFF |
| GSUB(s,old,new) | Replaces old with new in string | String | =GSUB("abcdef", "cd", "11") evaluates to ab11ef |
| IS_BLANK(s) | Checks if string is blank | Boolean | =IS_BLANK("") is true, =IS_BLANK("xxx") is false |
| RESOLVE_COUNTRY_CODE(s) | Converts country into Alpha2 code | String | =RESOLVE_COUNTRY_CODE("Australia") evaluates to 'AU' |
| RESOLVE_REGION_CODE(s, s) | Converts region into area code | String | =RESOLVE_REGION_CODE("Australia", "Queensland") evaluates to 'QLD' |
| REMOVE_SCIENTIFIC_NOTATION(s) | Converts scientific notation to number | String | =REMOVE_SCIENTIFIC_NOTATION("9.19e+11") evaluates to '919000000000' |
| Function | Description | Output Type | Example |
| ------------------------- | ----------------------------- | ----------- | ---------------------------------------------------------------- |
| DAY(d) | Extract the day from a date | Integer | =DAY("2020-05-03") evaluates to 3 |
| MONTH(d) | Extract the month from a date | Integer | =DAY("2020-05-03") evaluates to 5 |
| YEAR(d) | Extract the year from a date | Integer | =DAY("2020-05-03") evaluates to 2020 |
| DATE(year,month,day) | Build a date from parts | Date | =DATE(2020, 4, 11) evaluates to a date 2020-04-11 |
| ADD_DAYS(date,days) | Add days to a date | Date | =ADD_DAYS("2020-03-11", 4) evaluates to a date 2020-03-15 |
| ADD_MONTHS(date,months) | Add months to a date | Date | =ADD_MONTHS("2020-03-11", 4) evaluates to a date 2020-07-11 |
| ADD_YEARS(date,years) | Add years to a date | Date | =ADD_YEARS("2020-03-11", 4) evaluates to a date 2024-03-11 |
| START_OF_MONTH(date) | Round date to start of month | Date | =START_OF_MONTH("2020-03-11") evaluates to a date 2020-03-01 |
| START_OF_YEAR(date) | Round date to start of year | Date | =START_OF_YEAR("2020-03-11") evaluates to a date 2020-01-01 |
| END_OF_MONTH(date) | Round date to end of month | Date | =END_OF_MONTH("2020-03-11") evaluates to a date 2020-03-31 |
| END_OF_YEAR(date) | Round date to end of year | Date | =END_OF_YEAR("2020-03-11") evaluates to a date 2020-12-31 |
| CURRENT_DATE() | Get today's date (organisation timezone) | Date | =CURRENT_DATE() evaluates to a date 2020-06-22 |
| Function | Description | Output Type | Example |
| -------------------------------------------------- | -------------------------------------------------------------- | ----------- | ----------------------------------------------------------- |
| INCLUDES(arr,v) | Array includes value | Boolean | If x=["a", "b"], then =INCLUDES(x, "b") evaluates to true |
| LENGTH(arr) | Array length | Integer | =LENGTH([1,2,5]) evaluates to 3 |
| SELECT_WITH_PROPERTY_VALUE(arr,property,v1,...) | Filter array of objects where property matches value | Array | |
| SUM_PROPERTY_VALUE(object,property) | Sum the value of the property extracted from array of objects | Numeric |
| Function | Description | Output Type | Example |
| ---------------------------------------------- | ------------------------------------------------------- | -------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| LOOKUP_AGENT_FOR_BRANCH(branch_name_or_code) | Finds agent related to branch | Agent Profile | If B='Branch1', =lookup_agent_for_branch(B) evaluates to the agent owning Branch1 |
| ALIGN_TO_SCHEDULE(date, schedule_name) | Finds the previous date in the targeted census schedule | Date | If D='15-01-2020' and E='Referral dates', =align_to_schedule(D) evaluates to the date before 15-01-2020 of the census schedule named Referral dates |
| LOOKUP_COURSE(course_name_or_code) | Finds the course with the given name | Course | If C='Course1', then =lookup_course(C) evaluates to the course object called Course1 |
| LOOKUP_INSTITUTION_FOR_COURSE(course_name_or_code) | Given a course name or code, finds the institution to which the course belongs | Institution | If C='Course1', then =lookup_institution_for_course(C) evaluates to the Institution object to which the course called Course1 belongs |
| GET_TEMPLATE_ATTRIBUTE(object, attribute) | Retrieving a templating attribute from the given object | String | If C='Course1' and Course 1 has a custom data field 'blah' set to 'abc', then =GET_TEMPLATE_ATTRIBUTE(LOOKUP_COURSE(C), 'course_blah') evaluates to 'abc' |
| BRANCH_EXISTS(agent_name_code, branch_name_code) | Test whether a branch exists under a specific agent, enabling branch to be used optionally | Boolean | If agent A1 has branch B1 only, then =IF(BRANCH_EXISTS('A1', 'B1'), 'B1', '') returns 'B1' and =IF(BRANCH_EXISTS('A1', 'B2'), 'B1', '') returns '' |