Popular Help Content
|Table of Contents|
This page 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.
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.
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> lines in your form.
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 "Tag" that you created the table with.
Create a Select List Based on a Lookup Table
Create a Multiple Choice/Checkbox Question Based on a Lookup Table
NOTE: Lookup tables referenced in 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 a single select question the question to your form. In
In our example, we'll add two single select multiple choice lookup table questions, "state" and "district". Note: you cannot have any answer options associated with the single select question. Delete all answer options in the form buidler, otherwise you will get an error.
Now in your form's XML, find the "select1" tag that corresponds to your question and add an "itemset" tag that references your lookup table. This will display that lookup table's rows as select choices. Make the changes highlighted in Red.
<label ref="jr:itext('state-label')" />
<label ref="jr:itext('district-label')" />
<value ref="id" />
The itemset tag references the instance you specified, and the data within that instance. This is typically in the format "[lookup_table_tag_name]_list/[lookup_table_tag_name]". We have also added label and name tags under each itemset. This references the field in the lookup table that we want to use as the display and what we want to use as the value.
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
To filter an item the set of choices, we use Xpath expressions 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 filter is added in square brackets in the item set.
<label ref="jr:itext('district-label')" />
<itemset nodeset="instance('districts')/district_list/district[state_id = /data/state]">
<value ref="id" />
You can write complex logic within the square brackets. To reference the value of another question, use the "/data/<question_id>" format. To reference a field within the table row, just use its name (ex. state_id).
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:
|id||name||project_name||user 1||group 1|
|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:
Uttar Pradesh Users
Uttar Pradesh Users
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 conditional select options 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.
If you run into issues testing your app with lookup tables, try these two troubleshooting options:
Check to see if the table is visible to your mobile user: To check whether or not the lookup table is visible to your user, go to this link: http://www.commcarehq.org/hq/admin/phone/restore/?as=[mobile username]@[domain]&version=2.0 If the table is visible to your user, you should see the table appear in the XML payload.
Clear user data: After 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.
Large lookup tables may cause an error on the mobile device stating "get field slot from row 0 col 1 failed." This is due to the amount of memory allocated to the lookup table on the mobile device. The lookup table is larger than the available memory. If you get this error, a common work around is to split the table into multiple tables. This can be completed by adding a "more" option to the bottom of your first lookup table and performing a conditional question to display the results of the second lookup table if "more" is selected. For example, there are more than 3,600 Village Development Committees (VDC) in Nepal across 75 districts. One user created a conditional select option to display the VDCs within the selected district. However, the VDC list was too large to display in one lookup table. The user created three lookup tables that each supported the VDCs of 25 districts. Three VDC questions were created with conditional criteria that displayed the VDCs from one of the three lookup tables. Finally, a calculated question was created to determine which VDC was selected from the three VDC questions.
Training an application with lookup tables
- 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.
- 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.
- 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.