Formula
Formula is a flexible field that gives you the tools you need to shape your data with very high flexibility.
Transcript
Creating a Formula field
- Under any list click on fields
- Click + Create New Field
- Type in a field name
- From “Field Type” Drop Down List select “Formula”
- Next you will select the output field type, note that each field type works best with different Functions
- Next you can start building your function
Building the Formula
Fields
- You can click on the f(x) icon to see the list of fields available under the list you selected in the trigger step.
- You can either scroll to select the field or start typing the field name to narrow down the results.
- E.g. if you want to reference the field named Start Date it will look something like this within the formula {{Start Date}}
Users
- You can click on the @ icon to see the list of users.
- You can either scroll to select the User or start typing the field name to narrow down the results.
- The result of referencing a user should look like this @Mark Kilnger.
Options
- If you’re working with Static List or Status Field you can click on the # icon to see the list of Options.
- You can either scroll to select the Option or start typing the field name to narrow down the results.
- The option or status when referenced should look like this #Qualified#.
Functions
- You can click on the f(x) icon to see the list of Formulas available under the list you selected in the trigger step.
- You can either scroll to select the Formula or start typing the Formula name to narrow down the results.
- E.g. if you want to reference the Formula named Now it will look something like this within the formula Now()
Functions
Text Functions
Here are a few things you need to know before you start using functions:
- Text is often referred to as a string, since it’s a string of characters (Letters)
- When you’re referring to a field within the formula you need to surround it with double curly brackets, and should look like this {{Field Name}}
- When you’re writing plain text into the formula you need to surround it with double quotes, and should look like this “This is a text”.
- You can reference any field type as a text if you select the output as text.
| Name | Function | How To Use | Examples | Result |
| Concatenate | Concatenates many strings into a longer one, and can be written in 2 different ways. | Concat([Text 1],[Text 2],...,[Text n]) | Concat({{First Name}} ,” ” ,”Last Name” ) | Jack Klinger |
| Length | Count the number of characters in a text. | Len([Text]) | Len(”Arnold” ) | 6 |
|
Len({{First Name}}
)
*Given that the first name is “Arnold” in this example |
6 | |||
| Left | Returns a specific number of characters from the beginning of a string. | Left([Text],[Number of characters]) | Left(”Arnold”,3 ) | Arn |
|
Left({{First Name}},3 )
*Given that the first name is “Arnold” in this example |
Arn | |||
| Right | Returns a specific number of characters from the end of a string. | Right([Text] , [Number of characters]) | Right(”Arnold”,3 ) | Old |
|
Right({{First Name}},3 )
*Given that the first name is “Arnold” in this example |
Old | |||
| Substring | It is the exact opposite of Left() , it Removes the first n characters and returns the rest of the text | Substring([text] , [n]) * n is the number of characters to be removed | Substring(“ABCDEFG”,2 ) | CDEFG |
| Index Of | Return the location of String in a bigger String, the first character counts as 0, the second as 1, and so one. | Indexof([Search In],[Search For]) | IndexOf(“ABCDEFG” , ”CDE” ) | 2 * 3rd character return as 2 since we start count from 0 |
| IndexOf(“ABCDEFG” , ”A” ) | 0 * 1st Character returns as 0 | |||
| IndexOf(“ABCDEFG” , ”L” ) | -1 * If the String is not found it returns -1 since it has no index | |||
| Replace | To search for a substring and substitute it with another string | Replace([Search In],[Search For],[Substitute With]) | IndexOf(“ABCDEFG” , ”CD” , ”LM” ) | ABLMEFG |
| Trim | Removes empty spaces from beginning and end of text. | Trim([Text]) | Trim(” Hello There! ” ) | “Hello There!” |
| Trimleft | Removes empty spaces from the beginning of text. | Trimleft([Text]) | Trimleft(” Hello There! ” ) | “Hello There! ” |
| Trimright | Removes empty spaces from the end of text. | Trimright([Text]) | Trimright(” Hello There! ” ) | “ Hello There!” |
| Coalesce | Returns the first non null value from a set of parameters | Coalesce([Param 1],[Param 2],.....,[Param n]) | Format(,,1 ,”Mike” , ,) | 1 |
Date & Time Functions
| Name | Function | How To Use | Examples | Result |
| Now | Returns the current Date and Time depending on the timezone of the workspace. | Now() | - | |
| Now UTC | Returns the current Date and Time in UTC, i.e GMT+0. | NowUTC() * This will only work correctly if the field type is text, if the field type is date it will act like now() | - | |
| Today | Returns the current Datedepending on the timezone of the workspace. | Today() | - | |
| Today UTC | Returns the current Date in UTC, i.e GMT+0. | TodayUTC() * This will only work correctly if the field type is text, if the field type is date it will act like Today() | - | |
| Month | Return the month from a given date | Month([Date]) | Month(“2012-12-31” ) | 12 |
| Month Name | Return the name of the month from the Month’s number | MonthName([Number]) | MonthName(12 ) | December |
| MonthName(Month(“2012-12-31”) ) | December | |||
| Short Month Name | Return the short name of the month from the Month’s number | ShortMonthName([Number]) | ShortMonthName(12 ) | Dec |
| ShortMonthName(Month(“2012-12-31”) ) | Dec | |||
| Year | Return the year from a given date as a number | Year([Date]) | Year(“2023-01-22” ) | 2023 |
| Format | To give a specific format to a given date | Format([Date],[Format]) | Format(“2023-01-22” ,”yyyy-MMM-dd” ) | 2023-Jan-22 |
| Format(“2023-01-22” , ”yyyy.dd.m” ) | 2023.22.1 | |||
| Create Date | To create a date from Year, Month and Day | CreateDate([Year],[Month],[Day]) | CreateDate(2023 ,8 ,23 ) | 2023-08-23 |
| Subtract Date | Returns the number of days between 2 dates | SubtractDate([Date 2],[Date 1]) * Date 2 should be greater than Date 1 otherwise the result will be negative | SubtractDate(“2023-08-23” , ”2023-08-18” ) | 5 |
| Subtract Time | Returns the number of Minutes between 2 dates | SubtractTime([Date 2],[Date 1]) * Date 2 should be greater than Date 1 otherwise the result will be negative | SubtractTime(“2023-08-23 12:00” , ”2023-08-23 10:00” ) | 120 |
| SubtractTime(“2023-08-23 12:00” , ”2023-08-23 10:00” ) / 60 | 2 * The result was divided by 60 to get the number of hours instead of minutes | |||
| Get Working Days | Returns the number of working(business) days between 2 dates |
GetWorkingDays([Start Date],[End Date],[First Date Of Week (optional)],[Weekend Lenght (optional)])
* End Date should be greater than Start Date otherwise the result will be negative * First Date of Week takes a number between 1 and 7, Default is 1 which is Monday, 7 is Sunday * Weekend Length, is the number of days in the weekend, it's 2 by default |
GetWorkingDays(“2023-09-25” , ”2023-09-27 ” ) | 3 |
Mathematical Functions
| Name | Description | How To Use | Examples | Result |
| Total | Return the total from a set of numbers | Sum([Number 1],[Number 2],....,[Number n]) | Sum(3 ,4 ,5 ) | 12 |
| Average | Return the average from a set of numbers | Avg([Number 1],[Number 2],....,[Number n]) | Avg(3 ,4 ,5 ) | 4 |
| Maximum | Return the Maximum value from a set of numbers | Max([Number 1],[Number 2],....,[Number n]) | Max(100 ,33 ,713 ) | 713 |
| Minimum | Return the Minimum value from a set of numbers | Min([Number 1],[Number 2],....,[Number n]) | Min(100 ,33 ,713 ) | 33 |
| Random | Returns a random number between 0 and 1 | Rand() | 0.4 | |
| Random Between | Returns a random number between 2 values | RandBetween([MIN],[MAX]) | RandBetween(10,20) | 17 |
| Round | Rounds a number to the closest whole, or closest decimal based on the number of digits specified. | Round([Number],[Digits after Decimal Point]) | Round(3.12847 ) | 3 |
| Round(3.12847 ,2 ) | 3.13 | |||
| Round(3.12311 ,2 ) | 3.12 | |||
| Round(3.5 ) | 4 | |||
| Round(3.49 ) | 3 | |||
Logical Functions
| Name | Description | How To Use | Examples | Result |
| If | Evaluates a logical argument, and returns one value if true and another one if false. | If([Argument],[Value If True],[Value If False]) | If(5 < 7 ,”Tim” ,”Mike” ) | Tim |
| If({{Guess}} = 6 ,”Correct” ,”Try Again” ) * Let’s say that guess is 3 in this example | Try Again | |||
| If({{Selling Price}} > {{Cost}} , ”Profit” ,”Loss” ) * Let’s say that Selling Price = 300 and cost = 250 | Profit | |||
| IsBlank | Return True in case the parameter has no value assigned to it or it’s an empty string, and false if there’s a value. | IsBlank([Param]) | IsBlank(”Hi” ) | False |
| IsBlank() * No Parameters were presented | True | |||
| IsBlank(” ” ) * Empty String | True | |||
| IsBlank({{Assignee}} ) * The field is empty in this case and no value is assigned to it. | True |
Operators
Mathematical Operators
| Operator | Symbol | Sample | Result | Example |
| Addition | + | 3 + 5 | 8 | {{Cost}} + {{Shipment Fee}} |
| Subtraction | - | 9 - 5 | 4 | {{Price}} - {{Cost}} |
| Multiplication | * | 3 * 7 | 21 | {{Quantity}} * {{Price}} |
| Division | / | 21 / 7 | 3 | {{Total Price}} / 2 |
| Power | ^ | 3 ^ 2 | 9 | {{Quantity}} ^ 2 |
| Modulo (Mod) | % | 9 % 4 | 1 | {{Distance}} % 1000 |
Relational Operators
| Operator | Symbol | Examples | Description |
| Equal To | = | {{Name}} = “John Doe” | This will compare whether the value of the field “Name” matches the text “John Doe”. |
| {{Age}} = 13 | This will evaluate whether the value of the field “Age” is equal to 13. | ||
| Not Equal To | <> | {{Assignee}} <> “Arnold Stognam” | This will compare whether the value of the field “Name” matches the text “John Doe”. |
| {{Status}} <> #Blocked# | This will evaluate whether the Option in the static field “Status” is not “Blocked”. | ||
| Greater Than | > | {{Due Date}} > Today() | This will evaluate whether the date in the field “Due Date” is greater than the date of today. |
| Less Than | < | {{Height}} < 170 | This will evaluate whether the value of the field “Height” is less than 170. |
| {{Leave Balance}} < {{Requested Leave Days}} | This will evaluate whether the value in the field “Leave Balance” is less than value in the field “Requested Leave Days” | ||
| Greater Than or Equal to | >= | {{Price}} >= 3000 | This will evaluate whether the value of the field “Price” is greater than or equal to 3000. |
| {{Weight}} >= 100 | This will evaluate whether the value of the field “Weight” is greater than or equal to 100. | ||
| Less Than or Equal to | <= | {{Quantity}} <= 5 | This will evaluate whether the value of the field “Quantity” is less than or equal to 5. |
| {{Score}} <= 50 | This will evaluate whether the value of the field “Score” is less than or equal to 50. |
Logical Operators
| Operator | Example | Description |
| And | {{Due Date}} < Today() And {{Status}} <> #Blocked# | This will evaluate whether the Due Date is in the past, and the status is not blocked and return true only if both are true, |
| Or | {{Ticket Size}} > 5000 Or {{Tier}} = #VIP# | This will evaluate whether the ticket size is greater than 5000 or “Tier” is “VIP” and return true if both or either are true. |
Field Type Specific Cases
| Case | Description | Example | Output |
| Getting files name as Text | Simply create a formula field and select text as the output field type, then get the file field in the formula builder. | Let's say I have a file with the name (January Invoices.xlsx) and I mention the field holding it in the formula builder {{Invoices}} | January Invoices.xlsx | Getting files count as Number | Simply create a formula field and select Number as the output field type, then get the file field in the formula builder. | Let's say I have a field called "Attachments" with 7 files in it and I mention the field holding it in the formula builder {{Attachments}} | 7 |