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
Critical	Highest
High	High
Medium	Medium
Low	Low
–	Lowest

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
Highest	Critical
High	High
Medium	Medium
Low	Low
Lowest	Low

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. 

This recipe:

• 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
• Transform large and complex data structures with Javascript
• 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.