Lookup tables (AKA cross reference tables) enable users to quickly and easily lookup frequently used data that is static and immutable.
Data in lookup tables is typically organized like a database table, with columns and rows of data. You can lookup an entry within the specified lookup table by using:
- Any column
- Multiple columns
For example, if I'm trying to move accounting entries from my sales app into my accounting app i.e. Salesforce to Intacct, I might find that both departments are using different account codes internally. In order to ensure data is synced accurately from Salesforce into Intacct, I would need to take the account codes from Salesforce, find the matching codes in Intacct, and create the Intacct accounting entries using the corresponding Intacct account codes. In the following table, any Salesforce entries coming in with the code 34267 will be written into Intacct under the code 6754.
Lookup table with the account ID in Salesforce, account ID in Intacct, and the account names
Some other scenarios where you might need lookup tables:
- Given a city name, you want to get the zip code
- Given a metric unit, you want to convert to SI for distances
- Given a department and rank, you want to retrieve vacation accrual rates
Lookup tables are enabled only for certain plans. Check the Pricing and Plans page or reach out to Workato sales representatives at +1 (844) 469-6752 to find out more.
Setting up your lookup tables
There are three ways to upload data into your lookup tables:
- Import an existing CSV file
- Add new entries manually
- Add new entries via a recipe
Note: Lookup tables are immutable and the existing entries cannot be modified or deleted.
Importing an existing CSV file
The GIF below walks you through the process of creating a lookup table and loading it with data imported from a CSV file.
Creating a new lookup table and importing a CSV file
Adding new entries manually
You can manually enter additional lookup table entries.
Click on the Add new entry button
A new blank row will be generated for values to be entered
Adding new entries via a recipe
There is a Lookup table connector that allows you to automate your work with lookup tables. This connector supports adding of new entries via a recipe.
Using the lookup table connector
The Lookup table utility connector allows you to work with your tables via recipes. This connector supports the following actions:
- Lookup entry
- Search entries
- Add entry
Add entry action
Add a new entry to an existing lookup table. You can use this action to keep your lookup tables updated, e.g. read newly created job titles from your human resources application and create a new entry for that job title in your lookup table.
Select the lookup table to write to and then input the values to provide for each cell of the row
Lookup entry action
The lookup entry action allows you to search for an entry (a row in the lookup table) by any of its values. The lookup entry action behaves like a search and retrieves a single entry. The first matching entry depends on the values passed in. If you have duplicate entries (i.e. your lookup will return more than one entry), only one entry will be retrieved.
In the example below, we have a table called Vacation Rules with 4 columns - vacation type, department ID, job code and job title.
Using the lookup entry action in the recipe
In the following gif, we configure the lookup entry table to search for a specific row that matches the values we have (in that job) for department ID and job code. In the subsequent step, we can see what's returned - a single entry with all 4 available values. If we were looking for the corresponding vacation type and job title, we can use those datapills from the datatree.
The following gif walks you through the flow of configuring the lookup entry action to retrieve a matching entry.
Configuring the action to lookup entries with the matching job title and job code
Search entries action
The search entries action works similarly to the lookup action. The main difference between them is that lookup action returns a single entry whereas the search entries action returns a list of entries.
Use this if you expect a list of matching entries to be returned, and wish to process the list accordingly.
Update entry action
Updates an entry to an existing lookup table. This action requires the Entry ID to be mapped. You can retrieve the Entry ID from actions such as "Search Entries", "Add entry" and "Lookup Entry" action.
Using the Update entry action to update Jean's account status to closed
Delete entry action
Deletes an entry in the specified lookup table by using the Entry ID.
For this example, the Entry ID is being retrieved from the Search action.
Using the delete entry action to delete Jeans data in the table Account
Instead of deleting all entries one by one in the lookup table, the truncate table action deletes all entries from the specified lookup table. Using this action cannot be undone.
In the example below, we are deleting all entries in the table Weekday
Using truncate table action to delete all 7 entries in the table Weekday
Lookup table formulas
Instead of using an action step to carry out the entry lookup, you can also use a formula to lookup entries in your lookup table. This is as efficient as the lookup action and can be done within the input field without requiring additional actions.
To use the formula, set the input field to formula mode, and enter the formula in this format:
column name for search:
column name for result]
Performing lookup on in memory table
Output of search entries can be used as in memory lookup table. In memory lookup function is useful when a recipe invokes the
lookup function on the same lookup table 1000s of times.
To use the lookup formula on a in memory table, set the input field to formula mode, and enter the formula in this format:
entries pill from search entries action,
column name for search:
column name for result]
Using the lookup table formula to lookup days of the week entry in table Weekday
Each lookup table supports up to 5 columns of data. Each column can be used as a key to lookup the entry.
Maximum of 5 data columns available
There is a maximum of 5000 entries per lookup table.