Lookup Tables

What Are Lookup Tables?

Lookup Tables in CommCare are a powerful feature that allows you to manage and reference large, dynamic datasets outside of your main application's forms. They are ideal for information that:

• Changes over time (e.g., a list of project villages ).

• Varies by user (e.g., a list of sites a specific worker is responsible for ).

• Is too large to manage within the form builder itself.

Common Uses of Lookup Tables:

Lookup tables add advanced functionality to your forms, such as:

• Filtered Choices (Cascading Menus): Show choices in a question that are dependent on the answer to a previous question.

  • Example: Selecting a State in one question will only show the Districts belonging to that state in the next.

• User-Specific Choices: Display a list of options that is unique to the mobile worker currently logged in.

  • Example: Only show the referral sites that a particular user is assigned to.

• Randomized Content: Define a bank of questions or messages and randomly select one to display when the form is opened.

• Referencing Related Data: Store groups of related information, like geographical locations (State, District, Block).

Need help setting up your first look-up table in Excel? Download this sample to get started.

Creating and Managing Lookup Tables

1. Define the Table Structure

A Lookup Table's structure must be defined, specifying the Table ID and fields (columns) that will store your information. This can be done directly in CommCare HQ or in using an Excel template.

• Table ID: A unique name for your table (e.g., villages or products). It cannot contain special characters or spaces.

• Fields: These are your column headers (e.g., Name, ID, Code).

How to Define a Table in CommCare HQ:

1. Log in to CommCare HQ.

2. Click the Data link on the Dashboard.

3. In the Lookup Tables section, click Manage Tables.

4. Click the Add Table button.

5. Specify the Table ID.

6. Add the necessary Fields by clicking the Add Field button.

7. Click Save.

Open

2. Downloading and Uploading Data

After defining the structure of lookup table in CommCare HQ, the data itself is typically managed using a Microsoft Excel file.

Downloading the Table for Editing:

1. Navigate to the Manage Tables window.

2. Check the Include in Download box next to the table(s) of interest.

3. Click the Download Lookup Tables button.

The Downloaded File: The resulting Excel file contains a sheet named Types (for advanced configuration) and a separate sheet for each table you selected. Each table sheet includes your defined fields, plus reserved columns like UID and Delete (Y/N).

Open

Uploading Data (Updating or Creating):

1. Navigate to the Manage Tables window.

2. In the Upload Lookup Tables section, click Choose File and select your Excel file.

3. Optional: Replace Existing Tables:

   • Check the box: This deletes the old table data and replaces it entirely with the new table from your Excel file.

   • Leave unchecked: Existing rows are updated, and new rows from the Excel file are added.

4. Click the Upload Tables button.

Open

Speedy Tip

Open

In many cases, your Lookup Table may scale up to a point where it starts to make your form inefficient, deteriorating the user experience. Understanding what factors impact performance is key to optimizing your application. Loading large lookup tables is like climbing a really tall tree—slow and tiring. Follow these recommendations for optimal mobile performance:

• Keep lookup tables under 1,000 rows whenever possible.

  • The single biggest factor deciding a form's performance is the Number of rows in a lookup table. The more rows a table has, the longer it takes for the mobile device to query and filter that table, especially when navigating to a question that uses the lookup table.

• Split large tables into regional or category-specific ones for faster access.

• For large lookup tables, consider indexing fields that are used in calculations.

• Use conditional logic to load lookup values only when needed—don’t pull the whole forest into one form!

Factors That Do NOT Significantly Affect Performance

The performance of form loading and form navigation is generally independent of the following factors:

• The size of data in each column of the table.

• The number of Lookup Table type questions that have been set up in the form.

Controlling Data Access: The is_global? Column

The primary goal of restricted access features is to ensure that mobile workers only download and see the data rows relevant to their specific role, location, or group, which significantly improves sync performance and manages data security.

The entire process for restricting row visibility is consolidated into two steps: setting the table scope and defining the row-level assignments.

1. Setting the Table Scope (The Types Sheet)

The is_global? column on the Types Sheet determines the default access setting for the entire lookup table.

Defining Row-Level Assignments (The Data Sheet)

When a table is set to restricted (is_global? = no), you use specific columns in your data sheet (e.g., the district sheet) to define the access criteria for each individual row.

The process is consistent across all types of assignment:

1. Add Assignment Columns: Add the required column headers to your data sheet (row 1) (example here).

2. Assign Access: For each row, enter the corresponding user name, group name, or site code to grant that specific access.

Assignment Column Types

The "is_global?" column in CommCare lookup tables also improves performance and efficiency by enabling better management of data synchronization. Here's how:

1. Minimizing Synced Data:

   • Rows with "is_global?" = yes sync universally to all users, ensuring global availability.

   • Rows with "is_global?" = no sync only to users or groups matching specified filters (e.g., user name, group name).

   • This targeted sync prevents unnecessary data downloads, reducing the workload on devices.

2. Reducing Table Size on Devices:

   • Non-global rows are synced only for specific users or groups, based on defined filters like user name or group name. This ensures that only relevant data is downloaded to each device, reducing the size of the lookup table stored locally and optimizing app performance.

   • For large datasets, only relevant rows are synced, saving memory and processing time.

   • Example: A dataset with 10,000 rows could have 9,000 user-specific rows ("is_global?" = no) and 1,000 global rows ("is_global?" = yes). So each user downloads only relevant rows.

3. Enhancing App Performance:

   • Smaller datasets result in quicker data queries during form filling, especially for dropdowns or cascading menus.

   • Reduced sync size leads to faster synchronization, especially useful in low-bandwidth environments.

Lookup Table Tips & Tricks