How to cross reference data using lookup tables

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.

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.

A 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:

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. 

Let’s work through the example of mapping priority across Salesforce and JIRA to show how you can use lookup tables. 

First, 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

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.

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, and which column to return a value from. You can read more about the correct format for this formula in the docs.

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:

Benefits

Using lookup tables as a cross-reference 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

Do more 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

Learn more

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 to learn more about what you can do with lookup tables.