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

[Eric Stephan]

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 Stephan]

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

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:

<bind nodeset='/data/first_latin_letter' calculate = 'instance('lao-latin-map')/letters/letter/[lao=/data/first_lao_letter]latin' />

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 <este...@fhi360.org> 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-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eric Stephan]

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 <bind nodeset.............. />   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]

In line:

On Thu, Aug 20, 2015 at 11:48 PM, Eric Stephan <este...@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.

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 <bind nodeset.............. />   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

[Sheel Shah]

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 <este...@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 <bind nodeset.............. />   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-user...@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 Stephan]

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

[iotc iotc]

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]

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

--

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-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob]

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]

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

[Clayton Sims]

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

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