It's common for many of our partners to store complex, constantly changing information in spreadsheets. On the CommunityConnect platform, lookup tables allow you to easily upload, update, and access the information within a spreadsheet.


Instead of having to update individual flow variables or contact fields every time your data changes, you can just upload a spreadsheet with the most up-to-date information to our platform as a lookup table. You can then create flows that access this information by “calling” the lookup table.


1. Prepare Your Spreadsheet

  • File Format: Your spreadsheet must be formatted as a CSV or XLS file.

    • If you're using an XLS file, set all data as text (no numeric or date/time formatting).

  • Setup:

    • Organize your data in columns.

      • The first column should be the most static, unchanging variable. The example below is a spreadsheet of information about food banks. Name is in the first column because it is unlikely to change, while its address and hours might. 

    • Add a header to each column. The header you choose is how you will refer to each variable in a flow.

      • Add the prefix 'numeric_' to columns that are formatted as numbers.

      • Add 'date_' to columns formatted as dates. The accepted date format is dd/mm/yyyy.


2. Create Your Lookup Collection

If you haven’t done so already, create the flow that you will use to access the information in your spreadsheet. Within that flow, click on “Settings” and then “Import Database.” This will direct you to your Lookup Collections. Enter a name for your new collection and click save. The name should reference the data in your spreadsheet, like "Food Bank Names and Locations."


3. Upload Your Data

Now that you have created your collection, you can import your spreadsheet data. To do so, click “Import Data” next to your collection. Select your file and hit “Import.” You will receive an email once the spreadsheet has successfully uploaded.


4. Call the Lookup

Within your flow, create a “Call Lookup” ActionSet. In the “Queries” box, select the appropriate lookup table, and the “Lookup Parameters” boxes will appear.


Lookup Parameters

  • Field is the name of your first column in which the data isn't changing. Your Value is the expression you'll use to share field information in your Flow. 

  • Rule determines what the query will look for when searching the spreadsheet.

    • “equals” will look for a direct match between the value and one of the cells in the first column,

    • “contains” will start at the top of the first column and look for the first cell that includes the value,

    • “matches regex” allows you to input a regular expression to determine how the search will occur.

      • This rule is case sensitive. If you need it to be case insensitive, add "(?i)" in the Value field, e.g. "(?i)@results.food_bank"

  • Value is the term used to search the spreadsheet.


We recommend leaving the Result Name simply as “Result.” This will save “200” as @results.result if the lookup is successful and “404” if it is not, which you can use for debugging as necessary.


Now that you’ve created a “Call Lookup” action, you can access all the data related to that value. 


Sample Flow Interaction

In the example below, the user receives a message and then responds “Food Bank 1,” which is saved as the variable “Food Bank” as part of the “Wait for Response” action. 


The “Call Lookup” action searches for an exact match of “Food Bank 1” on the spreadsheet and finds it successfully. “200” is saved as the “Result.”


The user then receives a message with the address, which can be retrieved using the expression @webhook.result.address. Similarly, the expression @webhook.result.hours would return “8am - 6pm”.


Up Next: Create and Use a Gift Card Incentive