How to create a lookup using pull data functionality

[york_rff]

Hi there

I am quite sure that it is a standard solution what I am looking for, but somehow I can’t find any thread on this.

I have a number of schools and two or three of them build learning groups. So, after entering the school name, the learning group name should be shown for information.

“Pull data” seems to require external sources, but I want to have the lists in the same xls-form.

What I have tried is:

• SURVEY = type: note / name: learningroup / label: Learning Group: / choice_list: lg_id=school_name (with school_name having been selected: school_llcl1)
• CHOICES = list_name: schools / name: school_llcl1 / label: A School / lg_id: NA.ER.AR.001 (which is would should arrive in ‘learninggroup’)

How can I find an example on a similar solution?

[Kal_Lam]

Could you kindly share the community a sample of the xlsform and the goal you with to achieve. The community could help you out if it’s doable in KoBoToolbox.

[york_rff]

The purpose is to…
…select a province
…then select a district from this province
…then select a school from this district
…then see the ID of the learning group which the school belongs to.

I included the simplified Survey and Choices tabs NOT as a table, because it looked like that I can’t copy in data in table format. But after sending a copied table off, it looks well, so here it is:

SURVEY

type	name	label	choice_filter	choice_list
select_one provinces	province	Province:
select_one districts	district	District:	provincefilter=${province}
select_one schools	school_name	Name of school:	districtfilter=${district}
note	learninggroup	Learning group:		lg_id=${school_name}

CHOICES

list_name	name	label	provincefilter	districtfilter	lg_id
provinces	NA.ER	Erongo ER
provinces	NA.HA	Hardap HA
districts	NA.ER.AR	Arandis	NA.ER
districts	NA.ER.DA	Dâures	NA.ER
districts	NA.HA.AR	Aranos	NA.HA
districts	NA.HA.DW	Daweb	NA.HA
schools	school_llcl1	Arandis Primary School		NA.ER.AR	NA.ER.AR.001
schools	school_llcl2	U. B. Dax Primary School		NA.ER.AR	NA.ER.AR.001
schools	school_llcl3	Ebenhaeser Primary School		NA.ER.AR	NA.ER.AR.001
schools	school_llcl4	Elifas #Goseb Primary School		NA.ER.AR	NA.ER.AR.002
schools	school_llcl5	Another Primary School		NA.ER.AR	NA.ER.AR.002

[Kal_Lam]

The community would appreciate if you could upload and xlsform. FYI, you should be able to upload them. Refresh your page and try uploading.

As a backup you could also have a look on how to design a cascading select question by following the post that has some similar workarounds:

Example 1:

Weird behaviour in cascading select

Hi @sumankhanal, Welcome to the community! Seems like you have some coding error within your xlsform. I tried at my end with the information you provided and i got it correct. For details, please see the images below: In the survey tab of your xlsform: [Image 1] In the choices tab of your xlsform: Please note, you may not see all the choices in the image as i have hidden it to save space. But you should find all the choices in the reference xlsform shared at the end of this post. [Image 2] …

Example 2:

How to create a multi layer cascading select question?

Hi @PALASH9698, Please see the revised/updated cascading selects based on your added information. In the survey tab of the xlsform: [Image 1] In the chocies tab of the xlsform: [Image 2] Data entry screen as seen in Enketo: [Image 3] Reference xlsform: Palash Selected Villages.xlsx (18.0 KB) Have a great day!

As a backup you could also have a look at our support article Adding Cascading Select Questions.

[york_rff]

The only “upload” option I find in the icons at the top (quote, bold, emphasis, etc.). If I select “upload” there, there is an error message denying uploads for new users…

[Kal_Lam]

Would you mind refreshing and giving a try once more… it should work!

[york_rff]

Cascade select is not my problem. This is working well with province/district/school.

(Sorry, I just see that “cascading select” somehow went into the title of this thread. I thought I had written something like: Lookup / choice_list)

The problem is rather that learning groups are logically between district and school (a district has a number of learning groups and a learning group includes a number of schools), but I don’t want to ask to select the learning group BEFORE the school – but the other way round: after selecting the district, the school name should be selected (which works well), and THEN the respective learning group should be calculated/shown. Any idea?

[york_rff]

delete.xlsx (18.1 KB)

[Kal_Lam]

OK, try sharing the conditions of your learning group. Maybe the community could have a look at it and see if it’s doable!

[york_rff]

Conditions = ?

[york_rff]

I am not sure, but you seem to suggest to start over with a new post (and abandon this thread, as it has become confusing, due to the wrong title). Let me try.

[york_rff]

I have four levels: provinces, districts, learning clusters, schools. Three of them are part of a cascading select series of questions: province, district, school.

While learning clusters are logically between district and school (a district has a number of learning clusters and a learning cluster includes a number of schools), I don’t want to ask to select the learning cluster BEFORE the school – but the other way round: After selecting the district, the school name should be selected (which works well), and THEN the respective learning group should be calculated/shown.

Here is a simplified example of what I tried by using a choice_list, but it doesn’t work.

delete.xlsx (16.1 KB)

[Kal_Lam]

It’s OK here …

[york_rff]

I tried to achieve the same WITHOUT external file, but couldn’t find any idea how to do this (if you have, please respond to my other post re cascading select). Therefore I resorted to pulldata, but somehow it doesn’t work – even though I have found a tutorial which describes exactly what I am trying here: https://www.youtube.com/watch?v=-Hi_K1rWxCo&t=36s

I have a number of schools, of which always two or three belong to a cluster. After the school name has been selected, the cluster name (or cluster ID) should be presented just for information.

Here is what I tried, but while school name is shown, the next step just says: “Learning group is” (so the cluster doesn’t seem to be calculated properly).

SURVEY tab
type name label calculation
select_one schools school_name Name of school:
calculate clustercalc pulldata(‘odktestschoollist’,‘cluster’,‘fac_name’,${school_name})
note test_note School Name is ${school_name}
note lg_note Learning group is ${clustercalc}

CHOICES tab
list_name name label
schools school_llcl1 Arandis Primary School
schools school_llcl2 U. B. Dax Primary School
schools school_llcl3 Ebenhaeser Primary School
schools school_llcl4 Elifas #Goseb Primary School
schools school_llcl5 Erongosig Primary School

CSV file content (file name: odktestschoollist.csv)
fac_name cluster
school_llcl1 NA.ER.AR.001
school_llcl2 NA.ER.AR.001
school_llcl3 NA.ER.KA.001
school_llcl4 NA.ER.KA.001
school_llcl5 NA.ER.KA.002
centre_llcl1 NA.ER.AR.001

delete.xlsx (11.5 KB)

[Kal_Lam]

Could you kindly explain this more clearly …

@york_rff

The problem is rather that learning groups are logically between district and school (a district has a number of learning groups and a learning group includes a number of schools), but I don’t want to ask to select the learning group BEFORE the school – but the other way round: after selecting the district, the school name should be selected (which works well), and THEN the respective learning group should be calculated/shown. Any idea?

Maybe the community could help you out if this statement is understandable.

[york_rff]

OK, new try of summarizing / setting a new focus:

I tried to achieve the following WITHOUT external file, but couldn’t find some kind of a lookup approach (if you have an idea, please let me know). Therefore I resorted to pulldata, but somehow it doesn’t work – even though I have found a tutorial which describes exactly what I am trying here: https://www.youtube.com/watch?v=-Hi_K1rWxCo&t=36s (I am using the kobotoolbox app and server).

Objective: I have four levels, namely provinces, districts, learning clusters, schools. Three of them are part of a cascading select series of questions: province, district, school. While learning clusters are logically between district and school (i.e. a district has a number of learning clusters and a learning cluster includes a number of schools), I don’t want to ask to select the learning cluster BEFORE the school – but the other way round: After selecting the district, the school name should be selected (which works well), and THEN the respective learning group should be calculated/shown.
With other words: I have a number of schools, of which always two or three belong to a cluster. After the school name has been selected, the respective cluster name (or cluster ID) should be presented just for information.

Here is what I tried, but while school name is shown, the next step just says: “Learning group is” (so the cluster doesn’t seem to be calculated properly).

SURVEY tab
type name label calculation
select_one schools school_name Name of school:
calculate clustercalc pulldata(‘odktestschoollist’,‘cluster’,‘fac_name’,${school_name})
note test_note School Name is ${school_name}
note lg_note Learning group is ${clustercalc}

CHOICES tab
list_name name label
schools school_llcl1 Arandis Primary School
schools school_llcl2 U. B. Dax Primary School
schools school_llcl3 Ebenhaeser Primary School
schools school_llcl4 Elifas #Goseb Primary School
schools school_llcl5 Erongosig Primary School

CSV file (name: odktestschoollist.csv)
fac_name cluster
school_llcl1 NA.ER.AR.001
school_llcl2 NA.ER.AR.002
school_llcl3 NA.ER.KA.001
school_llcl4 NA.ER.KA.001
school_llcl5 NA.ER.KA.002
centre_llcl1 NA.ER.AR.001
centre_llcl2 NA.ER.AR.001
centre_llcl3 NA.ER.AR.002
centre_llcl4 NA.ER.AR.002
centre_llcl5 NA.ER.KA.001
centre_llcl6 NA.ER.KA.001
centre_llcl7 NA.ER.KA.002

Lookup_test.xlsx (14.6 KB) odktestschoollist.csv (342 Bytes)
Any idea what is wrong? Or even an idea to do a lookup WITHIN the choices tab?

[stephanealoo]

Hi @york_rff
I noticed that your issue was actually with your csv file. The data in it was not being read as two columns because you had incorporated a semi-colon as a seperator.

My approach is to first create the CSV file in excel with each data in its column and then save it as a CSV file. Normally you can notice a wrong placement when you open the CSV file in excel and data fails to sit within its columns (as what you had in the previous image)

See the list I used odktestschoollist.csv (342 Bytes)

Please note that your pull data question is a calculate question but you can actually use a text question in it as I did in my form

Running this the behaviour looks as follows

Wishing you all the best in finalizing your form.

Stephane

[york_rff]

I don’t believe this! How embarrassing! As if it would be the first time that I created csv files!? The only explanation I have is that my computer was reset and excel was upgraded to version 2016. It would have taken long or forever that I would have checked the “comma thing” – but indeed, when I open the csv-file with the NotePad, I do see semicola (what I can’t see in excel), independently whether I save as “csv (comma delimited)” or as “csv MS-DOS”.

Thanks so much for pointing this out. Maybe this hint can save a lot of people in the community from banging their heads against the wall…

Thank you very much, too, for the additional design ideas. Much appreciated.

[york_rff]

Just to give it a chance: Am I right that lookup (like with pulldata) is possible only with external files, but not with content on the choices tab?

[stephanealoo]

Hi,

@york_rff

Am I right that lookup (like with pulldata) is possible only with external files, but not with content on the choices tab?

You are right, pulldata only works with external files. If you needed to get a similar effect within the choices tab, then you can wittily play with cascade select which lands you to the same (not good for large choice options)