Reading from a database

I've just started using ODK, but previously used SurveyCTO. I am trying to use the pulldata function to retrieve fields like age, name from the server (in this case gsheet) to display in the form. I guess this is a two part question.

  1. Is it even possible to do this? I've read that you don't have bi-directional syncing, but it seems like from some existing threads that people are reading from their database.
  2. If you can, how do you do it when writing the form in a CSV?

This is the general function i've used in SurveyCTO, and have tried many different alterations but all have failed.

In the calculation field i've put:
pulldata('demo_dataset','age','registration-id', ${uid})

Any help from the community would be much appreciated!

Hi @MB
We're glad you're here. When you get a chance, please introduce yourself on this forum thread. I'd also encourage you to add a real picture as your avatar because it helps build community!

We support functions like pulldata/search which read data from external csv files attached to your form.
If those csv files are dynamic and you need to update them then you need to upload a new version to your server (ODK Central for example) and redownload in Collect. To make sure it's always updated in Collect you can enable Exactly match server

If you need to know more about those functions:
https://xlsform.org/en/#how-to-pull-data-from-csv
https://xlsform.org/en/#dynamic-selects-from-pre-loaded-data

Thank you @Grzesiek2010, really helpful. And helped me see that i'm structuring the function correctly, but I'm still not able to pull the data. A few additional questions:

  1. Could it be that this only works if its hitting a csv file name and therefore since I am using google sheets url that it will not work?

  2. I have a repeat group where I am extracting data from an android intent response. I am using count-selected to count the number of returns we get and then in the repeat group, I am using indexed-repeat to extract the data. Are these the right functions to use?

Apologies for what are likely rudimentary questions. I have run through so many iterations and although my csv is validated when i convert to xform, when i run through the form the pulldata retrieves blanks. I'm effectively trying to diagnose at what point in the form that I am doing something incorrectly.

Thank you so much for your help!

The best option would be to share your form here if it's possible so that we can see and test it. Could you do that?

Hi @Grzesiek2010 happy to do that. Slight caveat is that this form is integrating into a biometric system, and in order to test you would need to be set up with a project for them and have their app. But I think just seeing the form should be enough to identify where i've gone wrong.

The issue (I'm nearly certain) sits between rows 26-40, which is where I am extracting the delimited list of results being returned to the app and then displaying the results.
The image on the left is the page being displayed after the callback from the biometric app, which is correct. But then the image on the right is where I'm trying to calculate and extract the corresponding names and ages of the GUIDs that sit in the database, but i'm getting the results you see there.

Thank you so much for your help.

ODK Exploration copy.xlsx (28.9 KB)

Ah ok so as I thought you are trying to use pulldata with an online sheet. That's not possible. You need to download such a file (it must be a csv file) and upload it as a media file (like image/audio/video files for example).
Did you see anything (documentation or something) that would indicate it's supported?

Ah ok, yeah that was my hunch too. I struggled to find any documentation that mentioned the pulldata function. So best bet would be for me to buy ODK Cloud and set up a server, or to set up my own, I suppose?

Also, would you kindly check the Calculation fields for rows 26-40 as well? I've made quite a few changes to these fields and want to make sure I am leveraging the right functions to recursively loop through the results and to ultimately have them display correctly.

Looks like we're nearly there! Thank you!

It's always a better and easier option to use ODK Cloud.

When it comes to your calculations (rows 27-29) you don't need indexed-repeat() functions there because you refer questions that are in a regular group so there is just one instance for for every question (odk-registration-id, odk-guids etc). Indexed-repeat() is used when you refer questions that are in a repeat-group.
From row 35 it makes sense because there you refer concatinfo which is in a repeat group indeed.