Cascading select with csv source

1. What is the problem? Be very detailed.

We've deployed forms to follow up agricultural practices.
Each producer has a unique ID. (over 15 000 producers referenced).
unfortunately, unique IDs are not calculated all the same way.
Our collectors have difficulties to identify those IDs on field to collect data.
I've been trying to set up a form to lookup IDs retrieved from a CSV with 6 columns.
Country, Groupement, village, Name, Surname, ID.

I'd like to make a form that allows to narrow the search until it proposes the producer IDs, by filtering each column 1 by 1.

  1. select one country (from list of 2 or 3 countries, that's not a problem and holds well in a choice list.
  2. select one groupement ( filtered from the CSV matching country previously selected, with autocomplete and search (data, 'matches, 'country', ${search_country}) fonction, this is working fine
  3. select one village (filtered from the CSV matching the groupement previously selected, also working fine with autocomplete and search(data,'matches, 'groupement',${search_groupement}) fonction in appearence
  4. select one name, (also filtered from CSV with search() fonction using 2 filters arguments. This works also keeping the groupement and village values.
  5. select one surname

I need to list only the names corresponding with the groupement, village, and names previously found.
the Search(...) fonction doesn't allow to dig further than 2 levels of columns filter.
How can I handle that ? (I might have several village with same name in different groupements or countries, or several producer with same name, surname etc ...)

  1. choose from available ID's matching the exact values, from question 1 to 5.

As well, with only 2 levels of columns filters I won't find the only one or 2 IDs that are matching this criterias.

Using choice filters would do the job, but I couldn't find a way to filter from a side loaded external CSV.

The database IDs may be often updated such as maintaining a specific choice sheet in the form is not an option even the external_choice sheet that needs a different layout would be hard to maintain in Excel.

2. What app or server are you using and on what device and operating system? Include version numbers.

SMAP server as it allow to automatically side load csv from other forms retrieving only the needed columns in a seperate CSV. as well as user accountability and right management.

3. What you have you tried to fix the problem?

combining choice_filter and search fonction would be great but seems choice_filter won't load side csv as search() fonction does.

4. What steps can we take to reproduce the problem?

try to make a row search fonction based on more than 2 criterias.
each criteria being in a column of a CSV.

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.

I don't understand why cascading select has been done the way it needs an extra column to set the available filters. This is definitely not the way data are generally presented in a simple table.
this makes extra work needed to set the data in the form, this makes double the data in the same document ending in extra precious bytes to be transmitted on challenging network environnement...

why are the columns headers set in rows in choices ?

using external_choices sheet generates itemsets.csv making the "external" not so external, as you can't choose the file's name neither the way data will be presented in that file. this is well handle with search fonction, but limited to 2 args...

I'll be glad to hear about the right approach for this common concern !

Thank for the great tool that ODK is !

Hi @xtra121

I would recommend you use a coding system for the different geographical levels. Codes would be used to duplicate cases in which sub-localities share the same name even though they are in different areas.

A way to do this would be to use a unique code for each distinct element of level 1(country in your case), e.g 2 digits. Then have a code for level 2 made as level 1 _ code+ 3 digits. With the 3 digit code unique for each different record within the level, to avoid duplications. And so on.

You would choose the number of digits based on the largest number of different cases in each level (e.g. <10 would be 1 digit, <100 2 digits, etc.).

The data match function should then work smoothly as you would only need to filter based on the previous level code.

I hope this helps!

Best,
Andrea

1 Like