Formulas
Below are the formulas that can be used in UniFi. In all examples, the fixed values can be replaced by field ID's. For example, if you have a field with ID first_name then Left(first_name,5) will return the left 5 characters of whatever is entered in that field. Put your mouse over the formula in the table below to get more information.
Text formulas
| Formula | Output | Example |
|---|---|---|
| Concatenate(text1,text2...) | Text | Concatenate("John"," ","Smith") = "John Smith" |
| Left(text,length) | Text | Left("John",1)="J" |
| Right(text,length) | Text | Right("John",1)="n" |
| Mid(text,start,length) | Text | Mid("John",2,2)="oh" |
| Lower(text) | Text | Lower("John")="john" |
| Upper(text) | Text | Upper("John")="JOHN" |
| Proper(text) | Text | Proper("john smith")="John Smith" |
| Len(text) | Number | Len("John Smith")=10 |
| Numbervalue(text,thousand separator, decimal) | Number | Numbervalue("1,234.56",",",".")=1234.56 |
| Substitute(text, original, new, occurance number) | Text | Substitute("Q1, 2011","1","2",3)="Q1 2012" |
| Find(text to find,source text, occurance number) | Number | Find("Smith","John Smith",1)=6 |
| Exact(text1,text2) | True/False | Exact("John Smith","john smith")=False |
| Trim(text) | Text | Trim(" John Smith ")="John Smith" |
Number formulas
| Formula | Output | Example |
|---|---|---|
| Abs(number) | Number | Abs(-4) = 4 |
| Rand() | Number | Rand() = [random number] |
| Randbetween(lowest,highest) | Number | Randbetween(-100,100) = [random number between -100 and 100] |
| Round(number,decimals) | Number | Round(3.1415,2) = 3.14 |
| Rounddown(number,decimals) | Number | Round(3.1415,2) = 3.14 |
| Roundup(number,decimals) | Number | Round(3.1415,2) = 3.15 |
| Sqrt(number) | Number | Sqrt(16) = 4 |
| Sum(number1, number2...) | Number | Sum(1,2,3) = 6 |
Date and Times formulas
| Formula | Output | Example |
|---|---|---|
| Date(year,month,date) | Date | Date(2022,3,15) = Date value for 15/03/2022 |
| Datevalue(text) | Date | Datevalue("15/03/2022) = Date value for 15/03/2022 |
| Now() | Date | Now(2022,3,15) = Current date and time |
| Days(date1,date2) | Number | Days("13/03/2022","15/03/2022") = 2 |
System formulas
| Formula | Output | Example |
|---|---|---|
| created_by | Text | created_by = "John Smith" |
| created_at | Text | created_at = "16/03/2022 10:45:15" |
| modified_by | Text | modified_by = "Jane Smith" |
| modified_at | Text | modified_at = "17/03/2022 11:21:03" |
| id | Text | id = "b6245ac26fa14f7d93bf6e131c402265" |
| row_id | Text | row_id = "1b8e1f8c1c28429b9a7674f526a892ba" |
The id and row_id formulas can be used to uniquely identify a specific transaction or a specific row within a transaction table. These can be useful in a report to show a unique 'fingerprint' of the transaction, or in API calls as discussed later in this document.
These formulas are updated when a transaction is submitted or saved, so a newly started transaction will have a blank 'created_by' value until that user either submits it or saves it, at which point the value will be updated. Also, modified includes changing the step a transaction is on, so someone approving a transaction will have 'modified' it even if they didn't change any of the actual values.
Logical Comparisons
Logical comparisons are usually used in conjunction with other formulas to test if a condition is true or false and return different values as necessary. The output type depends on what you are returning, so if you are returning text then a text field should be used, if a number then a number field etc.
The following operators can be used when making comparisons:
- = Equal to
- <> Not equal to
- > Greater than
- < Less than
- >= Greater than or equal to
- <= Less than or equal to
- IsErr(condition) Checks if the condition produces an error (e.g. dividing by zero). Can be used in conjunction with 'If' statement below to check for an error and display a message if an error is detected.
| Formula | Output | Example |
|---|---|---|
| If(condition,value if true, value if false) | - | If(net+tax=gross,"Correct","Error") |
| And(condition1, condition2 etc...) | - | If(And(net+tax=gross,tax<>0),"Correct","Error") |
| Or(condition1, condition2 etc...) | - | If(Or(lunch="Banana",lunch="Apple"),"Fruit","Non Fruit") |
| Not(condition) | - | Not(1=1) = false |
Logic can be nested so more complex conditions can be evaluated, e.g. if an invoice requires the managers approval if over 500 or if over 250 and the department is IT, then something like the following would work:
If(Or(amount>500,And(amount>250,department="IT")),"Requires Approval","OK to Pay")
Aggregation formulas
| Formula | Output | Example |
|---|---|---|
| Sum(tableID.fieldID) | Number | Sum(table_1.total_amount) = Sum of the 'total_amount' column of table_1 |
| DSum(tableID.fieldID,condition) | Number | DSum(table_1.total_amount,product="A") = Sum of the 'total_amount' column of table_1 of rows where 'product' field = "A" |
| Sumapp(appID.tableID.fieldID) | Number | Sumapp(app_id.table_1.total_amount) = Sum of the 'total_amount' column of table_1 of the app with ID 'app_id'. |
| Dsumapp(appID.tableID.fieldID,condition) | Number | Dsumapp(app_id.table_1.total_amount,quantity>50) = Sum of the 'total_amount' column of table_1 of the app with ID 'app_id' where the quantity column is greater than 50. |
By default all of the above formulas will produce a sum of the total, however they can all have an optional last parameter which changes the aggregation method. Available options are "count", "average", "concatenate", "max", "min". E.g. Dsumapp(app_id.table_1.total_amount,quantity>50,"average")
The app ID is required for using the Sumapp formula. This can be determined by selecting the app from the menu on the left hand side of the main screen - the app ID is the string of characters between the last forward slash and the question mark as shown below.
Select the relevant part of the url and then copy and paste it in to your formula. E.g.
Sumapp(81f058f4ba794662a1f775a7e19e476f.details_1.gross_amount,"max")
To get the tableID, from within the form editor select the table properties (cog icon) for the table you want to Sum - the table ID is displayed in brackets after the table name.
Parsing / Lookup formulas
| Formula | Output | Example |
|---|---|---|
| JSON.parse(fieldID).JSON_item | Number | JSON.parse(supplier).Email=email value of item selected in 'supplier' field. |
The JSON.parse formulas would typically be used to extract data selected as part of an external or reference dropdown field. If the data returned is in the JSON format, it would typically be a list of item names and values, for example:
"supplier":"New Products Ltd.",
"code":"81001",
"email":"info@newproducts.com",
etc...
So in the above example, if the field ID of the external dropdown was 'suppliers', then for the example selected:
JSON.parse(suppliers).email = "info@newproducts.com".
Reference dropdown fields always store their data in JSON format, external dropdowns will depend on the source system.