How to use lookup tables for data transformation in Workato
What is data transformation?
Data transformation plays an integral role in your integration and automation projects. It is the process of converting data from one format to a more valuable or desirable format. This allows data to be more usable across different applications and systems, preventing the occurrence of information or data silos.
Related: A practical guide to data transformation with Workato
Why do we need data transformation when building recipes?
It is common for multiple teams across an organization to work on business processes like order to cash, lead management, employee onboarding, or others.
Often each team has a different application for accessing the data referenced by these processes. And it is not uncommon for each application to have their own unique names and values for data like status, priority, country code, currency code, etc.
Case study: Escalating a customer support case from Salesforce to Jira
For example, customer support and engineering teams need to work together and communicate to resolve a problem reported by a customer.
A customer support agent can create a new case in Salesforce, and can set the Priority of the case to “Critical” to be escalated for the engineering team to triage.
It is easy to create a recipe to automatically create or update an issue in JIRA— the application used by the engineering team, for the escalated case.
But how do we set the priority of the JIRA issue to indicate the same urgency as that of the case in Salesforce i.e. “Critical”. There is no one-to-one mapping available between the values of priority for case in Salesforce and issue in JIRA.
Not only are some of the names different (“Critical” vs “Highest”) but there are a different number of possible priorities in Salesforce and JIRA.
What is needed here is a solution that can correctly map the priority values in Salesforce to those in JIRA without losing context and data.
Related: How to group map your data
What are the different ways to transform data in Workato?
There are 2 possible ways to transform data in Workato, namely:
- Using recipe logic to transform data
- Using a lookup table to transform data
Below, we will analyze the pros and cons of both approaches and advice you on which method to undertake for your recipe.
The quick method: recipe logic
One quick way to resolve terminology conflicts is to create the cross-referencing logic in your recipe itself, using variables and if statements:
This method is convenient if you only have one or two possible values to translate. But there are limitations to this approach.
Limitations of the recipe logic method for data transformation
- Logic can’t be reused between recipes
- Recipes become hard to read when working with more than a few values
- Updating and maintaining your translation logic requires reading and updating multiple steps of a recipe.
A scalable approach: create a cross-reference with lookup tables
The lookup table is a data structure that can be used to persist cross-reference data for lookup operations in a recipe. Lookup tables provide significant performance gains in storing and retrieving data relative to an external database.
Read more about lookup tables here: Faster access to custom data with lookup tables
Benefits of using lookup tables for data transformation
Using lookup tables as a cross-reference for data transformation has a number of important benefits:
- Improved readability : Recipes are kept simple and readable, with no multi-step logic for defining the mapping of values.
- No impact on recipe design: No modifications to recipe needed when the set of lookup values changes.
- Reusability and standardization: The same lookup table can be referenced by any recipe in the workspace
How to create a lookup table for data transformation in Workato
Let’s work through the example of mapping priority across Salesforce and JIRA to show how you can use lookup tables.
Step 1: decide on your mapping rules
The first thing we need to do is to set out our business rules for translating priority. You can do this with a simple table:
|Salesforce Priority||Jira Priority|
In effect, when translating Salesforce cases into Jira issues, the automation will never set the priority to “Lowest”. However, since it’s possible to manually set priority to “Lowest” in Jira, we need to plan how to translate that change back to Salesforce.
The simplest way is to translate both “Low” and “Lowest” in Jira to “Low” in Salesforce.
|Jira Priority||Salesforce Priority|
Step 2: Create lookup tables
Find Lookup Tables under the Tools menu.
Create two lookup tables reflecting your chosen business rules. One to translate priority from Salesforce to Jira, and one to translate priority from Jira to Salesforce.
You can manually edit columns and add entries, or upload a complete table from a CSV file.
Step 3: Use lookup formulas to transform data
A bidirectional sync of cases between Salesforce and Jira will require two recipes, but let’s look at just the first one.
- Is triggered by a new or updated case in Salesforce
- Searches for a matching issue in Jira
- Creates the issue if it doesn’t yet exist, or
- Updates the issue if it already exists
To see how we set priority, let’s drill into the Create issue in Jira step:
To apply the translation rules recorded in our lookup table we use a formula. The formula specifies:
- which lookup table to use
- which entry to look up
- which column to return a value from.
You can read the full documentation on the correct format of lookup formulas.
Related: How to choose the right formula for your Workato recipe
Step 4: Verify the results
Set the recipe to test mode, then create a new case in Salesforce and assign the priority to Critical.
You should be able to see the correct priority in the test report:
You should also be able to see the correct status in the Jira UI:
What else can you do with lookup tables?
In this example, we’ve looked at translating the priority of an issue between Salesforce and Jira, but you can use lookup tables anywhere you need to create a cross-reference. Some common examples include:
- Cross reference data values across apps
- Parameterize recipe steps
- Pass data or control from one recipe to another
- Control table for monitoring/alerting
Alternatively, you can browse sample recipes from our community library that uses lookup tables.
Learn more about data transformation
Transforming data is a crucial skill for any automator. Find out more about advanced transformation techniques on Product Hub:
- Use formulas to validate data
- Get actionable information from unstructured data with natural language processing tools.
And don’t forget to check the docs on lookup tables to learn more about what you can do with it.