Create and use lookups in databases
In this guide, we'll walk you through how Lookups can be created and used in Runway to link information across data sources.
Lookups are a valuable way to link key information about your business so that it’s accessible throughout your model. They enable you to dynamically fill in related data using Dimensions, similar to a VLOOKUP() or INDEX(MATCH()) in spreadsheet tools.
More generally, Lookups can be used to fetch and reuse relationships between dimensions across Runway. Like VLOOKUP() in Excel, lookups in Runway will return the first matched value for lookups.
In this guide, we'll walk you through the process of setting up a lookup in Runway databases, with the example of associating marketing channel owners to customers.
Other examples of use cases for lookups:
- Associating your chart of accounts to rollup categories like (G&A, S&M, R&D)
- Associating account managers as owners of customers
- Associating hierarchal information, like cities you’re selling in to regions you care about (Americas, EMEA, APAC).
Enabling feature flags
Note: This step is only required for organizations that onboarded to Runway before March 2024
- Open the developer tools menu and access the debug menu.
- Ensure that the following option is enabled:
This will enable the usage of dimensional properties in databases, which can be used to define lookups.
You can confirm that you are in the correct state if your menu for adding database columns includes this option for adding a dimension:
Set up a new database
The next step is to create a database where you will define the relationship between the dimensions you want to associate (and eventually, use a Lookup for)
- Create up a new database — in this instance, "Channel Mapping."
- You can remove extraneous columns — the important outcome of this step is that you have a Dimension column for each of the for the dimensions you want to associate — in this instance, “Channel” and “Channel Owner”
Establish the lookup relationship
- Populate the database with the rows you want to define lookups for
- Often, you may already have this information in a spreadsheet or another resource. You can paste the information into this lookup database and it will create the rows in bulk. The important thing to note here is that if you want to utilize existing Runway dimensions, the text should match exactly.
Here’s an example of what the output of a lookup database could be. Note that each of these columns is of type Dimension.
Using the Lookup
We’re now ready to use our lookup in any database where the Channel dimension is present, to pull in values of Channel Owners
- Navigate to a database where the Channel dimension is utilized
- Create a lookup column from the column creation menu
- Specify how you’d like to perform the lookup — in this instance, we want to look up
- “Channel Owner”
- from the “Channel Owner Mapping” database
- using the “Channel” Dimension
- This will create the column and fill in values automatically ✨, and will stay up to date based on updates to the underlying “Channel Owner Mapping” database.
Managing and Updating Data
- To keep your lookups up to date, you may regularly want to ensure that all the records in the database you’re performing the lookup from are present.
- An easy way to keep tabs on this:
- Create a Page with view of your database holding business data (e.g. Customers in this case) with a filter on it for rows where the lookup column is empty. You can call the page something like “Lookup Check”.
- This will surface any records that are unable to successfully perform a lookup. If you find that there are new records in this view that you don’t expect, you probably need to add a new data record.
- For example, if a new Channel is added in your Customers database, you’ll want to add a row and Owner for it in the underlying “Channel Owner Mapping” database from where you’re performing the Lookup.
- You can confirm that a column is auto-filling values by hovering over a cell, which will indicate that a lookup is being performed.
Last updated on November 22, 2023