This video was really helpful in demonstrating how to access and extract external data in XLSForm, Kobo Toolbox, and ODK using the 'pulldata' command. The step-by-step instructions were simple to follow, and I like how the syntax for the "pulldata" function was explained in a straightforward and succinct manner. It was really helpful to learn how to use "pulldata" in a practical survey application from the examples given in the video. I feel very confident using "pulldata" in my own surveys now thanks to this video, and I strongly suggest it to anyone wishing to access and extract external data for their XLSForm surveys.
Wow - what a great summary and thanks so much for taking the time to leave this encouragement! Sorry I missed it the day it came in, but just wanted to say thanks!
Wow! Thanks for taking the time to let me know! If you want to learn more about how to EASILY get started with mobile data collection join my FREE Course now! ► humanitariandatasolutions.thinkific.com/courses/7steps
Quick question. It looks like you populated the .csv file listing Family ID and province offline? Is there a method for using one form to "register" a participant (or family) and then using the data from that register as a source to pull data from? I'm trying to design the end to end solution for field technicians from intake to follow up where baseline and follow up data is tracked by one beneficiary ID (if that makes sense?)
Hi! Great question! This is a common query. However, there is no "two-way sync" of data from one form up to a cloud "database" that then another form can pull data from. Therefore, if you want to match data up between two different forms, that must be done offline, as in my example. It isn't an "elegant" solution, agreed. If you want that functionality, you might want to check out ODK-X, CommCare, or Dharma (BAO Systems). Essentially, you want to look for a software that has "case management" functionality, or two - way syncing. If you're using an ODK-based solution like the one in this video, then essentially you need to do the data-linking behind the scenes, using other software. This can still ask be automated, etc, but wouldn't be managed within KoboToolbox, for example.
@@HumanitarianDataSolutions Thank you so much for this reply. You've saved me a lot of frustrating searching. I think what we need is actually case management. best to you...
I surely I enjoy learning Kobo tool box through this channel. Thanks. Today I have a question. I have a created a survey. I want to administer to subjects of my choice over and over. How should I link data of the same subject collected over different periods. I tried following the pull data video but I still need your help.
Hi Janna, In CSV file created unique id's, if I created drop-down list for that id's so my read only question can't filled as per CSV file they are just blank. Kindly let me know how to fix this issue.
Please tell me, Pulldata is not work with select_one value?? If I choice 'A' from select_one and then I want to show what is corespondation value with 'A' on csv?
Thanks for your brief tutorial. What if the there are more than one results returned based on the search? it only shows the first record it matches. Is there a way to display all matching results?
Great question - in order to use pulldata, you need to have a column with a unique index key - so there's only one match per lookup. So this wouldn't work for multiple matches. Can you explain your use-case a little bit more? Maybe there's a solution we can help you find!
I am trying very hard to get this to work and just can't seem to do it. I have replicated exactly what you show and just can't get the 'province' to show up as you describe. I am anxious to figure this out for an upcoming project we have. Is there a chance you can help me sort this out?
Hi Chrystine, (I love the spelling of your name by the way, really unique!) Thanks for your question! I've seen your email, so we can follow up further there! are you able to share your xlsform by email with me, and I can take a look?
Thank you very much for the video. Can we set a numeric answer of a question to be shown in somewhere else in the form as a default answer in number format question but not as a note.
Hi Janna, this video is great, thanks a lot! I just had an issue with the preview showing the pulled data, but it not showing on the tablets - do you have any advice how to solve that? thanks!
Hi, thanks for your videos. I have a query, I am using select one to send data from a csv file to kobo and it throws an error ... it says that it does not get the file ... I load it from media ... and it looks ... but when I go I don't see it and I can't edit anything. Thank you very much in advance
Hi thanks for the tip. What if the you wanted to shows the pull data for a preceeding select one question and now you want to pull predefined categories using the pull data option.... e,g. Select health facility , then once selected it takes to a second page which pulled data show the type of the previous choice as a dispensary, health centres, clinic etccc. Thanks
Hi Janna, I do the same, but "the familyprovince" does not appear on ODK (Kobocollect) mobile app. it only works on Enketo (web form). How to solve this problem?
Hello. Thank you very much for this video. It really saved me. I just have one critical problem though, I can't seem to be able to see the pulled data when using Kobo-collect. I want my fieldworkers to be able to see the prepopulated data for each question and verify this with participants. Any idea how I can solve this?
Hi Diana, thanks for this great tutorial. Is it possible to perform updates in the uploaded csv file i.e. by continually adding extra column data using the client side data collection? Example: You are going to visit the each family every month and just want to capture the date of visit
Hi Bill, great question. You can't update the csv file in real time, for example, based on the answers you're collecting through your questionnaire. However, you can update the csv file manually, add in columns, add rows, change data, etc, and upload it to Kobo Toolbox. The only thing you need to be careful of is that your data collectors then need to re-download the form and new csv file to their phone again.
Thank you very much for the tutorials. I needed some help, I want to create an online booking form using kobo toolbox but I am unable to do so. I want when one gets as a seat, that particular seat to be unavailable to other persons when applying. I want to allocate seats from 1 to 100. Thanks
Hi! Great question! I don't think this is possible with KoboToolbox simply because it is a one-way data collection system, so it doesn't feedback to the form information that has already been submitted. Pulldata is a kind of workaround within KoboToolbox, so you could possibly update the external datasets once or twice a day, and see if you can get close to 100. Let me know if you'd like to brainstorm further!
I was able to put in all the pulldata calculations and uploaded the csv file but I am getting errors while trying to redeploy the xls error message "unexpected KoBoCAT error 400:b'{"detail":"There has been a problem trying to replace ${\'token_id\'} with the XPath to the survey element named \'\'token_id\'\'. There is no survey element with this name."}''
This channel is really very helpful. Thank you very much Can the form be linked to spatial data and pull information. I wanted to do two things. 1. have a building footprint as polygon and when a data collector reaches that building and takes location the form should pop up the list of people (pre-populated as spatial data) living in that building 2. when a person takes the location, the form should pop up information of where the person is like county, district or state (which are prepopulated spatial data)whatever the hierarchical order. Must be possible. Eagerly waiting for a response.
Thanks for your question! I don't think you can update the csv directly, no. However, you could show the answer to the data collector "this family has 4 people"... And then ask a question "do you want to update"... And they could answer 5. And then you could simply update the csv after your round of data collection with all of the updated numbers. Let me know if you think this is a good solution, or if there's a better idea you have!
Yes, so you could have five or six different columns of data, and you would just create multiple "calculate" questions in your xlsform, each question would pull in a separate data point associated with the ID.
@@HumanitarianDataSolutions this is perfect, but my question is if I could return different data from a single column in the same record, for example, I have two columns, the first has 10 rows with the same data, this would be the id, and the second has a data different for each row, so when I try to do the pulldata it only brings me the data from the first row, the rest does not, what I want is to return all the data from the second column. This is possible? thanks so mutch!
Thank you so much for this ideas.. can we take more than one information at the seam calculate question ?? such as provens, name of interviewee , phone number....
Hello, I have a detail file of student data(name, age, schoolname, gender, address, etc) and I want to use pulldata function and auto load these data on Kobo toolbox after the collector enters student id. Based on id rest of the fields are auto filled. How could I do this? Please reply....
Hii please i need your help …if i need to ask about (address) province ,district ,subdistrict for more one questions for example ( agricultural holder address and his holding address ) and the two question have the same choices ..how i do that in xls form ? The choices are more than 28000
Hi Janna, Is it possible to return values from two columns? For instance if I want to return username and password based on let's say phone number. Can I do : pulldatafrom('pulldatafile','username','password','phone',${phone})
Hi Imran! What you do if you want to pull data from two different columns in your csv file is you create two different calculate questions in your Kobo form. One question pulls username, and one question pulls password. Hope that helps! One word of caution... The csv file is stored on your phone with no encryption. So anyone could technically access the whole list of passwords. So it isn't a very good idea for storing sensitive information. Not sure if you're actually pulling passwords from the csv, but this is just in case... Hope that helps!
Hi Janna, correct me if I am wrong but I thought the attachments ( i.e, the data file) is stored on the AWS servers hosted by Kobo. Isn't that the case?
@@imranesmail2006 hi, if you're only filling out the forms on a Web browser, and not collecting data through phones /tablets, that may be the case. However, if you're using a mobile device to collect data, the csv file will be downloaded and stored on the device, unencrypted. Hope that helps clarify, let me know if you have further questions!!
Just seen your helpful video. Maybe related to other questions too. can it works using API system? Or something like more automated. For example: the database updated separately using the same file. And during data collection the enumerator can see its update when do calculation. Thanks for your answer.
Hmmm, are you thinking that you'd like to have a database in the cloud server, and the answers to the questions updates the database, so all other enumerators and base office can immediately see the updates?
@@HumanitarianDataSolutions As I understand from your tutorial, (thanks for your great tutorial anyway) , if we want to provide updated lists for calculation by uploading new CSV file into form builder, then enumerators do updating the form through their app. I'm curios if kobo can do the opposite steps when we retrieves submitted data using XL and Kobo API. If it worked, some people can work to update CSV through cloud drive, at the same time enumerators see the update in the field when do calculation I think it would be useful when collecting a big number respondens into wide area and limited time to collect data. Thank you for your response. 🙏
Hi, thanks again for your response. I think this is a bit beyond the capabilities of Kobo Toolbox. However, you might want to read about ODK-X or CommCare or Dharma. I think these other different tools have more capability of two - way sync of data that might be interesting to you!
Hi vikram! I've just published my newest video which is all about the search function! Hope it really helps, would love to get your feedback on it! th-cam.com/video/eEXa8GK4yE4/w-d-xo.html
Thanks a lot for this video, I'm using Kobo web forms, but facing some problems this calculation is not producing results... is this tested in web forms as well or only on mobile app ?? Thanks Again
Thank you for sharing this information, how can this be accomplished with a @gepoint instead of static data from a cvs file? ex: pulldata("@geopoint",${location},"reversegeocode.address.Match_addr"). Any support you can offer would be great.
Hi Why this message comes up, the file is not empty. Is the anything I've to do? Error occured during the loading of this form. It is recommended not to use this form for data entry until this is resolved. Please contact support@kobotoolbox.org with the link to this page and the error message below: External instance "superviseur1" is empty.
This video was really helpful in demonstrating how to access and extract external data in XLSForm, Kobo Toolbox, and ODK using the 'pulldata' command. The step-by-step instructions were simple to follow, and I like how the syntax for the "pulldata" function was explained in a straightforward and succinct manner. It was really helpful to learn how to use "pulldata" in a practical survey application from the examples given in the video. I feel very confident using "pulldata" in my own surveys now thanks to this video, and I strongly suggest it to anyone wishing to access and extract external data for their XLSForm surveys.
Wow - what a great summary and thanks so much for taking the time to leave this encouragement! Sorry I missed it the day it came in, but just wanted to say thanks!
Wow!! 4 years later this has answered all my questions
This video made my day. Thank you so much for the explanation in clear manner.
That's such a kind comment, thank you so much!!! 😊😁
Thank you so much for this. I solved my pulling problem easily after watching your tutorial.
Your content kills me, I do love it and you
Wow! Thanks for taking the time to let me know!
If you want to learn more about how to EASILY get started with mobile data collection join my FREE Course now! ► humanitariandatasolutions.thinkific.com/courses/7steps
Thanks Janna. I really appreciate this. It a verry important leçon.
Thank you so much, this tutorial was very helpful to built a form with this function!
Hello! This is very helpful and simple to understand. But now, how to attach the csv file in ODK aggregate server? thanks!
You are the best!!! Thanks for the video !!!
Oh that's such a kind comment, thank you so much 😊 Glad it could help!!
Thank you so much, you resolved my problems, this is a great funtion
So happy this could help you!
Quick question. It looks like you populated the .csv file listing Family ID and province offline? Is there a method for using one form to "register" a participant (or family) and then using the data from that register as a source to pull data from? I'm trying to design the end to end solution for field technicians from intake to follow up where baseline and follow up data is tracked by one beneficiary ID (if that makes sense?)
Hi! Great question! This is a common query. However, there is no "two-way sync" of data from one form up to a cloud "database" that then another form can pull data from. Therefore, if you want to match data up between two different forms, that must be done offline, as in my example. It isn't an "elegant" solution, agreed. If you want that functionality, you might want to check out ODK-X, CommCare, or Dharma (BAO Systems). Essentially, you want to look for a software that has "case management" functionality, or two - way syncing. If you're using an ODK-based solution like the one in this video, then essentially you need to do the data-linking behind the scenes, using other software. This can still ask be automated, etc, but wouldn't be managed within KoboToolbox, for example.
@@HumanitarianDataSolutions Thank you so much for this reply. You've saved me a lot of frustrating searching. I think what we need is actually case management. best to you...
@@jeannemarie9185 oh perfect!!! Glad I could help at least save some research time 😊
I surely I enjoy learning Kobo tool box through this channel. Thanks. Today I have a question. I have a created a survey. I want to administer to subjects of my choice over and over. How should I link data of the same subject collected over different periods. I tried following the pull data video but I still need your help.
Hi Anthony, let me know if you want to jump on a quick call to discuss what you need! Email me at Janna [at] humanitariandatasolutions.com!
Oh sure, I'll. Thanks.
Very nice video ! Thanks a lot
Thank for the video. I was not successful as the kobo could not recognize my csv file. i am wondering if i missed something
Thanks. It is quite helpful
Wow! Thank you sooo much..Really helpful!
Thank you so much Augustine!!
You are the best!!!, I have learned a lot a greats functions, thank you so much.
Hi Janna,
In CSV file created unique id's, if I created drop-down list for that id's so my read only question can't filled as per CSV file they are just blank. Kindly let me know how to fix this issue.
Bonsoir. Merci beaucoup
Please tell me, Pulldata is not work with select_one value??
If I choice 'A' from select_one and then I want to show what is corespondation value with 'A' on csv?
Thanks for your brief tutorial. What if the there are more than one results returned based on the search? it only shows the first record it matches. Is there a way to display all matching results?
Great question - in order to use pulldata, you need to have a column with a unique index key - so there's only one match per lookup. So this wouldn't work for multiple matches. Can you explain your use-case a little bit more? Maybe there's a solution we can help you find!
THank you so much for the Help,
You're so welcome!! Thanks for leaving a comment, really appreciated!!
good work
Thank you!!!
I am trying very hard to get this to work and just can't seem to do it. I have replicated exactly what you show and just can't get the 'province' to show up as you describe. I am anxious to figure this out for an upcoming project we have. Is there a chance you can help me sort this out?
Hi Chrystine, (I love the spelling of your name by the way, really unique!) Thanks for your question! I've seen your email, so we can follow up further there! are you able to share your xlsform by email with me, and I can take a look?
Thank you very much for the video. Can we set a numeric answer of a question to be shown in somewhere else in the form as a default answer in number format question but not as a note.
how to check idf the Id entered in children form is found in the parent's entered data
form
Thank you very much, it worked very well for me.
Does this also work for Kobo collectect?
It certainly does!
Thank you
Hi Janna, this video is great, thanks a lot! I just had an issue with the preview showing the pulled data, but it not showing on the tablets - do you have any advice how to solve that? thanks!
Hi, thanks for your videos.
I have a query, I am using select one to send data from a csv file to kobo and it throws an error ... it says that it does not get the file ... I load it from media ... and it looks ... but when I go I don't see it and I can't edit anything.
Thank you very much in advance
Hi thanks for the tip. What if the you wanted to shows the pull data for a preceeding select one question and now you want to pull predefined categories using the pull data option.... e,g. Select health facility , then once selected it takes to a second page which pulled data show the type of the previous choice as a dispensary, health centres, clinic etccc. Thanks
Yes this is exactly the same idea! Hope it works out for you!! 👍💡
Great Help thanks
thank you so much
Hi Janna,
I do the same, but "the familyprovince" does not appear on ODK (Kobocollect) mobile app. it only works on Enketo (web form).
How to solve this problem?
Hello. Thank you very much for this video. It really saved me. I just have one critical problem though, I can't seem to be able to see the pulled data when using Kobo-collect. I want my fieldworkers to be able to see the prepopulated data for each question and verify this with participants. Any idea how I can solve this?
Hi Diana, thanks for this great tutorial. Is it possible to perform updates in the uploaded csv file i.e. by continually adding extra column data using the client side data collection?
Example: You are going to visit the each family every month and just want to capture the date of visit
Hi Bill, great question. You can't update the csv file in real time, for example, based on the answers you're collecting through your questionnaire. However, you can update the csv file manually, add in columns, add rows, change data, etc, and upload it to Kobo Toolbox.
The only thing you need to be careful of is that your data collectors then need to re-download the form and new csv file to their phone again.
@@HumanitarianDataSolutions can't we achieve it thru updating this thru googleSheets?
Thank you very much for the tutorials. I needed some help, I want to create an online booking form using kobo toolbox but I am unable to do so. I want when one gets as a seat, that particular seat to be unavailable to other persons when applying. I want to allocate seats from 1 to 100. Thanks
Hi! Great question! I don't think this is possible with KoboToolbox simply because it is a one-way data collection system, so it doesn't feedback to the form information that has already been submitted. Pulldata is a kind of workaround within KoboToolbox, so you could possibly update the external datasets once or twice a day, and see if you can get close to 100. Let me know if you'd like to brainstorm further!
How about we want to pull the cascade data?
I was able to put in all the pulldata calculations and uploaded the csv file but I am getting errors while trying to redeploy the xls error message "unexpected KoBoCAT error 400:b'{"detail":"There has been a problem trying to replace ${\'token_id\'} with the XPath to the survey element named \'\'token_id\'\'. There is no survey element with this name."}''
Hmmm, it seems you have an error in your form... Have you found the error yet? If not, let me know!
This channel is really very helpful. Thank you very much
Can the form be linked to spatial data and pull information. I wanted to do two things.
1. have a building footprint as polygon and when a data collector reaches that building and takes location the form should pop up the list of people (pre-populated as spatial data) living in that building
2. when a person takes the location, the form should pop up information of where the person is like county, district or state (which are prepopulated spatial data)whatever the hierarchical order.
Must be possible. Eagerly waiting for a response.
Thanks for your vidoe, can i change the data after pulling to the smart phon ?
for example: years ago family size were 4 member, now are 5.
Thanks for your question! I don't think you can update the csv directly, no. However, you could show the answer to the data collector "this family has 4 people"... And then ask a question "do you want to update"... And they could answer 5. And then you could simply update the csv after your round of data collection with all of the updated numbers. Let me know if you think this is a good solution, or if there's a better idea you have!
How can you add constraints when you have used pulled data function?
Is there a way to do this with multiple fields that have the same id?
so that it shows multiple fields in the note
Yes, so you could have five or six different columns of data, and you would just create multiple "calculate" questions in your xlsform, each question would pull in a separate data point associated with the ID.
@@HumanitarianDataSolutions this is perfect, but my question is if I could return different data from a single column in the same record, for example, I have two columns, the first has 10 rows with the same data, this would be the id, and the second has a data different for each row, so when I try to do the pulldata it only brings me the data from the first row, the rest does not, what I want is to return all the data from the second column. This is possible?
thanks so mutch!
Thank you so much for this ideas.. can we take more than one information at the seam calculate question ?? such as provens, name of interviewee , phone number....
Hi Rima, yes, a calculation question is very flexible. What are you trying to calculate? Maybe I can help you!
it was very useful, thanx
You're very welcome Nabaz!
Hello, I have a detail file of student data(name, age, schoolname, gender, address, etc) and I want to use pulldata function and auto load these data on Kobo toolbox after the collector enters student id. Based on id rest of the fields are auto filled. How could I do this? Please reply....
Keep repeating the calculations. Take it each by each
Dear Janna, could you please send the resources used in the video. Thank you
Thank you!
Hii please i need your help …if i need to ask about (address) province ,district ,subdistrict for more one questions for example ( agricultural holder address and his holding address ) and the two question have the same choices ..how i do that in xls form ? The choices are more than 28000
Let me have a list of HH members ,but I want to interview one of members after recording all?
Hi Janna,
Is it possible to return values from two columns? For instance if I want to return username and password based on let's say phone number. Can I do :
pulldatafrom('pulldatafile','username','password','phone',${phone})
Hi Imran! What you do if you want to pull data from two different columns in your csv file is you create two different calculate questions in your Kobo form. One question pulls username, and one question pulls password. Hope that helps!
One word of caution... The csv file is stored on your phone with no encryption. So anyone could technically access the whole list of passwords. So it isn't a very good idea for storing sensitive information. Not sure if you're actually pulling passwords from the csv, but this is just in case...
Hope that helps!
Hi Janna, correct me if I am wrong but I thought the attachments ( i.e, the data file) is stored on the AWS servers hosted by Kobo. Isn't that the case?
@@imranesmail2006 hi, if you're only filling out the forms on a Web browser, and not collecting data through phones /tablets, that may be the case. However, if you're using a mobile device to collect data, the csv file will be downloaded and stored on the device, unencrypted. Hope that helps clarify, let me know if you have further questions!!
Just seen your helpful video. Maybe related to other questions too. can it works using API system? Or something like more automated. For example: the database updated separately using the same file. And during data collection the enumerator can see its update when do calculation.
Thanks for your answer.
Hmmm, are you thinking that you'd like to have a database in the cloud server, and the answers to the questions updates the database, so all other enumerators and base office can immediately see the updates?
@@HumanitarianDataSolutions
As I understand from your tutorial, (thanks for your great tutorial anyway) , if we want to provide updated lists for calculation by uploading new CSV file into form builder, then enumerators do updating the form through their app.
I'm curios if kobo can do the opposite steps when we retrieves submitted data using XL and Kobo API. If it worked, some people can work to update CSV through cloud drive, at the same time enumerators see the update in the field when do calculation
I think it would be useful when collecting a big number respondens into wide area and limited time to collect data. Thank you for your response. 🙏
Hi, thanks again for your response. I think this is a bit beyond the capabilities of Kobo Toolbox. However, you might want to read about ODK-X or CommCare or Dharma. I think these other different tools have more capability of two - way sync of data that might be interesting to you!
@@HumanitarianDataSolutions Hi again. Thanks for your answer and info. I read about them. Thanks for your great sharing. Keep good sharing 🙏
Oh great to hear! Thank you so much!!!
could you make a video using SEARCH function that uses external CSV file to bring to select_one list, thx
Another great suggestion! Adding it to the list of videos to create!
@@HumanitarianDataSolutions thx a lot
Hi vikram! I've just published my newest video which is all about the search function! Hope it really helps, would love to get your feedback on it! th-cam.com/video/eEXa8GK4yE4/w-d-xo.html
Thanks a lot for this video, I'm using Kobo web forms, but facing some problems this calculation is not producing results... is this tested in web forms as well or only on mobile app ?? Thanks Again
Thanks for your question! I'll double check it for you and let you know what I find!
Can pull data as a texbox?
Hello ma. How I can Upload my CSV file in Ona server. Thank you
Thank you for sharing this information, how can this be accomplished with a @gepoint instead of static data from a cvs file? ex: pulldata("@geopoint",${location},"reversegeocode.address.Match_addr"). Any support you can offer would be great.
Hi Tyrone, thanks for your question! Are you using Survey123, or what data collection app are you using?
@@HumanitarianDataSolutions Please can you post similar pulldata example asked by Tyrone Miles for survey123 forms
Instead of excel , can this be Google Sheets... It will b easy to update sheets
Hmmmm, no I don't think so.
@@HumanitarianDataSolutions ok thanks
Awasome
Thanks Sidy!
Hi Why this message comes up, the file is not empty. Is the anything I've to do? Error occured during the loading of this form. It is recommended not to use this form for data entry until this is resolved.
Please contact support@kobotoolbox.org with the link to this page and the error message below:
External instance "superviseur1" is empty.
Can you tell me how can I randomly call phone number in xls from the range of phone number is from 09000000 to 09999999.
Thank you
Very welcome!
Can you tell me how can I randomly call phone number in xls from the range of phone number is from 09000000 to 09999999.