Lookup Tables

Lookup tables are a powerful and useful means of referencing frequently used custom data with no dependency on external apps/databases. When building recipes for process or data integration often referencing datasets (e.g. product catalog, vacation accrual rates for employees, State abbreviations & descriptions, holiday calendar, marketing/sales events) the following challenges may be encountered:

• The datasets in applications/systems that are not easy to integrate (e.g. CSV, PDF, Microsoft Excel, other proprietary databases/apps) 
• The operations to access data from another system are expensive (e.g. special coding required for integration, expensive computational or I/O operations)

 By using lookup tables in a recipe you can simplify the integration, ensure data integrity and eliminate the need for complex formulas/code.  A few examples of how lookup tables may be used:

• Data Lookup: requires retrieving a specific value based on a key. Examples:
  • retrieve city name (e.g. San Francisco) based on office location identifier (101)
  • retrieve time zone (e.g. GMT + 8) based on city name (e.g. San Francisco)
  • retrieve holiday name (e.g. President’s day) based on date
  • Check if a device identifier (e.g. phone number) is whitelisted for billing
• Data Validation: check if a specific value is valid by comparing with a list of values. Lookup can be used to return a Boolean value TRUE when value is valid and FALSE when value is not found in the list. Examples:
  • check if a vacation code/account type is correct
  • check if a country code is valid
  • check if a zip code is valid
• Data Normalization: Lookup tables can be used to normalize the same data referenced differently across multiple systems. For example:
  • Currency code is referenced as alphabetic ISO code “USD” in one Salesforce/Expensify and numeric ISO code “840” in Intacct/NetSuite or other.
  • Zip Code is referenced as “94404” (5-digit format) in Salesforce and “94404-0643” (9-digit format) in NetSuite

What are lookup tables?

In the simplest form lookup tables are two-dimensional data structures containing key-value pairs that allow faster access using a known key. Lookup tables are typically designed for:

• Configuration and descriptive data that are not related to individual applications (e.g. Holiday calendar, zip codes by city etc)
• Static  and immutable data that rarely changes or grows
• Small set of data relative to event/transaction datasets
• Examples: Time Zone abbreviations by city , Office location for a company, company holiday schedule, ISO currency codes  etc.

Setting up Lookup Tables

Access the Lookup Up tables section from the menu under your account.  

Lookup tables can be created using one of the two below methods:

• Importing data from a CSV (comma separated values) file

• Directly adding entries using the lookup table editor in the browser

Lookup Table Size & Usage:

• Table:

• Assign a unique name to each lookup table so that you can identify them easily when referencing in a recipe

• Columns (keys)

• Lookup tables must comprise a maximum of 5 columns
• New columns can be added to the table definition
• Existing columns can be removed or renamed from the table view using the lookup table designer

• Rows (values)

• Lookup tables can consist a maximum of 10000 rows
• New rows can be added to the lookup table data either manually or using a recipe
• Existing rows can be updated or removed using the lookup table designer

Using Lookup Tables in recipes

Lookup tables can be accessed from in a recipe in the following ways:

• Lookup Table Connector: You choose the lookup table from the list of applications the same way you would use another application. This will bring up the lookup table schema (key/value) for your reference. The connector supports the following actions:

• Lookup Entry: Find a value in the lookup table based on a match with the query criteria e.g. lookup a Country Name based on a country code.

• Search for multiple values: Find more than one value in the lookup table based on a search criteria e.g. find all marketing campaign codes in month of February

• Add New Entries: Automatically add new values/entries to the Lookup Table using the recipe e.g. update the currency code list in the look up when new currency code is detected in Expensify

• Using a formula: The lookup function is a convenient way to query the Lookup tables directly from formula. The formula can only be used for looking up a specific value

Let’s review a sample integration recipe that automates communication with the customer support team by sending email and Slack notifications. The recipe goals are as follow:

• When it is a company holiday, email the list of  issues to the holiday on-call support team
• When it is not a company holiday
  • post the list of issues on Slack Channel for customer support
  • email the list of issues to the regular customer support team

However, the company holiday schedule is not stored in any application or database and is instead distributed over email as a PDF attachment. Prior to the support for Lookup tables, complex formulas would have been required in order to check if the date on which the recipe is run was a company holiday.

Below is how you can use lookup tables to simplify the recipe design.

• Create a lookup table “CompanyHolidays”

• Build your recipe with a conditional action to check if the recipe run date is a company holiday

• Evaluate the condition for Company Holiday using the lookup function in the recipe

• Check if the recipe run date is a company holiday

• Add actions based on the outcome of the evaluation of the conditional statement
  • When the run date is a company holiday set an action to send email to the holiday distribution list
  • When the run date is a NOT a company holiday, set an action to send email to the regular customer support team and post message on Slack channel for customer support

Ready to jump in?

• Check out our quick start guide on using Lookup tables
• Browse sample recipes that use lookup tables