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.(Calculate a Z-Score in a Form - CommCare Public - CommCare Public)
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
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
···
On Wed, Apr 5, 2017 at 3:33 AM, Jos van der Ent wrote:
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.(
Home - CommCare Public - CommCare Public
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
--
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.
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
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
- 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.
- 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
···
On Fri, Apr 7, 2017 at 6:41 AM, Jos van der Ent wrote:
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
--
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.
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?
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 ref.
Can you try copy/pasting this instead?
···
On Fri, Apr 14, 2017 at 3:47 PM, Jos van der Ent wrote:
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?
--
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.
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
···
>
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
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
···
On Tue, Apr 18, 2017 at 6:50 AM, Jos van der Ent wrote:
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
--
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.
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.