Example: In-Form Lookup Table Querying

Owned by Clayton Sims
Last updated: Jun 02, 2021
3 min read

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.

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

Step 2 - 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

Step 3 - Try 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

Step 4 - 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)

Step 5 - 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