Get custom data from choices sheet for calculation

Support
1

I have some lists on choices sheet with a custom "code" column like this:

list_name       name                      label                      code
------------------------------------------------------------------------------------
states          massachusetts             Massachussets              MA
states          florida                   Florida                    FL
states          new-york                  New York                   NY

How can I have a calculation in survey to getting the code of a given state? I know I can do this with pulldata() from an external CSV, but is there a way to do this simply choices sheet?

2

You can do this with an XPath query.

For example, in a formula, you can reference instance('states')/root/item[name=${stateFieldName}]/code to get the code of the selected stateFieldName (I'm assuming that's the name of the select_one field in the survey.

1 Like
3

Sorry to zombify this topic, but I am not able to achieve this. I would like to get a value from a column of a list in the choices sheet (not from a extarnal csv file) and I can't. I get no error, no value.

I would like to get, from the "stations" list the value of the column "month_03" for tech selected item.
In the calculation column of my calculate field I have this xpath expression :

instance('stations')/root/item[name=${station}]/month_03
4

In xls it would be something like this for example: choiceNameTest-export.xlsx (8.3 KB)
Would that solve your problem?

5

thank's for your help @Grzesiek2010, but it is not exactly what I am trying to do, I would like to get the value of another column describing my choice.
Here is the context : in a form focusing on lagoon water level monitoring, I want to show a note with the water level expected in the management plan for the date of the day.
In the form above, if I select station b, I would like to print 15 as the expected water level for today. The expression mentioned as a solution in this topic looks good but I can't make it run for me.

instance('stations')/root/item[name=${station}]/month_03

expected_value_for_a_selected_object_this_month.xlsx (8.8 KB)

6

The problem is with the way the converter works.
If you try to convert your form (without that instance..., to make it work) the output does not contain any info about those additional columns (they just don't exist in the output xml file).
In order to use it you need to have in your xml a section like:

         <instance id="stations">
            <root>
               <item>
                  <itextId>static_instance-stations-0</itextId>
                  <name>a</name>
                  <month_02>10</month_02>
                  <month_03>5</month_03>
                  <month_01>5</month_01>
               </item>
               <item>
                  <itextId>static_instance-stations-1</itextId>
                  <name>b</name>
                  <month_02>20</month_02>
                  <month_03>15</month_03>
                  <month_01>10</month_01>
               </item>
               <item>
                  <itextId>static_instance-stations-2</itextId>
                  <name>c</name>
                  <month_02>30</month_02>
                  <month_03>25</month_03>
                  <month_01>15</month_01>
               </item>
            </root>
         </instance>

One way of forcing the converter to keep that data is using choice_filter what I did:
expected_value_for_a_selected_object_this_month.xlsx (6.3 KB)

of course you don't need it so after converting the form I recommend editing your xml file manually and edit line:

<itemset nodeset="instance('stations')/root/item[month_01='5']">

to

<itemset nodeset="instance('stations')/root/item">

and it should work.
Maybe there is a better and easier way how to keep the data you need during converting @LN any idea?

7

You can see an example of this kind of querying in the docs section on dynamic defaults from form data. Look at the example in "XLSForm that looks up default values based on a selection" where we show using a choice_filter of true() to force the creation of a secondary instance. That is equivalent to not having a filter at all because all values are included. That's what I would recommend for now.

1 Like
8

Oh great!
@mathieubossaert so your form would be like:
expected_value_for_a_selected_object_this_month.xlsx (6.2 KB)

9

Thanks a lot @Grzesiek2010 and @LN , I think I now better understand the process behind the xlsform and its instanciation.

In my case I already have a choice filter on the stations select_one, to only show the stations that are relevant for the current lagoon. So I would imagine that the secondary instance was already create.
The example above (with a logical close to the real one) works fine, so my conclusion is that I have a syntax problem in my real form.
lagoon_station_default_value.xlsx (7.0 KB)

I appologize I waste your time :frowning:

10

What's wrong because I downloaded and converted your form and everything seems fine?

11

Everything is ok in this simple example.
I have to find my problem in the real bigger form. But now I know I can achieve what I need... Maybe am I too tired to see the big bug in my xlsform...
Thanks again.

12

I find my problem but can't solve it because of my lack of knowledge and practicing xpath.
There is a repeat group containing the station and I don't figure out how to get a good xpath expression. lagoon_station_default_value_within_repeat_group.xlsx (7.4 KB)

13

indexed-repeat seems to be a solution:
lagoon_station_default_value_within_repeat_group.xlsx (7.4 KB)

1 Like
14

It works great. Thanks a lot. It allows me to finalize a form I will share on the forum this spring or summer.
I know I now have to investigate the xml side of ODK.
Thanks again.

15

@LN How do I do the same for a value inside a repeat and the calculation is also inside a repeat? I am getting use index_repeat error.

instance("uganda_tree_species")/root/item[name=${advance_item}/item_cost_index

The advance_item field is inside the repeat.

16

Figured this out. The values/names of the choices should be unique

1 Like
17

Hi @MattFoy. But how can we achieve this from the external choices sheet?
I am using an external choices sheet as well.