Creating a form with cascading select, from a list in an external csv file

Hi everyone,
This is my first post here, I have been reading a lot of posts so far and recognize the efforts taken by the community to support one another.
As a result of the lockdown enforced because of COVID-19, in India agro supply chains are disrupted and livelihoods are lost. As a COVID response, a state government is looking to map commodities farmed by thousands of women across the state, and help them sell their commodities.

For this a cascading select from district to village is essential. However as the number of villages are around 10,000, I have read that referencing lists from external pre-loaded csv files would prevent long loading times for the form, and have followed the process documented by Micheal in (Select One or Many From External File Question Type — KoBoToolbox documentation)

I have also followed the process to create cascading choices as described by Tino (Adding Cascading Select Questions — KoBoToolbox documentation) and placed it in a csv file called district_village_mapping.csv

However, I get the error after deploying the form and loading the csv file : Error creating row: [Error: type select_one_from_file not found]

This is my survey sheet:

My choices sheet have:
First row as (list name, name, label)
Second row as (district, name, label)
Third row as (cmrc, name, label)
Fourth row as (village, name, label)

Could someone please have a look?Help required urgently, as we require this to be deployed as soon as possible
Also I was not able to upload more than one image, as I am a new user. Sorry about this!

Thanks and regards,
Varun

Hi @Varun

I’ve been working on a similar problem for some of our projects too and managed to crack it a week or two ago. I’ve attached the very basics here of what I was using to combine cascading selects to filter a search from an external CSV.

I’m not 100% sure it will solve your issue, but it might give you some ideas of how it could work.

Major downside is that it ONLY seems to work in Enketo (web forms) for now, but there might be a way to make it work using ODK app. I haven’t been able to test that out yet. Other minor issues is that it doesn’t work with ‘preview’ version of the form and you can’t use the KoBo ‘Reports’ function either - you have to do a ‘Legacy’ version export of the data or pull it into some other data viz software.

Hope this is of some help to you and others looking at this issue in challenging circumstances!

Chris

EXAMPLE - Selects with Choice Filter Search with from_file.xlsx (41.2 KB) id_names.csv (8.8 KB)

4 Likes

Hi @Varun,

Welcome to the community!

Hi @LCDI,

Welcome to the community! Thank you for the wonderful response which should help the entire community! Expecting the same in the upcoming days as well!

Have a great day!

@LCDI have you had any luck getting this to work on the app? I’m having the same issue where it works on enketo but not on Collect. Thank you!

@SNB, do you mean the select_one_from_file question type works with Enekto but not with Collect? Kindly please be informed that the Collect android app should also support select_one_from_file question types. Try using the latest versions of the Collect android app to get this working.

Hi @Kal_Lam , I meant setting up choice_filter in an external csv that’s getting pulled into my main survey.

@SNB, what is the question type you are using for this?

Getting error while using your given files

Welcome to the community, @vishwadeep! Please be informed that this is a bug with the form builder (as the same currently is only supported through the xlsform). You could also follow the GitHub issue for the same here:

Hi Kalyan,

Acctually my issue is that I used Pull data and search,but both scripts are getting error in Kobo toolbox

Firstly I describe my issue…
I need create a cascade (lookup list) of Country, State and Villages. The list is huge (over 1 lakh rows) in that .csv file.
So i tried Pulldata and search commands in my xls file but as I upload the files to kobotoolbox it start showing this error.
what should I do?

1 Like

Hi @vishwadeep
I would like to recommend a workaround
Using search () in the appearance column.

NOTE:

  1. This method will only work on the android collect application. The original writeup can be found here (XLSForm Docs).

  2. This method does not need you to execute choice_filter for cascade questions since this is done using one of the search functions i.e., matches (see below)

Step 1 : In the XLSForm, within the survey sheet, specify select_one listname or select_multiple listname in the type column (where listname is the name of your choice list) as you would typically do when defining a select_one or select_multiple respectively.

image

Step 2 : On the appearance column (survey sheet), define the search function as you would desire.

There are five ways you can use the search function:

  • contains - this will check and return all the items in the CSV column you need but only for rows which the filtration column contains the specified parameter within any part of the text in the filtration column.
  • beginswith - this will check and return all the items in the CSV column you need but only for rows which the filtration column beginswith with the specified parameter.
  • endswith - this will check and return all the items in the CSV column you need but only for rows which the filtration column endswith with the specified parameter.
  • matches - this will check and return all the items in the CSV column you need but only for rows in which the filtration column matches the specified parameter in full to the specified parameter.
  • You can also use search without any of the four elements; in this case, you will have the entire list without any filtration applied.

Please note for this example; we are using the “matches” approach and the search without any additional function.

You will define your search in the column as follows:

search (‘nameofcsv’, ‘matches’, ‘filtercolumnincsv’, ${questionnameinxlsform})

In this example, we are using a CSV file known as countriesbyregions to get various lists.

image

To understand the example, see the structure of the used CSV file.

image

If you need to use any other appearance style such as minimal, you will need to define it before the search appearance followed by a space e.g., minimal search () or minimal autocomplete search ()

image

Step 3: On the choices sheet of your XLSForm, you will need to define how your list will be built from the csv file:

  • list_name column: specify the name of your choice list as you normally would.
  • name column: include the name of the .csv column to use for uniquely identifying selected choices.
  • label column: include the name of the .csv column to use for labelling the choices.
    Note: If you wish to include multiple columns in the labels, include a comma-separated list of all columns to include. The name column will be dynamically populated based on the column name you put there, and the label column will be dynamically populated based on the column name(s) you put there.
  • In your choices worksheet row, you may also include a .csv column name in the image column. If you do, the image filename to use will be pulled from the specified .csv column.
    Note: If you refer to image files in this way, you must always upload those image files as media file attachments when you upload your form to the server.

image

Using our example, the definition in the choices sheets will be as follows (look at our earlier CSV)
image

Note:
Note:

  • The contents of the columns within the CSV that will provide the contents of the name must meet all the requirements of creating a name in the XLSForm choices sheet.
  • If you need to pull the label for multiple languages, you can still specify the column that provides them using the same approach above.

Step 4: Upload and deploy your XLSForm as usual. Then upload the CSV within the media section as shown below.

image

Stephane

1 Like

Hi @stephanealoo
I am not getting the sorted list from the CSV file which I uploaded as per the instruction you provided.
I am attaching my both the XLSX and CSV file. Can you please correct me where I am doing wrong?
Cascade111.csv (9.2 MB)
Project2.xlsx (12.7 KB)

Hi @vishwadeep
Let me post my own working files so that you look at them to identify any issue on yours.

Select One and Multiple from CSV.xlsx (12.2 KB)
countriesbyregion.csv (36.9 KB)

Stephane

1 Like

@stephanealoo
I really don’t know what is happening. I try to use the file you just uploaded. But it cannot be able to fetch data from CSV