Lookup tables: How to create a hidden value that gets a value from a different column, based on which entry the user chose

Eric_Stephan2 · August 20, 2015, 7:38am

Hi folks --

I am starting to make use of lookup tables -- I see you guys made it easier
to set up since I last looked at this!

I will use the lookup table to allow the user choose from a list of the
letters from the Lao alphabet, which will later be "concatted" together in
a hidden value into a code string, like ຂຄ-ງຂກ

To do this, I will have a lookup table that contains all Lao alphabet
characters. Then one question will say "what is the first letter" and it
will present a menu generated by the lookup table to choose the first lao
letter. A second question will do exactly the same.

That is of course easy. But my twist is that I want to also construct a
second version of the resulting code string... that will encode the lao
characters into corresponding LATIN characters. For each lao character,
there will be a corresponding latin character. The Lao character ກ will
have the code of A... the Lao character ກ will be B ... etc. So this "latin
version" resulting from the same set of choices could look like BC-DBA

I am guessing that I can just make an additional field name in my lookup
table, so that the table actually has that additional column... the lookup
table would look like:

1 ກ A 2 ຂ B 3 ຄ C 4 ງ D 5 … …

So let's imagine one of my form questions simply said "what is the first
letter of your code", and I had the question use the lookup table. And the
user chose the second Lao letter, ຂ.

Naturally, the resulting lao letter chosen by the user would be stored in
the form data as the answer for the question itself.

--> BUT my question is: How could I create a way to, in a separate place
in the data (I imagine a hidden value field), return the other value that
corresponded to that choice that the user had made for the first letter? In
other words, I'd love to have a formula in a hidden value that would return
"B", since that's the latin letter that corresponds to their answer. Then I
would simply construct my "latin version of the code" with that method.

... I was imagining that there would be an "instance" call that could look
up the answer that was given BUT to return the value in that third
column... or something like that. But I couldn't find an explanation of how
that Instance call works with a table.

... Another possible way of doing it: Let's assume that instead I used the
normal ID field in the table to store those "Latin values." Is there a call
that could tell me the ID of the value that was chosen in that earlier form
question?

thanks!
Eric

Eric_Stephan2 · August 20, 2015, 7:40am

... and please ignore my typo above where I say "the Lao character ກ will
be B" ... I meant ຂ ... but I think you get the basic gist of it!

William_Pride · August 20, 2015, 12:04pm

Eric,

This is possible by using a lookup table containing the Lao letter (let's
say under the column name "lao") and the corresponding Latin letter (column
"latin"). If you store the selected Lao letter under a hidden value called
/data/first_lao_letter, your calculate statement would look something like:

Where the part is basically saying "filter out the row with the selected
letter" and then we select the 'latin' value from this row.

Hope this makes sense!

Best,
Will

···

On Thu, Aug 20, 2015 at 3:40 AM, Eric Stephan wrote:

... and please ignore my typo above where I say "the Lao character ກ will
be B" ... I meant ຂ ... but I think you get the basic gist of it!

--
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.

Eric_Stephan2 · August 21, 2015, 3:48am

Thank you so much! this is so helpful -- I have follow-up questions about
this as I try to get it to work --

1-- in your example above, did you work on the assumption that I would name
my lookup table lao-latin-map? because I see that string in your code and I
wanted to confirm if that should be the name of the table I made.

2-- for the record, in the test app I am trying to build this in, I went
ahead and named my table lao-latin-map and I made the three columns be id,
lao, and latin -- as you suggested. You can see attached screenshot.

3-- It was interesting that you suggested "If you store the selected Lao
letter under a hidden value called /data/first_lao_letter..." ... is it
definitely best that I store that lao letter in a hidden value like that?
because I was wondering if I could simply reference that letter as right
where it was stored in the Select Field where the user selected it, which I
call /data/code1 ... but maybe there is a reason I should then subsequently
place it into a normal hidden value field.

4-- When I see your code ... Would I place
that code directly into the XML of the form, or can I place it into the
"Calculate Condition" box in a hidden value? This is probably
embarrassingly obvious to you. But you say "your calculate statement..."
in your text above so I was not sure.

5-- If your answer to #4 is that it should be placed in the form XML, can
you give a tip as to where in the code it should belong?

6-- Also, since this "bind" code seems to be storing something into a field
called /data/first_latin_letter ... should I also, in my form, create a
hidden value field that is called /data/first_latin_letter and simply leave
it blank, so that this code can then force the data to be stored into it?

7-- Another question about the XML of the form. Using this technique, will
I need to manually insert one of those <instance id="....... lines into
my form XML to include the lookup table in my form... as some of the online
tutorials still mention? ... Or, as I suspect, is that no longer necessary
as the system now has the new built-in way of handling lookup tables... and
I have already succeeded in having form question use lookup tables without
inserting that code line?

8-- Also, in your code line above, I see that the code includes those two
items in the string ...../letters/letter/..... But nowhere in my process
have I ever defined or configured anything called letters or letter. What
do those two sections of the code string point to? -- should I have created
something called *letters *and letter?

thanks so much for this... I'm sorry I need so much input on this --

Eric

William_Pride · August 21, 2015, 1:41pm

In line:

Thank you so much! this is so helpful -- I have follow-up questions about
this as I try to get it to work --

1-- in your example above, did you work on the assumption that I would
name my lookup table lao-latin-map? because I see that string in your code
and I wanted to confirm if that should be the name of the table I made.

Yes, that's the assumption I made

2-- for the record, in the test app I am trying to build this in, I went
ahead and named my table lao-latin-map and I made the three columns be id,
lao, and latin -- as you suggested. You can see attached screenshot.

3-- It was interesting that you suggested "If you store the selected Lao
letter under a hidden value called /data/first_lao_letter..." ... is it
definitely best that I store that lao letter in a hidden value like that?
because I was wondering if I could simply reference that letter as right
where it was stored in the Select Field where the user selected it, which I
call /data/code1 ... but maybe there is a reason I should then subsequently
place it into a normal hidden value field.

I was just using the hidden value for the sake of illustration - that
calculation could be used anywhere.

4-- When I see your code ... Would I
place that code directly into the XML of the form, or can I place it into
the "Calculate Condition" box in a hidden value? This is probably
embarrassingly obvious to you. But you say "your calculate statement..."
in your text above so I was not sure.

Either one would work - a hidden value for that calculation with an ID
first_latin_letter would result in an XML output identical to the bind I
posted.

5-- If your answer to #4 is that it should be placed in the form XML, can
you give a tip as to where in the code it should belong?

N/A due to (4)

6-- Also, since this "bind" code seems to be storing something into a
field called /data/first_latin_letter ... should I also, in my form, create
a hidden value field that is called /data/first_latin_letter and simply
leave it blank, so that this code can then force the data to be stored into
it?

N/A due to (4)

7-- Another question about the XML of the form. Using this technique, will
I need to manually insert one of those <instance id="....... lines
into my form XML to include the lookup table in my form... as some of the
online tutorials still mention? ... Or, as I suspect, is that no longer
necessary as the system now has the new built-in way of handling lookup
tables... and I have already succeeded in having form question use lookup
tables without inserting that code line?

You would, but I believe this would already have been inserted for you due
to using the question.

8-- Also, in your code line above, I see that the code includes those two
items in the string ...../letters/letter/..... But nowhere in my process
have I ever defined or configured anything called letters or letter. What
do those two sections of the code string point to? -- should I have created
something called *letters *and letter?

That was just an example if you'd named and structured your fixture as such
(IE first node "letters", second node "letter") - the actual calculation
will need to be modified to traverse your fixture to point to latin/lao
character.

thanks so much for this... I'm sorry I need so much input on this --

Eric

Best,
Will

···

On Thu, Aug 20, 2015 at 11:48 PM, Eric Stephan wrote:

--
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.

Sheel_Shah · August 21, 2015, 2:07pm

Hi Eric,

The instance('something') refers to the name of your lookup table. You
don't need to manually add this to your form if you already have a lookup
table question for that lookup table in your form.

The format of the syntax in the hidden value is:
instance('something')/something_list/something[column_to_lookup =
/data/question]/column_to_store

So assuming your lookup table is called *lao_letter *and has the columns
lao and latin and your form has the following questions:
/data/lao_question1 [lookup table question]
/data/lao_question2 [lookup table question]
...
/data/latin_answer1 [hidden value]
/data/latin_answer2 [hidden value]
...

Then your hidden calculation should look like:
/data/latin_answer1 -> if(/data/lao_question1 != '',
instance('lao_letter')/lao_letter_list/lao_letter[lao =
/data/lao_question1]/latin, '')

Thanks,
Sheel

···

On Friday, August 21, 2015, Eric Stephan <estephan@fhi360.org > wrote:

Thank you so much! this is so helpful -- I have follow-up questions about
this as I try to get it to work --

1-- in your example above, did you work on the assumption that I would
name my lookup table lao-latin-map? because I see that string in your code
and I wanted to confirm if that should be the name of the table I made.

2-- for the record, in the test app I am trying to build this in, I went
ahead and named my table lao-latin-map and I made the three columns be id,
lao, and latin -- as you suggested. You can see attached screenshot.

3-- It was interesting that you suggested "If you store the selected Lao
letter under a hidden value called /data/first_lao_letter..." ... is it
definitely best that I store that lao letter in a hidden value like that?
because I was wondering if I could simply reference that letter as right
where it was stored in the Select Field where the user selected it, which I
call /data/code1 ... but maybe there is a reason I should then subsequently
place it into a normal hidden value field.

4-- When I see your code ... Would I
place that code directly into the XML of the form, or can I place it into
the "Calculate Condition" box in a hidden value? This is probably
embarrassingly obvious to you. But you say "your calculate statement..."
in your text above so I was not sure.

5-- If your answer to #4 is that it should be placed in the form XML, can
you give a tip as to where in the code it should belong?

6-- Also, since this "bind" code seems to be storing something into a
field called /data/first_latin_letter ... should I also, in my form, create
a hidden value field that is called /data/first_latin_letter and simply
leave it blank, so that this code can then force the data to be stored into
it?

7-- Another question about the XML of the form. Using this technique, will
I need to manually insert one of those <instance id="....... lines
into my form XML to include the lookup table in my form... as some of the
online tutorials still mention? ... Or, as I suspect, is that no longer
necessary as the system now has the new built-in way of handling lookup
tables... and I have already succeeded in having form question use lookup
tables without inserting that code line?

8-- Also, in your code line above, I see that the code includes those two
items in the string ...../letters/letter/..... But nowhere in my process
have I ever defined or configured anything called letters or letter. What
do those two sections of the code string point to? -- should I have created
something called *letters *and letter?

thanks so much for this... I'm sorry I need so much input on this --

Eric

--
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.

--
Sheel Shah
Project Manager | Dimagi
m: +1.781.428.5419 | skype: sheel_shah

Eric_Stephan2 · August 24, 2015, 5:00am

You guys -- I took your advice and this works. Giant thanks for the help!
Eric

Rob1 · August 27, 2015, 5:37am

Thanks Sheel, that's exactly what I needed.

Is there an IsNull() type function in Commcare?

I'm using the following adaption of your code for a number field but the
coalesce seems a bit clunky.

if(coalesce(/data/number_question,0) !=0,
instance('something')/something_list/something[column_to_lookup =
/data/number_question]/number_column_to_store,0)

Rob

Sheel_Shah · August 27, 2015, 6:14am

Hey Eric,

To check if something is blank you can just use if /data/question = ''
(That's two single quotes, not one double quote).

Thanks,
Sheel

···

On Thu, Aug 27, 2015 at 11:07 AM, iotc iotc wrote:

Thanks Sheel, that's exactly what I needed.

Is there an IsNull() type function in Commcare?

I'm using the following adaption of your code for a number field but the
coalesce seems a bit clunky.

if(coalesce(/data/number_question,0) !=0,
instance('something')/something_list/something[column_to_lookup =
/data/number_question]/number_column_to_store,0)

Rob

--
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.

--
Sheel Shah
Project Manager | Dimagi
m: +1.781.428.5419 | skype: sheel_shah

Rob1 · August 27, 2015, 7:11am

Thanks - it works! I got thrown off track by a type mismatch error which
must have been referring to something else - Regards Rob

Alexandra_Hinton · October 18, 2017, 2:16pm

https://lh3.googleusercontent.com/-zr31tIAzX8g/Wedg6QkfBZI/AAAAAAAANuc/6dhyAa8eZ6oyPkE9NUrr1F_vgoGvoVS8wCLcBGAs/s1600/WMAT_Users%2BLookup%2BTable.png
I have a question very similar to this one and I'm hoping you all could
help me figure out the correct coding for my hidden value.

I need to create a hidden value with the username associated with the event
type and community entered into a form.

Communities are stored in a lookup table along with the usernames assigned
to those locations. There are two teams, so there are two usernames
associated with each community (field: CL_username and field:
EMPWR_username).

So, if question
type_of_self-harming_behavior = 'binge_substance_use_ie_alcohol_or_drug_use'
and
community_where_you_usuallymost_often_reside = 'CarizzoGeneral'
then the hidden value should be:
abeach5

Alternatively, if type_of_self-harming_behavior = anything option other
than binge
and
community_where_you_usuallymost_often_reside = 'CarizzoGeneral'
then the hidden value should be:
acooley5

What should the hidden value code look like?

Thanks!!

···

On Thursday, August 27, 2015 at 3:11:11 AM UTC-4, Rob wrote: > > Thanks - it works! I got thrown off track by a type mismatch error which > must have been referring to something else - Regards Rob > >

Clayton_Sims · October 20, 2017, 6:38pm

Hi Alexandra,

Just so I understand: The behavior you want is for the list here to be
filtered by the community, and then you want to choose one of two columns
based on a question?

I think in your case you would want the expression

if(
type_of_self-harming_behavior = 'binge_substance_use_ie_alcohol_or_drug_use'
instance('item-list:WMAT_users')/WMAT_users_list/WMAT_users[Community=#form/
community_where_you_usuallymost_often_reside]/EMPWR_username,
instance('item-list:WMAT_users')/WMAT_users_list/WMAT_users[Community=#form/
community_where_you_usuallymost_often_reside]/CL_username
)

-Clayton

···

On Wed, Oct 18, 2017 at 10:16 AM, Alexandra Hinton wrote:

https://lh3.googleusercontent.com/-zr31tIAzX8g/Wedg6QkfBZI/AAAAAAAANuc/6dhyAa8eZ6oyPkE9NUrr1F_vgoGvoVS8wCLcBGAs/s1600/WMAT_Users%2BLookup%2BTable.png
I have a question very similar to this one and I'm hoping you all could
help me figure out the correct coding for my hidden value.

I need to create a hidden value with the username associated with the
event type and community entered into a form.

Communities are stored in a lookup table along with the usernames assigned
to those locations. There are two teams, so there are two usernames
associated with each community (field: CL_username and field:
EMPWR_username).

So, if question
type_of_self-harming_behavior = 'binge_substance_use_ie_
alcohol_or_drug_use'
and
community_where_you_usuallymost_often_reside = 'CarizzoGeneral'
then the hidden value should be:
abeach5

Alternatively, if type_of_self-harming_behavior = anything option other
than binge
and
community_where_you_usuallymost_often_reside = 'CarizzoGeneral'
then the hidden value should be:
acooley5

What should the hidden value code look like?

Thanks!!

On Thursday, August 27, 2015 at 3:11:11 AM UTC-4, Rob wrote:

Thanks - it works! I got thrown off track by a type mismatch error which
must have been referring to something else - Regards Rob

--
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.

bbataye · August 6, 2019, 3:19pm

Hello everyone,

I have been using this method to pull data from 4 separate look up tables across an adult survey and a youth survey: One for adult males, one for adult females, one for youth males, and one for youth females. I have uploaded the tables to commcare under the titles male_peer and female_peer for the adults and male_ypeer and female_ypeer for the youth. The code to pull the data from the adult tables seems to work just fine. The peer look up tables seem to work just fine in the application but the code to pull the information from the look up tables does not work. I made sure that the look up tables are referencing the appropriate look up tables. Can someone let me know what might be going on with my code?

Ethan_Soergel · August 6, 2019, 4:37pm

When you say that it doesn't work, what do you mean? Do you get a blank value, an incorrect value, or an error?

That expression says essentially:

if p1namem is not blank, display it
otherwise display the name of the feemale ypeer with the appropriate LID
or if that is blank, the name of the male ypeer with the appropriate LID

I'd also advise testing this out in the "Evaluate XPath" tool in Data Preview, or by adding labels to your form so you can ensure each component of that expression works as you expect.

bbataye · August 6, 2019, 9:37pm

Thank you for your quick response! I mean that the survey will not run because of an error. It is strange because it mimics the adult code exactly aside from the look up table name (Code here).

I entered the xpath into the evaluator and this message appeared "cannot convert multiple nodes to a raw value. Refine path expression to match only one node." Do you have any idea as to why an error would appear for the youth code given that it mimics the already working adult code?

Clayton_Sims · August 6, 2019, 10:00pm

Hi Brian,

Does the error provide any text that would be helpful?

bbataye · August 7, 2019, 1:35pm

Hi,

Thank you all for the help! Here is the full error message:

Calculation Error: Error in calculation for /data/survey_post-consent/peer_selection/name_generator_1/question_abt_ppl_you_talk_abt_personal_matter/p1select/eq_peer_1_name XPath nodeset has more than one node [instance(female_ypeer)/female_ypeer_list/female_ypeer[534]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[535]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[536]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[537]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[538]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[539]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[540]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[541]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[542]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[543]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[544]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[545]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[546]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[547]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[548]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[549]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[550]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[551]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[552]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[553]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[554]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[555]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[556]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[557]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[558]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[559]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[560]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[561]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[562]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[563]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[564]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[565]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[566]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[567]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[568]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[569]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[570]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[571]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[572]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[573]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[574]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[575]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[576]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[577]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[578]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[579]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[580]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[581]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[582]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[583]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[584]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[585]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[586]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[587]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[588]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[589]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[590]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[591]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[592]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[593]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[594]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[595]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[596]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[597]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[598]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[599]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[600]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[601]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[602]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[603]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[604]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[605]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[606]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[607]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[608]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[609]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[610]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[611]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[612]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[613]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[614]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[615]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[616]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[617]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[618]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[619]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[620]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[621]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[622]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[623]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[624]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[625]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[626]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[627]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[628]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[629]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[630]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[631]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[632]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[633]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[634]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[635]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[636]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[637]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[638]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[639]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[640]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[641]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[642]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[643]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[644]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[645]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[646]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[647]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[648]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[649]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[650]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[651]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[652]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[653]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[654]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[655]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[656]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[657]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[658]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[659]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[660]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[661]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[662]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[663]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[664]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[665]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[666]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[667]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[668]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[669]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[670]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[671]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[672]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[673]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[674]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[675]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[676]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[677]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[678]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[679]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[680]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[681]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[682]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[683]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[684]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[685]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[686]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[687]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[688]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[689]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[690]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[691]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[692]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[693]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[694]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[695]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[696]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[697]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[698]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[699]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[700]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[701]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[702]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[703]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[704]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[705]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[706]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[707]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[708]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[709]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[710]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[711]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[712]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[713]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[714]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[715]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[716]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[717]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[718]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[719]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[720]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[721]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[722]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[723]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[724]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[725]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[726]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[727]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[728]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[729]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[730]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[731]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[732]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[733]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[734]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[735]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[736]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[737]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[738]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[739]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[740]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[741]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[742]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[743]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[744]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[745]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[746]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[747]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[748]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[749]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[750]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[751]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[752]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[753]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[754]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[755]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[756]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[757]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[758]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[759]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[760]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[761]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[762]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[763]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[764]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[765]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[766]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[767]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[768]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[769]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[770]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[771]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[772]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[773]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[774]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[775]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[776]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[777]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[778]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[779]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[780]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[781]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[782]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[783]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[784]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[785]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[786]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[787]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[788]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[789]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[790]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[791]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[792]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[793]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[794]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[795]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[796]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[797]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[798]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[799]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[800]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[801]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[802]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[803]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[804]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[805]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[806]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[807]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[808]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[809]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[810]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[811]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[812]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[813]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[814]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[815]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[816]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[817]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[818]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[819]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[820]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[821]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[822]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[823]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[824]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[825]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[826]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[827]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[828]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[829]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[830]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[831]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[832]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[833]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[834]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[835]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[836]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[837]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[838]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[839]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[840]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[841]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[842]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[843]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[844]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[845]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[846]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[847]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[848]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[849]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[850]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[851]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[852]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[853]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[854]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[855]/name[1];instance(female_ypeer)/female_ypeer_list/female_ypeer[856]/name[1]]; cannot convert multiple nodes to a raw value. Refine path expression to match only one node.

Any thoughts?

Ethan_Soergel · August 7, 2019, 1:52pm

That error means that this part of the expression matches more than one row:

instance(female_ypeer)/female_ypeer_list/female_ypeer[LID = p1name_f]/name

You wrote

I entered the xpath into the evaluator and this message appeared “cannot convert multiple nodes to a raw value. Refine path expression to match only one node.”

Try evaluating only parts of the expression. What is the value of p1name_f? Is it supposed to match only one row?

Rather than a coalesce, I'm guessing you probably want something like this, where you choose which expression to used based on whether that value is available

if(/data/p1name_f,
   instance(female_ypeer)/female_ypeer_list/female_ypeer[LID = p1name_f]/name,
   instance(male_ypeer)/male_ypeer_list/male_ypeer[LID = p1name_m]/name)

bbataye · August 7, 2019, 3:46pm

Hello,

The value of p1name_f is a participant ID. Depending on the look up table, it can call from youth males, youth females, adult males, or adult females. There are four separate look up tables. I changed the file path from youth "female_ypeer" to adult "female_peer" and the code worked just fine. Would this then mean that the issue is with the table itself? I tried recreating the table and it still did not work. Does Commcare have a limit on how many tables can be included?

Ethan_Soergel · August 8, 2019, 9:39pm

Have you verified that when this error is triggered, the value of p1name_f is actually a participant ID and not blank? The Expression Tester should let you run something like this to inspect that lookup table:

instance('female_ypeer')/female_ypeer_list/female_ypeer

It's possible there's a problem with the table itself. In order for your expression to work, there must be exactly one row in the lookup table where LID equals the value of p1name_f.