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.
...
| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Multiexcerpt include macro | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
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:
...
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.
...
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 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.
...
<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.
...
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 |
|---|---|---|---|---|
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.
...
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 |
|---|---|---|---|---|---|---|
N | state | yes | population | id | name | lang |
4. Modify the
...
state sheet to add the list of states with each language. We'll also need to modify this sheet's columns to include the additional language information. You will need to add columns for each language supported. The example below includes two languages, but you can add additional columns titled name: lang X and field: name X for more languages.
...
UID | Delete(Y/N) | field: population | field: id | name: lang 1 | field: name 1 | name: lang 2 | field: name 2 |
|---|---|---|---|---|---|---|---|
1000 | up | en | Uttar Pradesh | hin | उत्तर प्रदेश | ||
1500 | gujurat | en | Gujurat | hin | गुजरात | ||
1583 | bihar | en | Bihar | hin | बिहार |
5. Upload the updated Excel file to CommCareHQ. You can check "Replace Existing Tables" to replace any existing data for this table on CommCareHQ.
...
6. You can review the uploaded data by clicking the View Table link next to the table on the page.
Using Multiple Languages with Multiple Choice/Checkbox Questions
...
lang-code and label text that matches the language code for each language. We'll also set a Display Condition of 1=2 on this to ensure that it doesn't appear in the form. This label will be used to determine which language to display, based on what language is chosen.
...
2. Now add a single or multiple answer lookup table question question to the form.
...
3. Choose the Lookup Table Data item.
a. Set the lookup table to the lookup table you uploaded (ex. state)
...
c. Set the display text field to the following: name[@lang = jr:itext('lang-code-label')] . You can replace name with the column that contains multiple languages.
...
4. Update and Save your form. Now the list of options should change their label based on what language is chosen for the application. However, the data stored will remain the same.
Testing an application with lookup tables
...