Recombining answer choices in xls form

Hello,

I am needing to recombine answer choices in my ODK xls form, and I'm struggling to figure out how to do it (this is for property grabbing work in east Africa). Here is the example and the answer choices (as outlined in the "name" column" of my choices tab - they are numbered on the screen for the enumerator):

504.1 What means did the offender(s) use to attempt to take the homestead? Select all that apply.

murder_widow
attempted_murder_widow
assault_widow
sexual_assault_widow
threatening_violence_widow
bewitching_cursing_widow
social_ostracization_widow
assault_dependent
threatening_violence_dependent
murder_dependent
attempted_murder_dependent
bewitching_cursing_dependent
arson
malicious_damage_property
fraud_deception
criminal_trespass
other
dont_know
no_response

If they select "murder_widow" or "attempted_murder_widow" or "murder_dependent" or "attempted_murder_dependent" I would like this to recombine into another answer choice on the backend called "murder & attempted murder". If they select "arson" or "malicious_damage_property", I would like this to be called "damage of property". There are more, but you get the point.

Do I create a "calculate" question type or can I put the formula in the "calculation" column? I cannot figure out the formula. Please guide, if you can. I need to do this for a series of questions, where I would like the backend work to go ahead and recombine, but I still want the original answer choices for documentation.

Thanks for any pointers,
Andee

Here is a demo form that I think should give you good ideas. That form has a list of species that can fit into different categories: invasive, endangered, plant, animal. It shows two ways to go from a list of options selected by a user to whether any select species fit the category.

The first way is probably what you had in mind -- the calculates explicitly match against selected options that would fit the category.

The second way is more dynamic -- I've added some category columns to the choices and use those to compute the categories. I'm happy to go into more details around how these expressions work or you can use them as a pattern for your use case. The most interesting part is probably the one in square brackets. For example:

selected(${sighting}, name) and animal = '1'

This is filtering the list of choices. The selected(${sighting}, name) part means only include choices with name values in the sighting field meaning they were selected by the user. And the second part further filters down to only include selected choices for which there is a 1 in the animal column.

Then we can count how many choices meet those criteria. If there's more than 0, that means the selection fits the category (there was at least 1 animal selected).

2 Likes