Creating a calculate field that uses answers to a previous multiple choice cascading question

Support
1

1. What is the issue? Please be detailed.

I do not know how to write up the calculate field question that I need.

The sequence of questions is as follows:

(Q1) Which district do you live in: District A, B C, etc. (single choice)

(Q2) Which crops do you grow: Crop 1, 2, 3 (this is a multiple choice cascading question where possible chocies of crops grown depend on the district selected in Q1 - a maximum of 2 crops may appear as possible choice for each district - some crops appear as a choice in more than 1 district)

(Calculate Field that I need help for):

I need 2 calculate fields named “crop1” and “crop2”. Each of them will simply give the name of the crop if that crop was selected in Q2. It is possible that in Q2, only 1 of the 2 choices presented was selected, in that case, the second calculate field will not be needed.

The objective of these 2 calculate fields is that later in the questionnaire, I can include the crops’ names (${crops1} and ${crop2}) in the label of the questions. However, in the case where only 1 crop would have been selected in Q2, I would not need any of the questions only related to ${crop2} to appear.

In the attached Excel, I need your help to know what to write in the orange cells please.

2. What steps can we take to reproduce this issue?

I can send my excel questionnaire for you to see how Q1 and Q2 are set up. Because this links to how to set the calculate field

3. What have you tried to fix the issue?

I tried several calculate formulas in the survey sheet but it doesnt work. I also tried changing the format of Q1 and Q2 without making the survey too complex but I am not able to. Indeed, one option is that Q2 is separate for each district and doesn’t use a complicated cascading multiple choice filter question. But that means that later in the questionnaire, I would have to repeat ALL teh questions with refereance to the crops for each district. WHich is a nightmare as teh questionnaire will become very long and confusing. I would really prefer to be able to combine all that into a single formula for ${crop1} and ${crop2} so that I only use these 2 variables for any district.

4. Upload any forms or screenshots you can share publicly below.

HDDAP baseline - draft farmers questionnaire - test.xlsx (20.6 KB)

2

Hello @Julie

Welcome to ODK Community, when you get a chance, please Introduce yourself here!

To get labels of selected items (crops) in select_multiple you can use jr:choice-name() in combination with selected-at() since more items can be selected. The later function allows you to point to a desired index for label retrieval. However, I noticed your choices uses text labels, in which case selected-at() alone, might suffice to retrieve the label. I used the combo, should you use pseudo name (i.e.: numbers and short codes) in your choices.

As for controlling relevant based on number of selected items, you can use count-selected().

I made the changes in your HDDAP baseline - draft farmers questionnaire - test.xlsx, test it out and let me know if it worked for you.

Happy ODKing

Best,

Jules R

1 Like
3

Oh wow, it works!!! Thanks a million Jules. I spent 2 days not knowing from where to start and trying many different ways which all failed!

Have a great day!

Julie

1 Like