Creating and Updating Lookup Tables

Owned by Sravan Reddy
Last updated: Apr 24, 2024 by Gillian Javetski
5 min read

 

What is a lookup table?


Lookup tables are used in applications to provide data that is not directly stored in the application and may change over time. For example, you can use them for a list of villages that changes over the time of your project. They also allow you to assign certain data (ex. villages) to certain users They are particularly helpful for grouping related information like defining geographical locations. 📖Learn more about Referencing Locations in Applications.

Define a Table

A table consists of fields which defines the information to be stored. A project can have multiple tables defined and multiple fields can be defined for a given table. Note: Hierarchal data is best managed with a table for each variable (ex: state, district, block, and outlet).

To create a lookup table:

  1. Login 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 (unique name that describes the table's contents)
    Note: Special characters and spaces cannot be included in a Table ID.

  6. Include Fields (information to be stored in a table) by clicking the Add Field button

  7. Click Save when finished


Note: The Visible to all users? selection determines whether a table is public or assigned to individual users. This is especially helpful when users should only see those items that are related to them. Applications deployed in multiple languages require more than one Field for a given property (i.e., state name, state abbreviation) 📖Learn more Advanced Lookup Tables.

Viewing Table Data

After a table(s) is created, the view tables option can be used to see its data.

To view a table:

  1. Login to CommCare HQ

  2. Click the Data link on the Dashboard

  3. In the Lookup Tables section, click View Tables



Note: Tables can also be viewed in the Manage Tables window by clicking View Table next to the table ID of interest.

Downloading or Uploading Tables for Editing

Once a table is created, it can be downloaded for bulk editing. New tables can be created or existing tables updated by uploading Microsoft Excel files.

To download tables:

  1. Navigate to the Manage Tables window

  2. Identify the lookup table of interest

  3. From the Manage Tables section, click the Include in Download checkbox next to each table of interest
    Note: The all or none buttons can be used to select or deselect multiple tables.

  4. Click the Download Lookup Tables button. Download time will vary by connection speed.


Note: The downloaded Microsoft Excel file includes a sheet titled Types, a sheet for each table with collected data, user ID columns, and Delete (Y/N) columns. The Types sheet is necessary when defining advanced lookup tables. 📖Learn more Advanced Lookup Tables.

To upload tables:

  1. Navigate to the Manage Tables window

  2. From the Upload Lookup Tables section, click Choose File and select the desired Microsoft Excel file

  3. From the Upload Lookup Tables section, click the Replace Existing Tables checkbox
    Note: The Replace Existing Tables option will replace any existing tables with new data from the uploaded Microsoft Excel file. If not selected, existing rows will be updated with data from the Excel file, and any new rows from the file will be added to the lookup table.

  4. Click the Upload Tables button

Editing Table Data in MS Excel

When lookup tables are downloaded for editing in MS Excel, the file includes a sheet titled Types along with a sheet for each table with collected data to include a UID and Delete (Y/N) columns. The Types sheet is necessary when defining advanced lookup tables. 📖Learn more Advanced Lookup Tables.

Example: "District" table download


Types sheet

 

District (table) sheet


 

Note: If a table requires rows assigned to users (i.e., user 1, group 1), these columns should be added manually. 📖Learn more about Mobile Worker Groups.

Splitting Lookup Tables

Tables can become very large and degrade application performance when deployed to mobile devices. This issue can be avoided by splitting large tables which is especially helpful for location-based lookup tables that will need to reorganize.

To Split a Large Table:

  1. Complete steps 1-3 from "To download tables"

  2. Open the download file ("Sheet 1" in this example)

  3. Create a second sheet in MS Excel ("Sheet 2" in this example)

  4. Copy and paste all of the fields from the Types tab of "Sheet 1" into "Sheet 2"

  5. Rename the "Sheet 2" tab

  6. From the "Sheet 2" tab, change the table_id to a title of choice (ex: "hypertension_2")

  7. In "Sheet 1", copy the table headers (i.e., UID, Delete, etc.) in row 1 from the tab that contains the lookup table name ("hypertension" in this example)

  8. In "Sheet 2", create a new tab and assign a name of choice for the lookup table

  9. Copy the headers into row 1 of "hypertension_2" (or the name you chose in step 6)

  10. Transfer the cells from "Sheet 1" that should be included "Sheet 2"

  11. Complete steps 1-2 of "To upload data"

Note: After all steps above have been completed, the lookup table references should be updated or a second question can be added to a Form to reference the new lookup table instead.
The result is a second lookup table that contains file that was split. Below is an example.