Data retrieval from Lookup Table

Users
1

Hi all,

I'm working on an app where I want to select a different livestock feeds from two different lookup tables using multichoice questions and then use hidden values to retrieve data for labels and calculations with the name, energy content, and protein content of the respective feeds.

Roughage lookup table
Roughage lookup table1505×190 13.2 KB
Concentrate lookup table
Concentrate lookup table1507×183 12.1 KB
Lookup Table mutiselect
Lookup Table mutiselect472×712 30.2 KB

I've been following the instructions found here Challenge retrieving data from lookup table for calculation in form but receive the error that the hidden value R1_identify 'references id which is not a valid question or value.'

My reference for the lookup table in the hidden value is:
if(#form/feedstuffs/feed/R1_select = ' ', 0, instance('item-list:BD_Roughage')/BD_Roughage_list/BD_Roughage[id = #form/feedstuffs/feed/R1_select]/roughage_name)

I'm unsure where this problem is coming from. Is it the hidden value reference or the multichoice question lookup itself?

Regards,
Bethany

2

Hi Bethany,

My interpretation of the error is that the BD_Roughage lookup table doesn't contain a column named id.

This portion of the expression:

[id = #form/feedstuffs/feed/R1_select]

Is attempting to filter the BD_Roughage table by matching a column from the table named id to the result of the R1_select question.

In your case it looks like the uploaded column name is roughage_id instead.

-Clayton

3

Hi @Clayton_Sims,

Many thanks! It's currently working. I also had a problem within the hidden value for the ME expression but your suggestion helped identify the fix there too.

Previously, in setting out the lookup tables, I did try utilising repeat groups for a more robust approach but was stuck when it came to retrieving data from the separate lookup selections within the repeat groups. Is there a best way to go about this retrieval e.g. using current() or accessing by count /data/child_repeat[1]/name?

Thanks again for the support!

Regards,
Bethany

4

Hi Bethany,

Coordinating references in between repeat groups is definitely a tricky thing to configure and will require using some advanced aspects of the system, as you've identified.

There's a Demo of this functionality that you can import into your app as an example to get a sense of how to plug those pieces together, it's probably the best place to start.