Cascading select many rows choice

Hello everyone,

Sorry because this topic recieve question but it seems that none respond to my situation…
I want to be able to use a cascading select from external files: the 4 administrative levels of 4 countries… you imagine there is quite a few lines!

I tried the option select_one_external available here:
http://xlsform.org/en/#external-selects
It says in the app that itemsets.csv is missing

Apparently, select_one_external is not supported by Kobo.

I tried the search fonction and manage to make it work but cannot make it cascading. I saw the possibility to add properties to search here:
http://web.archive.org/web/20170710045637/https://opendatakit.org/help/form-design/data-preloading/

Is it working the match o column to filter in Kobo?

I also tried the options select_one_from_file with the pre-loaded csv, but i cannot find how to make it work in a cascading way.

Is there any example on how to make it work? I don’t know XML i always worked with XLS Form and Kobo…

Thanks a lot!
Quentin

1 Like

Yyeeeaaaahhh i managed to make it work!
Had to leave, will upload the document later.

I used 5 levels
Type:
select_one_from_file csv_country.csv
select_one_from_file csv_region.csv
select_one_from_file csv_district.csv
select_one_from_file csv_community.csv
select_one_from_file csv_healthpost.csv

Name
pays
reg
dis
comm
stru

Choice-level
pays -> (Nothing for country)
reg -> pays=${pays}
dis -> reg=${reg}
comm -> dis=${dis}
stru -> comm=${comm}

With therefore 5 csv preloaded in UTF8 with first cell:
list_name,name,label,pays,reg,dis,comm

I used the column as choice filter as usual, only and i don’t know why i had to put only one list_name per level, therefore one csv per lecel. Not a big deal i upload the 5 csv not just one but its the same

Quite happy!
I’ll upload the files on my way back i wrote on my phone so i may have put a - instead of a _

Ciaooo

3 Likes

Hello everyone,

I have to deploy a survey in 4 countries and locate until a 5th administrative level:
Country, region, district, community, health post

The cascading select through choice-filter and the normal choice page in cascade doesnt work due to the amount of rows to process.

I have been looking around and found different options but none seem to work with KoboCollect o OdkCollect:

Search in appearance works but doesn’t allow to filter. The option of the search function like ‘startwith’ or ‘endwith’, ‘match’ or even better column-to-filter would do the trick but do they work? It doesn’t seem to me that they work

Select_one_external is not aupported by Kobo apparently

Pulldata allow for pulling a value in a calculate type, not displaying choices of answers in a select_one as far as i understood

Select_one_from_file works when having one pre-loaded csv per level of cascade (5in my case) but only through enketo, not through the mobile apps

Please can someone helps me by telling me either to forget about it and just build as many survey as needed for the app to work or by giving me some examples of success or tricks?

Thanks a lot everyone!
Quentin

Hi @Quentin,
I would like to recommend a different structural approach to sorting out this issue.

  1. I assume that your region levels are manageable. So in this case you would need to have a region based skip which would allow you to divide the subsequent divisions into different questions which can be routed with a skip routine
  1. By tricking the app that each list is different, you avoid the processing of a large list. I have done so by ensuring that the listing for each region is different. So you do not need external select but keep all the responses in the choices sheet…
  2. You will create your cascade starting from the District not from the region, see example below.

Change your regions, districts, community and health posts appropriately. This should work out your issue. Please note if the Regions are still too big, think about the same logic but start the cascade at the Community.

Hope this works out for you.
Stephane

1 Like

Thanks Stephanealoo, i will try doing this
I’ll think about creating a vba code that would allow to do so automatically if i manage to find time i’ll share it over here
However, the great thing about external data is that these data can be updated while the form is deployed… i never tried but i think we cannot update a form while its deployed even if its just at the choice page level right?
If its true this solution is definitly not optimal because names of villages and health center change through time in such a big scale.

Hi @Quentin,
Hope you are doing well. I tried your approach but am getting the error message:

Unable to deploy form:
The same instance id will be generated for different external instance source URIs. Please check the form. Instance name: ‘project’, Existing type: ‘file’, Existing URI: ‘jr://file-csv/project.csv’, Duplicate type: ‘choice’, Duplicate URI: ‘None’, Duplicate context: ‘survey’.

2

Could you guide me here?

Thanks!
Varun

Helo Varun,

Could you share your csv or a template with the first lines so we can check if there is an issue there?
Be sure to follow the example given with the fruits here to use the select_one_from_file

1 Like

@Quentin Hi, thanks for the prompt response.
Sharing a snapshot of the first few lines of the first csv form (districts)
3

Sharing a snapshot of the first few lines of the first csv form (projects) - this is where i need the cascade. A project is a subset of district and must be filtered based on selection of a district
4

Hi @Quentin Could you help?
We are doing an urgent survey as a part of the state governments initiative for getting a better grip on COVID
Would really appreciate it if you show me how to get the cascading select with uploading external csv files, as described in the messages above

Sorry you know as things go in this sector we all are running late in our urgent tasks

I still don’t really see the csv you are refering to that should be in csv format UTF8 separated with coma that you call on you select_one_from_file function…

However have you tried doing it the simple way with your cascading options directly in the choice page with filtered by choice:filter columns?

In the survey project where you need your cascade, you have 2 questions one is select_one district named district
the other other one select_one project with as a value in column choice_filter igual to district = ${district}

and in your choice page you have under list name the two lists project and district with the slight difference that there is a third column called district where the value for each project should be the district

1 Like

Hi @Quentin

I would like to suggest that you look at using select_one_external as explained in the article below. XLSForm Docs

Alternatively, I would recommend the following topic which had been discussed on the ODK forum

Stephane

1 Like

Thanks Stephanealoo,

Always of great help.
Unfortunatly i still didn’t manage to make it work… for the moment we have splitted surveys by regions and aggregate de data after but i don’t really like this option.
I tried the files from the post in ODK forum and i didn’t succeed in making them work (without changing anything)…

For the moment i still recomand simple cascading questions and splitting surveys when it gets too heavy

Any functioning examples are greatly welcomed :slight_smile:

Hi @Quentin
I am not promising that I will work on this immediately, could you however send me the form in the XLS version and the list as a separate excel. I will then try and look at it to see which scenarios would work. Kindly do so on a private message if you have sensitive information on the survey.

Stephane

1 Like

Dear @Quentin,
Really appreciate your replies to my problem.
I have attached both the csv files as well as the xls file
district.csv (1.0 KB) project.csv (20.7 KB) urban_trial_1.xlsx (10.4 KB)

Note: Currently there are only 2 levels with around 500 rows. However the form i need to build has 100,000 rows and 4 levels, with the top level being a district, and there are 34 districts in the state.
Splitting it into 34 forms wouldnt make sense either because i would have around 3000 rows in each form.

Pulling from an external file makes the most sense, but i am unable to do so

Looking forward to your guidance here, and sorry for the trouble
Varun

Hi Varun,

Can I check if the problem remains unsolved? At CartONG we are providing an IM helpline for projects which are using data to assist the response to the coronavirus pandemic.
https://www.cartong.org/cartong-im-covid-19-help-center

4 levels and CSV files not a problem. 100,000 rows we may need to do a little testing on to identify the exact performance limit and advise on the best way to manage that aspect. If this issue is still blocking you, do please let me know as soon as possible and hopefully we can help you get your form working.

Best regards,

Noel

1 Like

Hi Noel, yes the problem is still pending

Would love your help!

Happy to help. Would you mind filling out this short form and I will contact you directly to your email address? No need to repeat the details of your technical problem as its already well understood so you can just write “already explained” if there’s any questions about that. If not working for an NGO you can just put the name of your employer/organisation, its just for our reporting needs and then I can contact you directly and see how we can move forward.

https://cartong.org/content/coaching-hotline-requests-im-covid-19-help-center

1 Like

Hi, just wanted to share back with the community the relevant checks to do when using select_one_external and a single CSV file rather than multiple files.

  • With select_one_external the CSV file must be named “itemsets.csv”, other names are not possible
  • For reasons that I dont fully understand I have found that sometimes a form like this will be rejected if Labels, Hints etc do not all specify a language (eg. Label::English), even if there is only one language. Adding this helped avoid a server500 error.
  • When there are multiple cascading layers the top layer goes in the choices tab of your xlsform and all other layers are included in the CSV file.
  • System does not seem to have significant performance issues even with 100,000 rows
4 Likes

@Quentin, @Varun, kindly please be informed that you could manage your survey project through select_one_external question type as discussed in our previous post: