8 ways to use Workato’s lookup tables

By Leela Pasupuleti

When working on a Workato recipe you might be wondering how to achieve data persistence without using an external data store or how to externalize the business logic so that it can be easily maintained.

Enter Workato lookup tables!

In this blog, I’ll go over some of the ways to use lookup tables to achieve the above and beyond.  But before that, let’s talk about what a lookup table is.

What is a lookup table?

Lookup tables are a data structure within Workato that help you easily store and look up frequently used data within a recipe

Organized like a cross-reference/database table, a lookup table typically consists of rows and columns of data. You can look up an entry in a specified lookup table by matching it against data in one or more columns.  It supports up to a maximum of 10 columns and 100,000 entries which lets you implement it for a wide variety of use cases, which we will get to shortly.

How do I set up lookup tables?

1. Manually by entering values
2. Uploading a csv file
3. Building it programmatically (both by single or by batch values)

Here’s a more detailed guide from Workato on how to set up lookup tables.

How do I access lookup tables?

1. Using a Workato action
   • Lookup entry
     • Returns the first entry that matches the search criteria
   • Search entries  
     • Returns the list of entries that match the search criteria
   • Get all entries 
     • Returns all the entries in the table
       
2. Using a formula
   • lookup(‘Department Codes’, ‘Department code’: ‘ACC’)[‘Department’]
      Department Codes – Table Name
     Department Code – Reference Column Name
     ACC – Search Criteria (datapill or the value)
     Department – Column Name that needs to be looked up

Here’s a more detailed guide from Workato on lookup table actions.

What can I do with lookup tables?

Now that we’ve covered what lookup tables are, let’s explore some of the ways you can use them:

1. Data transformation

Data transformation is the process of changing the format or the value of data and is a component of most integrations. Depending on the use case, data is either needed to transform from a set of standard values or to cross-reference data such as IDs across different applications.

The example use-cases where a code is used to derive its value type:

• ISO Country Codes (US/USA -> United States of America)
• Currency Codes (CAD -> Canadian Dollar)

Below is an example of cross-reference IDs. PayCodes in Sage has an ID that corresponds to a different ID in Workday. We can use a lookup table to cross-reference these IDs between these two systems.

2. Parameterized steps and actions

We can parameterize recipe steps by removing hard-coded values and replacing them with values extracted from lookup tables. This will provide us the flexibility to update these values without stopping, updating, and re-deploying your recipe.

Think of a scenario where your client needs to download batches of records from another system via Workato. Let’s say we need to implement SLA tiers to decide on how many records they can request in one single call. The same condition can be captured in the lookup table and implemented in Workato as shown below:

3. Control logic based on existence criteria

You can use the lookup table to control the flow and business logic, thus avoiding complex conditions, multiple if-else statements, or hard-coding within the recipe, which makes it easier to maintain and allows any future changes to the logic without updating the recipe. Here’s a use case to demonstrate the same:

Payable charges are captured in Sage T&E and periodically sent in batches to Workday with the relevant Web Service messages. Workato recipe will segregate and summarize the payable charges into List using the below business logic and later send the XML message to Workday for each List. We will use the Lookup table to cross-reference the IDs and also to control the logic of which XML message needs to be sent to Workday. 

• If the PayCode’s category is Expense, we need to send Submit Expense Report request (XML message) to Workday
• If the PayCode category is Payroll and the attribute type is Hours, we need to send Submit Payroll Input message 
• If the PayCode category is Payroll and the attribute type is not hours we need to send Submit Payroll Input with Related Calc message 

I’ve created a lookup table for this use case as shown below. The recipe will take the batch payroll records and loop through all the line items to segregate the records into three different lists by looking up the Paycodes, PayCategory, and Attribute Type.

4. Lookup table chaining

There are times when we need to store more than 100,000 values for lookup purposes but due to the limitation Workato has on the lookup table it’s not possible to do so, at least not directly. But it is possible to extend the limit of lookup tables (100,000) by chaining them. 

For instance, to increase the limit to 200,000 create three lookup tables as shown below:

a. MasterLookup

b. EmpLookup1

c. EmpLookup2

Adding to the above tables, create a recipe function (optional) as below:

1. Search for entries in MasterLookup for LookupType to get all the fields
2. Retrieve the lookup name where the input param Code lies between FromCode and ToCode
3. Query the lookup table obtained in step 2 to get the value(s) for the input code and return the results

Please note that this implementation will have some performance issues due to the function call (optional) and requires two lookup calls.

The chaining lookup process can be more efficient if we can build the lookup tables by using a simple hash function(based on code) such as mod to decide which lookup table to use and we can use the same logic to rapidly identify the lookup where it is stored to speed up the process. There are some considerations when using hashing functions for building chaining but I’ll save that for my next blog.

5. Data persistence

There are situations where you need to persist data in your recipe and a lookup table can be used to do so. 

For instance, recipes that run on a schedule to process bulk data might require storing the watermark (the last run time or record) so that it can resume processing the next batch of records when it’s called next time. We can store the watermark in the lookup and pick up the next batch of records for processing.

6. Caching

Caching is a technique to temporarily store the frequently accessed data for faster performance. Workato provides caching for API end-points, however, if there is a need to cache results (albeit simple results) from a recipe function, you can achieve this by using lookup. If your recipe results are fairly static and do not change that often, then it’s ripe for caching.

Note: You will need to implement the cache timeout or retention logic within your recipe based on how frequently the underlying data is updated, otherwise it might return stale data.

7. Async call output

If you need to capture the results from an asynchronously called recipe, you can use a lookup table for the same. You can find more details in this Workato documentation.

8. Inter-recipe data exchange

In most cases, it makes sense to pass data from one recipe to another directly using the input parameter, but in cases where the data is not available during the recipe call but will be available later then you can pass it indirectly using a lookup table. 

If it’s possible, change your design so that you can call the recipe function and pass the parameters directly to ensure that it’s clear and avoid any side effects.

Conclusion

In conclusion, Workato lookup tables are a versatile and powerful functionality that is available for the developers to leverage it beyond its simple lookup use-cases. In this blog, I provided you with various ways of using lookup tables to power up your Workato recipes. I hope you enjoyed reading; please feel free to email me with any feedback or to let me know if you have used lookup tables in any other creative ways that I haven’t covered here.

About the author

Leela is a Senior Workato Developer with Bits In Glass. She is Automation Pro III certified and has experience integrating enterprise systems like Salesforce, Workday, NetSuite, Bullhorn, and Zuora.