Cascade select. Show an additional choice option for a particular group along with the other default choice

1. What is the issue? Please be detailed.
I want to implement a choice_filter. Sincere apologies if such a query has already been answered. I cannot find it.
I have three regions, Ra, Rb, and Rc. Each region has one normal school (Sn), and Rc has an additional special school (Ss). I want to show the schools in choice based on the region.
If Ra or Rb is selected, Sn (the only choice) should appear in the choice in the next question. If Rc is selected, both Sn and Ss should appear in the choices.

I can implement separate list_names for the regions and use ${region}='...' in the relevant, but this creates two columns in the output data. I want a single column.

This appears to be simple. I played around with the simple and cascading select, but couldn't make it work.
list_name name region
schools Sn Ra
schools Sn Rb <-- duplicate names, won't work
schools Sn Rc <-- won't work
schools Ss Rc

This post seems relevant:

Ciao @shashpal,

Happy New Year. Since the school and regions have a many-to-many relationship, you could achieve the expected result by applying the following:

  • in the choice_filter for ${school} put if(selected(${region},’Rc’), selected(“Sn Ss”, name), selected(“Sn”, name))
  • name in the example above is the name column from choices sheet while the value in the first parameter of the selected(,) function is custom array (quoted values separated by space) that wraps the values of regions as desired in your questions description
  • you won’t be needing the filter column in your choices
  • If you have a longer list of choices with many-to-many relation to regions, consider using external files (csv) where functions like pulldata() or search() database alike queries with multiple layers of filtering.

I tested choice_filter() method described above and it worked for me, hoping it does for you as well.

Best regards,
Jules R

| |


Dear @jules_rugwiro,
Thanks a lot. This solution is perfect for my case.

1 Like