Challenge retrieving data from lookup table for calculation in form

Jos van der Ent

unread,

Apr 5, 2017, 3:33:17 AM4/5/17

to commcare-users

Hi All,

I am building an app where I want user to select a source of input from a lookuptable and than retrieve a value from the same lookup table which is to be used in a calculation. E.g. when the user selected Market Waste I want the density value of 0.8 to be used in calculations (see example below).

Source | Density

Market waste | 0.8

Brewery waste | 0.7

I have been looking at the page how to calculate z-scores, which I think is using this sample, but the calculations are actually confusing me.(https://wiki.commcarehq.org/display/commcarepublic/Calculate+a+Z-Score+in+a+Form)

Can somebody assist me how to retrieve the data from the look-up table in my case?.Once I understand the structure I can than replicate on other forms as wel.

My lookup table looks like this 'types':

Delete(Y/N)	table_id	is_global?	field 1	field 2	field 3	field 4	field 5	field 6
N	compost_sources	yes	category	id	name	lat	lon	density

The parameter tab is called 'compost_sources' with its first rows:

UID	Delete(Y/N)	field: category	field: id	field: name	field: lat	field: lon	field: density
XXXY		solid	sw_ashsawmill	Ashaiman Saw Mill			0.4
XXXZ		solid	sw_mm	Main Market			0.7

Clayton Sims

unread,

Apr 6, 2017, 3:13:08 PM4/6/17

to commcare-users

Hi!

Sorry for the confusion on this!

When loading values from a lookup table based on a selection in the form you can imagine that what the multiselect question is doing is providing you with the "id" part of the table, and that when you load values in the form later, you reference the selected ID to distinguish which "row" of the table you are looking up.

the pattern for a lookup table "reference" is:

instance('item-list:compost_sources')/compost_sources_list/compost_sources[id = #form/SELECT_QUESTION_ID]/FIELD_NAME

where you put your multiselect question in for the SELECT_QUESTION_ID and the field name in this case would be density

It's also often helpful to load the individual values (like density) into their own hidden value for clarity and debugging.

This would look something like:

Multiselect Question:

question_id: selected_source_id

Input Source: Lookup Table

Label: name

Value: id

Hidden Value:

question_id: density

calculation: if( #form/selected_source_id = '', 0, instance('item-list:compost_sources')/compost_sources_list/compost_sources[id = #form/selected_source_id]/density)

Then you can reference #form/density elsewhere in the form in an easy manner.

Does that clarify things at all?

-Clayton

--
You received this message because you are subscribed to the Google Groups "commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Jos van der Ent

unread,

Apr 7, 2017, 6:41:48 AM4/7/17

to commcare-users

Clayton,
Your instructions helped me to get it to work, so things are calculated now as I intended. Thanks.

I like the suggestion to first retrieve the value to a hidden value first when you intend to do calculations on it. In the end of my questionnaire I want to summarize the inputs, so the field worker gets an overview of the data put in. For some field it would not make sense to make a hidden value first. I tried putting the reference in a label field directly but that does not seem to work.

e.g.:
I would like to show the name field (Ashaiman Saw Mill) and not the id (sw_ashsawmill)

is there a way to put the reference directly without creating a hidden first?

Regards Jos

Clayton Sims

unread,

Apr 7, 2017, 10:22:09 AM4/7/17

to commcare-users

Hi Jos,

Great to hear that things are working as intended!

It makes sense that sometimes you'd want to do the references directly. It sounds like you have two questions

1) How to put a lookup table reference into the Question Label directly.

For this one when you are referencing a value in a label that we haven't managed to make "Drag and Drop"-able yet, you'll need to "wrap" the reference

The syntax is:

where you replace the YOUR_EXPRESSION section with the reference you are trying to display. In this case if you wanted to display the above directly you could copy paste that in and make the expression

Note: I'd actually recommend continuing to put each one of those references directly into a hidden value and dragging the hidden value into the output label instead.

That will mean you'll have a few "extra" nodes in your form, but when you look at the labels in your form later they will be much cleaner, and we're not charging by the question! You should of course go with whatever makes the most sense for your use case though.

2) How to reference the name.

After you've established the selected_source_id question in the form, the name of the source can be thought of just another "field" of the source row that you reference with the same format as density, IE:

instance('item-list:compost_sources')/compost_sources_list/compost_sources[id = #form/selected_source_id]/name

-Clayton

--

Jos van der Ent

unread,

Apr 14, 2017, 3:47:21 PM4/14/17

to commcare-users

HI Clayton,
Thanks again. I have been trying your option by putting it in a label question, but I ran into errors. When I adjust the syntax to my needs I get the following error when I try to save:

It looks like your form is not valid XML. This can often happen if you use a reserved character in one of your questions. Characters to look out for are <, >, and &. You can still save, but you CANNOT LOAD THIS FORM again until you fix the XML by hand. What would you like to do?

I entered the suggestion above (For ease of testing I used the same example as above), which resulted in my case in this to the following syntax:

<output value="if( #form/src_1/src_1_name = ' ', 0, instance('item-list:compost_/sources')/compost_sources_list/compost_sources[id = #form/src_1/src_1_name ]/density)"/>

Did I make a mistake in the syntax?

Clayton Sims

unread,

Apr 14, 2017, 6:08:43 PM4/14/17

to commcare-users

Hi Jos,

I noticed that in the instance ref you have the text "item-list:compost_/sources", is that a mistake in the transcription?

It looks like HQ might be upset about the mixing of the #form references in the expanded <output> ref.

Can you try copy/pasting this instead?

Jos van der Ent

unread,

Apr 18, 2017, 2:42:18 AM4/18/17

to commcare-users

Clayton,

The "/" is a mistake and was probably introduced, because I could not 1:1 copy from the formmaker.

I also tied your ref and it worked fine. Looking at your syntax, you replace "#form" with "data" just as simple as that?

This method helps us the easier deal with names changes ( E.g.The field operators just see the new name, meanwhile on the background we can keep on using the 'old' id)

Jos

Jos van der Ent

unread,

Apr 18, 2017, 6:50:35 AM4/18/17

to commcare-users

Clayton,

As an addition I think I found the cause/more details:

Both options actually work (#form or /data/: If I type the complete reference in a different editor and paste it into the form editor the 'reference script' is not triggered. With reference script I refer to the feature in the form editor that references are recognised and the blue blue is put around it.

If I type the reference directly in the editor that the script is triggered and I get a dropdown with the form references. If I try to save after that I get the warning as I mentioned earlier.

The out value fields below show the same result.

To me this looks like unwanted behaviour, do you suggest to make a issue report of it?

Regards Jos

Clayton Sims

unread,

Apr 18, 2017, 11:49:27 AM4/18/17

to commcare-users

Hi Jos!

Thanks for experimenting across it, we'd absolutely appreciate the report. Using a mix of our raw syntax along with the "bubbles" that get created to help shorten and ease expression creation has some fuzzy edges, and it's super helpful for the team to know where they are.

Would appreciate if you would mention in the ticket that you aren't "blocked" specifically from building your form currently, so the team doesn't escalate the ticket into the time-sensitive stream.

Let me know if there's anything else I can help out with!

-Clayton

Jos van der Ent

unread,

Apr 18, 2017, 12:48:15 PM4/18/17

to commcare-users

Thanks A lot for your support. I reported it, so I suspect when there is time, the developers will have a look to resolve it.

Reply all

Reply to author

Forward