When automating a business process, whether it’s building a data pipeline or creating a bots-based workflow, more often than not it requires applying formulas to transform data.
However, formulas, while incredibly useful, do need special handling when operating on null values. Just like any other function, they expect valid input values in order to produce a desired output.
You may watch the video above to find out how to handle nulls in formulas. Alternatively, read on below.
What are formulas in Workato:
Workato gives you a rich library of over 400 unique formulas to help you cleanse, validate, enrich, and transform the data in your recipes.
Formulas in Workato are pre-built functions that let you:
- convert data types
- remove unnecessary characters
- check for correctness of data
- change cases
- split strings
- format dates, and much more.
You can either use formulas independently or in combinations by chaining them together with dots, as shown here:
Chaining formulas with dots
Working with null values
As mentioned above, formulas do need special handling when operating on null values.
Formulas are expected to report errors when input data pills contain the occasional null value. Null values in data pills can be an outcome of an incorrect data-mapping step, or just the source data field missing some information.
These formula errors have the potential to cause the recipe to stop entirely, making it even more important to handle nulls immediately.
How to avoid formula errors when working with nulls
Here are 3 simple and effective ways you can handle nulls and avoid formula errors to keep your automations running smoothly.
1. Using Prefixes:
Using ‘&’ operator when input value is not null
You can add the “&.” operator before each formula to indicate that the formula should be applied only when the input value is not null. In the event you are chaining multiple formula functions, the “&” operator must be used for each formula in the chain.
2. Using Alternate Values
It is common for applications to store the same or similar information in multiple fields. For example, the contact email of a lead may be stored in a “work email” field or a “personal email” field in your CRM app; the address information can be in the shipping or billing address fields; contact’s work phone or mobile phone.
Let’s look at a case where we need to capture a lead’s email. The source application may always contain a value in one of the two fields or in both fields. Because we care to get a valid email address for the lead, we can construct a formula to choose the work email when present, or alternatively choose the personal email when work email has no value i.e. NULL.
It is important to know that the order of the fields specified in the formula determines which value will be selected first i.e. in this case work email will be prioritized over personal email.
3. Using Conditional Statements:
conditional statements
In some situations there could be a requirement to be very precise in selecting the right value for the formula. By using conditional logic in formulas, which looks like an if-else statement, you can express advanced conditions for selecting the right value.
In the above image, the condition at the beginning is just a boolean expression that evaluates to True or False, and the outcome decides which expression is returned.
- If the condition is true, the formula returns expression 1
- if the condition is false, it returns expression 2.
Using the example of capturing a lead’s email, we could create a formula that:
- uses the work email when the lead type is an organization
- uses a personal email when it is just an individual.
That formula would look like this:
Conditional formula to capture lead’s email
Conclusion
Null values are an inescapable reality when it comes to working with data. Being able to handle nulls the right way is extremely important for both the health of automations and data quality requirements of business processes.
Read the full documentation on Formulas to learn more.