CommCare Lookup Tables 101 - Apply for a free trial by March 4th!

Lookup Tables are a powerful feature to “look up” information directly from Excel spreadsheets, making it a lot easier and faster for you to input and update external information in your app. Lookup Tables are great for managing large amounts of information, such as Weight-for-Age Z Score tables for nutrition projects or extensive geographical locations like states, districts, clusters, or villages.

Read the guide below to learn more about how to use Lookup Tables. Please note that Lookup Tables are only available to users on a paid Standard software plan or higher. 

1. Determine if Lookup Tables are right for you

Are Lookup Tables helpful in your app?

• Do you have large cascading lists of choices, such as states/districts/villages?
• Do you have lists of choices that you want to be able to easily update over time without changing the app?
• Do you have lists of choices that are specific to the user? For example, do you have a question asking for village name and only want to show users options that are relevant to them?

If you answered “yes” to any of these, read on to learn how to get started with Lookup Tables.

2. Define Your Tables

Think about the questions in your app where you want to reference external information. We will use the following questions as an example:

If you want a question to look up external data, you create a table of data for that question. We call different types of data in the table fields.

In practice, a table is an Excel sheet and each field is a column in that sheet.

To set up your tables, clicking on the Data tab of CommCareHQ and select Manage Tables. Read more detail on how to set up the tables here.

3. Fill your tables with data

Select each table you want to update and download them in Excel. After you fill in the data, upload the Excel file into CommCareHQ. You can confirm that it worked as expected by clicking on View Tables under the Data tab on CommCareHQ.

4. Add Lookup Tables to your app

Now that you have defined and filled in your tables, it’s time to create the Lookups from your form! To add a Lookup Table question, click on the “multiple choice” drop-down:

The question will appear just like a regular multiple choice question, but with only one choice:

Click on “Lookup Table Data” and choose the table you want to use from the Lookup Table dropdown.

Just like any other multiple choice question, you also specify a “value” and a “label”. Instead of writing in anything you want to appear, these must reference fields from your Lookup Table.

Example: District Lookup Table Question The “district” question will show a list of choices from your Lookup Table. The mobile workers will see the “label” fields, and the submitted data will show the “id” field.

That’s it!

If you want to drill down into another location, like village, it’s easy to set up a filter on the “village” Lookup Table question based on the id of the chosen district. Read more on how to set up filters, multiple languages, and more on our Help Site.

5. Try it out!

Install or update your application to try Lookup Tables! After logging in make sure to push the Sync with Server button — Lookup Tables update on “Sync” rather than normal app updates, so they are much easier to maintain and update over time!

To mobile workers, the Lookup Table question will look just like a normal multiple choice question. They won’t know Lookup Tables are behind the scenes, but they will certainly appreciate lists that are much shorter and up-to-date!

For more information visit the Help Site.