Lookup Tables

To manage larger data sets and questions to pull from, CommCareHQ allows you to define Lookup Tables that can be used in your forms for information that changes over the lifetime of your application, or is different for each user.  

https://www.youtube.com/watch?v=BPyUZ7OoSEc

Overview

CommCareHQ allows you to define Lookup Tables that can be used in your forms for information that changes over the lifetime of your application, or is different for each user.   Here are some examples of potential functionality that can be added with lookup tables:

  1. Filtered Choices: Filtering the choices of a Single or Multiple Answer question based on the answer to the previous question.  For example, if the user chooses State in one question, the next question will only show the Districts in that state. This is useful for drilling down into different locations.

  2. User Specific Choices: Providing a set of choices for a question that are specific to the logged in user.  For example, in a referral form, you may only want to show the list of referral sites that a particular user is responsible for. 

  3. Randomized Questions/Messages:  Choosing a random question or message for a user from larger set.  For example, you can define a bank of knowledge questions in a lookup table and randomly choose to ask or show one of those each time a form is opened. 

Note 1: This functionality depends on having a user logged in and hence won't work in demo mode. 

Note 2: To update lookup tables on the phone over time, you need to turn on "Two Way Sync". 

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

Creating and Updating Lookup Tables

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 here: Advanced Organization Level Configuration | Referencing the Location/Organization Hierarchy 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).

1. Login to CommCare HQ.

2. Click the Data link on the Dashboard.

lt1.png

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

3d6c1783-2012-4a52-94c8-382bfefdeb45.png

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 in this section.

Viewing Table Data

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

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 Table

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.

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 in this section.

Upload Table

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 in this section.

Example: "District" table download

Types sheet

District (table) sheet

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.

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."

  1. 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.

Significance and role of “is_global?” column while using lookup tables

The "is_global?" column is a special feature in CommCare lookup tables that determines the scope of availability for specific rows in the table. It plays a critical role in controlling data visibility and accessibility, particularly in scenarios where certain data rows are shared globally across all users while others are restricted to specific groups or users.

Key Characteristics of "is_global?"

  1. Purpose:

    • Defines whether a specific row in the lookup table is available to all users or restricted based on user-level filters like user_id or group_id.

  2. Value Options:

    • yes: The row is marked as global, meaning it will sync and be accessible to all mobile users.

    • no (or omitted): The row will only sync for users who meet additional criteria specified in other columns (e.g., user_id, group_id).

    •  

  3. Column Placement:

    • When we create a lookup table, Click on Add Table- Edit Table dialogue box opens. Visibility Checkbox-Visible to all users. If we check this box the "is_global?" field will be set to “Yes”. 

    • If we do not check the box, the field will be set to “no”, by default.

    • If we create/update a table using excel upload and the field is missing, it will be automatically set to no during upload.

    • The "is_global?" column is optional but highly recommended for use in large or complex projects where global and user-specific data need to coexist in the same lookup table.

How It Works

  • Global Rows ("is_global?" = yes):

    • These rows sync to all users regardless of other filters (e.g., location, role, or user ID).

    • Ideal for universally relevant data, such as country lists, fixed status codes, or common reference values.

  • Filtered Rows ("is_global?" = no):

    • These rows sync only to users who satisfy other criteria.

    • Useful for context-specific data such as location-specific inventory or user-assigned tasks.

Use Cases

  1. Mixed Data Visibility: In a CommCare Project tracking agricultural inputs, a lookup table might focus on crops and include data like:

    • High-yield crops (e.g., wheat, corn) visible only to farmers with land above a certain acreage.

    • Low-yield crops (e.g., berries, herbs) visible to all farmers regardless of acreage.

The table could have the following fields:

  • Crop Name: The name of the crop.

  • Yield Type: "High-Yield" or "Low-Yield."

  • Acreage Filter: Minimum acreage required to view (null for crops accessible to all).

  • Is_Global?: A flag indicating whether the crop is visible to all users.

This structure keeps the data consistent (all rows represent crops) while enabling filtering based on user attributes (e.g., acreage).

  1. Performance Optimization:

    • By marking frequently used data as global, you reduce the overhead of applying filters for every user.

    • Filtered rows limit the size of data synced to individual devices, ensuring better app performance.

  2. Role-Based Data Access:

    • In a health application, global rows could include universally applicable disease codes, while restricted rows could contain patient-specific treatment plans.

Configuration Example:

 

ID

Name

Yield_Type

Min_Acreage

is_global?

1

Wheat

High-Yield

10

no

2

Corn

High-Yield

5

no

3

Berries

Low-Yield

-

yes

4

Herbs

Low-Yield

-

yes

 

  • Rows 3 and 4 (Low-Yield crops): Visible to all farmers because they are marked as is_global? = yes.

  • Rows 1 and 2 (High-Yield crops): Only visible to farmers who meet the Min_Acreage requirement and are not marked as global.

Implementation Steps

  1. Add the "is_global?" Column:

    • In your lookup table Excel sheet, add a column named "is_global?".

    • Populate it with yes or no values based on the row's intended scope.

  2. Use a Single-Select Question linked to the lookup table.

    • Apply this filter in the lookup table reference:

      is_global? = 'yes' or user_id = instance('commcaresession')/session/user/data/user_id
  3. Test Sync Behavior:

    • Create new version: Save changes and create a new version.

    • Sync and Test on Mobile: Log in as a user and verify that:

      1. Rows with is_global? = yes appear for all users.

      2. Rows with is_global? = no appear only for the relevant users.

    • If filtering doesn't work as expected, revisit the XPath expressions and verify the lookup table contains the correct is_global? values.

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_id, group_id).

    • 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_id or group_id. 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

Excel Tricks for Lookup Tables

Below is a list of tips and tricks for managing your Lookup Tables in Excel.

Managing large lookup tables efficiently

Sometimes when creating Lookup Tables, you can receive large source files with values in different fonts, all caps etc. This page describes a few tricks on how to make values easily all lower case, how to replace spaces by underscores etc. Below are a set of Excel formulas, with a description of what it does and how to use it.

Note: this tutorial assumes that you are using Excel in English. The names of the formulae are different in French.

How to turn all your text entries to first-letter capital for each word (ex: First_name Last_name)

=proper

Use: Converts words written in any format to words where the first letter is upper case and the rest is lower case. Example: CAPE TOWN > Cape Town

How: Add a column next to the column where all the values are, then in the first cell of the new column add =proper(cell number) and click enter (for 'cell number' click on the cell with the all caps value). Drag the corner right little box from the cell down so that all values from the all caps column will be converted.

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

Note: For words that are already properly formatted (ex: Johannesburg in the example above), no change is applied. Note as well that "cape town" turns to Cape Town.

How to quickly format all your text entries to all caps:

=upper

Use: Converts words written in any format to all caps. Example: Cape Town > CAPE TOWN

How: Add a column next to the original words/text. Use the formula =UPPER(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

How to quickly format all your text entries to lower case:

=lower

Use: Converts words written in any format to all lower case. Example: Cindy Jones > cindy jones

How: Add a column next to the original words/text. Use the formula =LOWER(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

How to quickly delete unwanted spaces:

=trim

Use: For when you receive a list of names/places where there is a space before the first word or after the final word. Example " cindy jones " (notice the space before 'cindy' and after 'jones'. The TRIM function will yield "cindy jones"

How: Add a column next to the original words/text. Use the formula =TRIM(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

Note: TRIM only deletes spaces before or at the end a full string of text. It will not eliminate common/accurate spaces such as the space between a first and last name.

 

Combining the above formulas: (ex: to eliminate unnecessary spaces AND to format upper/lower case)

=UPPER(TRIM(cell number)) or =LOWER(TRIM(cell number)) or =PROPER(TRIM(cell number))

 

Use: Do the steps above more quickly!

How: Add a column next to the original words/text. Use the formula =UPPER(TRIM(cell number)) in the new column and click enter. Drag down the formula to replicate this for all your text. 

*the bolded UPPER can be modified to reflect what you actually want.

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

 

How to turn spaces into underscore (necessary for uploading your lookup tables into CommCare HQ)

Use: It is recommended that before you upload your lookup table into CommCare HQ that you eliminate the spacing between the words. Example: marie france > marie_france

How: (numbered steps correspond to the images below)

  1. Select the column(s) where the data are located. Then click "Ctrl + F" to show the "Find" box. Go to the tab "Replace" and in the "Find what:" entry box, type a space.

  2. In the "Replace with:" entry box, type an underscore "_"

  3. Click "Replace all."

How to quickly append text/numbers to the end a text:

=CONCATENATE

Use: Imagine that you have a list of districts and another list of villages. Some of the districts have the exact same name as the villages (Ex: District: Bambara, which contains a village also called Bambara). CommCare will require you to distinguish which of the "Bambara" is a village and which is a district. For this example, we will append "_vil" to the end of each village name. 

How: Create a column next to the one containing the words you want to append. Use =CONCATENATE(cell number, "what you want to append"). In this case, we appended _vil. 

When clicking on the new value in the column "Appended Village", you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

How to quickly eliminate duplicate entries from your Lookup list:

Use: To link villages to districts, you need one row per village. But because multiple villages can belong to a single district, this means as a result that you will have several rows where your district is repeated. CommCare will require a separate list for districts and a separate one for villages. For that reason, you will want to eliminate all the duplicate districts from your Excel spreadsheet.

How: (numbered steps correspond to the images below)

1. Select the columns containing duplicate data. Click "Data" on the toolbar, then click "Remove Duplicates"

2. Make sure that you select that your data has headers (if it does), and that you have selected the entire column. Click OK. 

3. You will see a message box with the number of duplicate values that have been removed and the number remaining.

If you want to be able to see which duplicate values were removed

  1. In a new tab, copy - paste your original column with duplicates into column A

  2. Copy - paste your new cleaned up column into column B

  3. In column C, enter the following countif equation: "=COUNTIF(B1:B800, A1)"

  4. In any row that has the value "2" in column C, the column A value is a duplicate

How to (very) quickly fill in a column with a value or a formula:

If you want to quickly pull down a single value, you can double click (don’t drag) on the bottom right corner and the value will fill in bellow for all rows where there is information next to it.  

The RED X shows you were to double click. 

AND the same thing can be done with Functions!  But don't forget to copy and paste value once you've created a column of functions. 

In-Form Lookup Table Querying

Lookup table data can be referenced in forms in ways other than as potential selection choices. This provides a quick example to show you how.

Add the sample table

Download the attached Excel File and upload the file into your project space.

Afterwards you should have the following lookup table available:

Copy the example form

Copy the following text with your web browser and paste it into the tree pane of a CommCare HQ form builder form:

Form Builder clip version 1 id type labelItext:en-default appearance calculateAttr instances relevantAttr /fruit_counter Group Fruit Counter null null null null /fruit_counter/what_kinds_of_fruit_do_you_want_to_count Select What kinds of fruit do you want to count? null null null null /fruit_counter/what_kinds_of_fruit_do_you_want_to_count/citrus Choice Citrus null null null null /fruit_counter/what_kinds_of_fruit_do_you_want_to_count/berry Choice Berry null null null null /fruit_counter/there_are Trigger "There are <output value=""#form/fruit_counter/count"" /> matching fruits in the lookup table" minimal null null not(#form/fruit_counter/what_kinds_of_fruit_do_you_want_to_count = '') /fruit_counter/count DataBindOnly null null count(instance('item-list:fruit')/fruit_list/fruit[type = #form/fruit_counter/what_kinds_of_fruit_do_you_want_to_count]) {} null

 

Afterwards, you should see these questions added into your form:

Test It Out

You will need to be logged in as a mobile user to test the form, either on a mobile phone or by using the Login As feature in live preview to choose a mobile user.

Once you've loaded the form, you should be able to choose a filter, and see an up-to-date count of how many items from the lookup table match the filter.

 

Understand

The lookup table querying is done inside of the #form/count Hidden Value calculation:

count(instance('item-list:fruit')/fruit_list/fruit[type = #form/fruit_counter/what_kinds_of_fruit_do_you_want_to_count])

This shows how the lookup table can be queried with xpath syntax by starting expressions with the following root, with filters applied for additional logic

instance('item-list:fruit')/fruit_list/fruit

For example, to print a list of the names of all citrus fruits instead, you could use the expression

join(" ", instance('item-list:fruit')/fruit_list/fruit[type = 'citrus']/name)

 

Experiment

Once you've loaded the form, you can use the Data Preview XPath Evaluator to experiment with different expressions, and see the data live. 

This tool is especially helpful for experimenting with lookup table queries because it can display for you the intermediate outputs of the filters, which can be a big help when debugging more complex expressions.

NOTE - In order for you to be able to reference a lookup table from this tool, your form needs to already contain at least one reference to the lookup table in another expression (calculation, display condition, selection choices, etc) for the form to be connected to the table. You can see the lookup tables connected to your current form by typing "instance" into the tool and seeing what autocompletes.

Setup a Form to Display a Random Question from Lookup Table

This section will walk through an example of setting up a form that will display a random yes/no question from a lookup table.  This is useful for asking randomized questions for doing knowledge assessment, or to send random behavior change communication out (for example, using CommCare Messaging).  

First set up the following table using the section on creating tables. Set up the table so that they are available for all mobile workers in the project.  

random_question

field: question_id

field: question_text

field: question_category

field: question_id

field: question_text

field: question_category

p01

Do you know the optimal spacing for planting maize?

planting

p02

Do you know the depth of hole to dig for each crop?

planting

p03

Do you know the width of hole for each crop?

planting

f01

Do you know how often you should apply fertilizer?

fertilizer

f02

Do you know the quantity of fertilizer to apply?

fertilizer

f03

Can you identify what a plant looks like that has been over-fertilized?

fertilizer

This table has two categories of questions - this corresponds to two different random questions that we will include in our form. 

Including Lookup Table Information in the Form

Once we've defined a lookup table, we need to reference it in our form.  This is done by adding an "instance" line that references the name of the table.  You can place this above any existing instance tags in your form.  

<instance id="random_questions" src="jr://fixture/item-list:random_question"></instance>

You can choose any word for the id="__" portion.  This is how you'll reference your lookup table in the rest of the form.  For the src="jr://fixture/item-list:____" section, you must specify the Table ID that you created the table with.  

Selecting a Random Question

First, lets add two select question to the form  for each of our random questions.  

Next ,we need to add 3 hidden values per random question we want to ask. These are used for selecting a random question, storing the ID of the selected random question and storing the actual question text of the random question.  Add the following hidden values as shown below.

Now we'll add calculate conditions to each of the hidden values, starting with those for the planting random question.  

In the planting_question_num hidden value, add the following calculate condition: 

if(count(instance('random_questions')/random_question_list/random_question[question_category = 'planting']) > 0, 1 + int(random() * (count(instance('random_questions')/random_question_list/random_question[question_category = 'planting']) - 1)), "")

This calculation looks a bit complicated, but it basically checks the number of questions in the fertilizer category, and randomly picks a number between 1 and the number of questions.   

Note: You may get a notice saying that the expression was too complex to validate.  You can just hit "Ok" as this is expected when using lookup tables.  

 

In the planting_question_id hidden value, add the following calculate condition:

if(/data/planting_question_num != "", instance('random_questions')/random_question_list/random_question[question_category = 'planting'][int(/data/planting_question_num )]/question_id, "") 

This calculation makes sure that a planting_question_num has been calculated, then stores the question_id for the randomly chosen question. 

 

In the planting_question_text hidden value, add the following calculate condition:

if(/data/planting_question_num != "", instance('random_questions')/random_question_list/random_question[question_category = 'planting'][int(/data/planting_question_num )]/question_text, "") 

This calculation is similar to the previous one, but instead of storing the code, it stores the actual question text to be displayed. 

 

Next, we will change the label for the the "Planting Random Question" select question to just display the text of the selected question, using an <output value="..." /> statement.  Change the label as is shown below.

You can now setup similar calculations and labels for fertilizer calculations and question.  You will just need to change the question_category to "fertilizer" instead of planting in any calculations.   

Testing an application with Lookup Tables

In order to test the conditional select options and other logic works in the application, you must sign on as a mobile user and submit 'real' data to the server. 

  1. Generic User: Create a generic user called "dimagi" or "demo" and update the fixture specifically for this user - populating all the conditional select options with the same 'dimagi' or 'demo'. As long as the partner is aware of the use case, they will be able to filter out this user in reports. During training, mobile users will sign onto CommCare and submit live 'practice' data. Work with the partner to specify a date where all data submitted to CommCare HQ is considered actual data for program purposes. This should be made clear to the FLWs and to the data analyst/M&E officer.

  2. Training Application: Copy the final application - remove the conditional select options from the application. This app can be used during training as a training application and the users can enter data in demo mode.

  3. No SIM: If the partner has not secured SIM cards for the phones prior to training, install CommCare on all the phones. Use one SIM card to restore the user or practice user ('dimagi' or 'demo') to all phones. The phone will update according to the latest fixture item list on HQ. Remove the SIM. The conditional select options will work on the phones and the data will not be submitted to the CommCare HQ. 

Setup a Question with Filtered Choices

This section will walk through an example of setting up a form that both displays conditional options in lists (list of districts from chosen state) and has options that are specific to each user. 

First setup the following two tables using the Creating and Updating Lookup Tables section.  You can use whichever mobile workers or groups that you have in your project.  

state: 

field: id

field: name

user 1

group 1

field: id

field: name

user 1

group 1

up

Uttar Pradesh

supervisor

Uttar Pradesh Users

gujurat

Gujurat

supervisor

Gujurat Users

district

field: id

field: name

field: state_id

user 1

group 1

field: id

field: name

field: state_id

user 1

group 1

ah

Ahmedabad

gujurat

supervisor

Gujurat Users

ja

Jamnagar

gujurat

supervisor

Gujurat Users

agra

Agra

up

supervisor

Uttar Pradesh Users

faizabad

Faizabad

up

supervisor

Uttar Pradesh Users

Our sample creates two tables (state and district).  The district table has three fields (id, name and state_id).  The state_id will be used to filter the list of districts based on the selected state.   The following image shows our final setup.   

Create a Multiple Choice/Checkbox Question Based on a Lookup Table

NOTE: Lookup tables referenced in Multiple Choice or Checkbox questions should not have values with spaces in them. This can cause data issues in your app. Please define the values in the lookup tables without spaces.

To create a multiple choice or checkbox question based on a lookup table, first add the question to your form.

In our example, we'll add two multiple choice lookup table questions, "state" and "district".

Now go to "Lookup Table Data" and select the correct lookup table and value and label fields. When inputting the value and labels an autocomplete widget will show you valid options for these fields.  

If you were to test this application, you'd see the first question displays a list of all states and the second question displays a list of all districts (irrespective of the selected state).  To filter the district list, we need to add a filter condition to the choices. 

Filtering the Choices

To filter the set of choices, you can use the 'Filter Option' under 'Lookup Table Data' (similar to the expressions used when defining Display Logic or Calculations in your form).  In our example, we'll filter the District item list based on the selected state.  Our district table contains a field called "state_id".  The values in this match the value specified for the state question.

The left option will show an autocomplete widget to help you pick a lookup table attribute and the right option will allow you to drag a question from the question tree. 

Loading a lookup table value into a hidden value instead of a multiple choice/checkbox question

Sometimes you'll want to pull information from a lookup table into a hidden value. For example, from the example above, you want to save another case property called "project_name" and after the user selects a district, you already know which project it falls under. You create a hidden value question under the district question and name it "project_name". Then you create the following lookup table:

project:

id

name

project_name

user 1

group 1

id

name

project_name

user 1

group 1

ah

Ahmedabad

nutrtition

supervisor

Gujurat Users

ja

Jamnagar

nutrition

supervisor

Gujurat Users

agra

Agra

agriculture

supervisor

Uttar Pradesh Users

faizabad

Faizabad

agriculture

supervisor

Uttar Pradesh Users

In the hidden value you'll enter the following calculation: instance('item-list:project')/project_list/project[id = /data/district ]/project_name. Depending on what the user chooses for district, the value for "project_name" will be either 'nutrition' or 'agriculture'. From the formula, the first 3 'project' refers to the lookup table name. 'id' is the value from the column in the lookup table that is matched with the question 'district', which has '/data/district' path in the form. The last part of the formula is 'project_name' and will be the value taken from the same named lookup table column and loaded into the hidden value. 

How to enter your own value using an 'other' option

There might be times where you want to give the option to enter a location or value that might not be in the lookup table. You can do so by adding an 'other' option to your district table and a text question to your form that will only be displayed when the 'other' option is selected. The following steps will show you how to do so.

Using the example above, we want to allow the user to enter a district that is not in the lookup table. To do so, you can add an ‘other’ field to the district lookup table:

field: id

field: name

field: state_id

user 1

group 1

ah

Ahmedabad

gujurat

supervisor

Gujurat Users

ja

Jamnagar

gujurat

supervisor

Gujurat Users

agra

Agra

up

supervisor

Uttar Pradesh Users

faizabad

Faizabad

up

supervisor

Uttar Pradesh Users

other

Other

 

 

 

You then proceed to create the form like you did above:

Once the locations are set up, you need to add another filter to the lookup table question in the form. This filter will be based on the district ID, so the first field should be set to ‘id’. The second field should be set to ‘is equal’. And the third field should be set to ‘other’. We also want this option to occur only when ‘other’ is selected, so we also need to change the ‘Result is’ field to be ‘True when ANY of the expressions are true.” Be sure to save the expression once you are done!

After saving the new expression and returning to the question properties page, you should see the full expression:

Now you just need to add another text question to the form that will be displayed only when ‘other’ is selected:

Now when your users select ‘other' for the district, they will be able to proceed to another question that will allow them to enter the district name. 

Testing an application with lookup tables

In order to test the filtered choices and other logic works in the application, you must sign on as a mobile user and submit 'real' data to the server.  If you see an error when opening the form that states "Could not find an appropriate fixture for src: jr://fixture/item-list:table_name" you have not properly setup your lookup tables. 

  1. Sync your application. Some users have reported this error with large lookup tables that have not properly downloaded which choosing "Update Commcare" from the menu. Run the sync to ensure all assets are properly downloaded to the device

  2. Make sure that the current user has lookup table rows assigned to them (Lookup Tables | Creating and Updating Lookup Tables).

  3. Alternatively, setup your lookup table so that it is available for all users (For the lookup table, set it up so that it is "Visible to All Users" by clicking Edit Type). 

  4. Once you've done this, make sure you've synced your phone. 

If you run into issues testing your app with lookup tables, try to Clear user data. After you sync your phone if the lookup table still is not working, you may need to clear user data and log-in again. To do this, go to the login screen and click on Settings. After the Settings screen shows up, click the menu button, and select the first option, "Clear User Data." This will clear the username and force you to re-enter login credentials. Once you log in again, the new or edited table should be accessible to your user.

Training an application with lookup tables

  1. Generic User: Create a generic user called "dimagi" or "demo" and update the table specifically for this user - populating all the conditional select options with the same 'dimagi' or 'demo'. As long as the partner is aware of the use case, they will be able to filter out this user in reports. During training, mobile users will sign onto CommCare and submit live 'practice' data. Work with the partner to specify a date where all data submitted to CommCare HQ is considered actual data for program purposes. This should be made clear to the FLWs and to the data analyst/M&E officer.

  2. Training Application: Copy the final application - remove the conditional select options from the application. This app can be used during training as a training application and the users can enter data in demo mode.

  3. No SIM: If the partner has not secured SIM cards for the phones prior to training, install CommCare on all the phones. Use one SIM card to restore the user or practice user ('dimagi' or 'demo') to all phones. The phone will update according to the latest lookup table on HQ. Remove the SIM. The conditional select options will work on the phones and the data will not be submitted to the CommCare HQ.

Factors contributing to form's performance using LookUp Table : 

In many cases, your Lookup table may scale up to a point where it starts to make your form inefficient for user to use. Lookup tables tend to deteriorate the user experience in such scenarios. 

  • Number of rows in a lookup table plays a major role in deciding the form's performance while user navigates to the question using that Lookup Table.

  • Performace of form loading as well as form navigation is independent of size of data in each column of the table. It is also independent of number of Lookup Table type questions have been setup in the form. 

  • You can improve the performance of by indexing a lookup table or using other techniques like using Combo Box for appearance attribute of single-select type lookup table.

Using Lookup Tables with Multiple Languages

For larger applications, you may need to have a lookup table that supports multiple languages.  Based on the language selected for the application, CommCare can be setup to get the correct language information from the table.  

Defining a Lookup Table that Support Multiple Languages

Please review the Creating and Updating Lookup Tables section to understand the basics of defining lookup tables.  Unfortunately, the current interface cannot be used to completely define a table that supports multiple languages.  Instead, we can upload an Excel document that sets up the table for us. 

  1. Define a basic table that includes the information you'd like to store.  Here is an example of a table that stores States.

Note: I've set this table up so that its visible to all users (for simplicity).

2. Download the table from CommCareHQ to add language information. 

3. Modify the types sheet to identify which columns will have multiple languages.  This is done by adding an extra property on the field that will indicate the language. This is highlighted in Red below. 

Delete(Y/N)

table_id

is_global?

field 1

field 2

field 3

field 3 : property 1

Delete(Y/N)

table_id

is_global?

field 1

field 2