Once you have toggled your input field into formula mode, you can make use of the many available formula functions. Click here to view the comprehensive list of formula functions and how to use them.
However apart from those functions, it is equally important to understand the special characters used within formula mode, and when to use them. Let us take a look at these special characters:
||whatever comes after this will be used when a query gives a True response
||whatever comes after this will be used when a query gives a False response
||the brackets / parenthesis helps to group several commands together
||anything within these quotations will be considered as a string/plain text
||an alternative representation of or|
||escape character, used to denote that the next character has special meaning
e.g. \n represents newline
So you've looked through this table, but you still don't understand how to use these formulas? Fret not. Let's take a look at several examples of how we can use these operators.
Using ? and : to perform an if/else conditional statement
The ? and : symbols are used together to form conditional statements within any workato field that has formula mode turned on, and is based off Ruby's Ternary Operators. This is an alternative method for Workato users to account for various conditions within particular fields, instead of having to use the conditional action.
When the input value or pill before the ? equates to true, "result 1" which is directly after the ? will be returned.
However, when the input value or pill before the ? equates to false, "result 2" which is directly after the : will be returned.
An Example with Salesforce and Quickbooks
In this case, we are moving a quickbooks customer to a Salesforce account. We have a field in Salesforce accounts called "Accounts Description"
Here's what we want to do
- Map the "Customer ID" to the accounts description field in the format "QuickBooks Customer ID: <ID>"
- Add Notes from the QBO Customer to the Account Description on a new line in the format "Notes: "<Note>""
- However if there are no notes related to the customer, it should show "Notes: NIL"
We will achieve the conditional statement in the formula mode field as shown below.
Let's break this down in parts:
The first line: "QuickBooks Customer ID: " will show up as text, followed by the customer ID from QuickBooks
The second line starts with "/n", which creates a new line in the field. This is followed by the text "Notes:".
What comes next is the use of the .blank? function. It checks the (Notes) pill for any values. If it does not contain any value it is identified to be blank and thus returns a true value. If it does contain a value, it returns false instead.
Finally, this is where the ? and : symbols are used. Again, the explanation for ? is "whatever comes after this will be used when a query gives a True response" and subsequently for : is "whatever comes after this will be used when a query gives a False response".
This means that if the (Notes) pill has no value, the .blank? function will return true, and thus the text "NIL" will be returned.
If the (Notes) pill does indeed have values, then the .blank? function will return false. and the values within the (Notes) pill will be returned instead.
Using parenthesis ( ) to group commands together
The ( ) characters are most commonly used in math functions when we need to prioritize an addition/subtraction operator. For example, using the brackets will yield different results whereby (6 + 10) / 2 would return 8 while 6 + 10 / 2 would return 11.
The usage of ( ) is also important when we want to use multiple functions in an appropriate order. The function below is used to get the first day of the next month from the current date. Using the brackets ensures that the addition of today's date with 1 month is performed first before getting the first day of the month. If the brackets were to be removed in this case, this step would return an error because .beginning_of_month expects to be used on a date, but however interacts with 1.months first.
Using " " and ||
Many fields have a data type requirement, and in this example below the account name field expects a string as indicated by the ABC icon on the left of the field. When formula mode is not turned on, simply typing your text as Default will work because Workato will help automatically convert it into a string format. However on turning on formula mode, there is need for greater precision and will require surrounding the text with the quotations to ensure that "Default" is identified as a string.
|| is identical to using the special keyword or. In the example below, the name pill will be used if it contains a value, else the "Default" string will be used instead.
Using Escape characters
In the field's text mode, text that is written on multiple lines will retain their multi-line format.
When using the formula mode field, strings need to be appended together with the + symbol, while \n is used to retain the multi-line format.