API call to pull submission and push as media

[alios82]

I want to pull submission in form A and push to form B (as csv media).

Is there a way to chain API call on ODK central server to:

1)Pull submitted data as CSV
2)Push the CSV as media to one or more existing forms

The general answer is yes. I have already done that using APIary but via remotely hosted server using API key auth.

However, can it be (3)scheduled(as opposed to manually) or (4)scripted to to run in event of a new submission or (5) combination of 3&4 on local server e.g. in digital ocean?

[Florian_May]

Hi @alios82,

you can schedule a near-real-time set of API calls as follows:

• Have a script to action your API calls. Sounds you have a working set of API calls.
• Create a Docker image running the script as a cron job (this is the answer to "run script automatically on a schedule") - often enough to be real-time, slowly enough to let jobs finish before restarting. This is the answer to "event of new submission".
• Run the Docker image on your preferred hosting provider using your preferred orchestration suite.
• Mount persistent storage for anything you need to keep around - e.g. submission media files.
• You can use the persistent storage from another container (e.g. a dashboard). This is a solution to feed live data into a dashboard or other consumer.

[alios82]

Thanks Florian.
Yes i am running API calls via powershell on a local computer over the internet.
Am new to containers. Any specific reading i can do ?
I have central in digital ocean and would prefer to use the same for running script. What would u recommend - python or other shell based script?

Any info is appreciate as a springboard...

Love the idea of streaming to dashboard.

[Florian_May]

No worries!

I've kept my instructions very generic because you can pick basically any technology for each step.
I'm a few weeks away from writing up a nice example, and getting this all to work included a few filthy problems to solve.

A few high level pointers:

• I'm doing all data / API work in R using ruODK, but you can use any scripting language you're comfortable in (Python, bash, Ruby, see examples in ODK apiary docs)
• Docker image with a cron job: https://stackoverflow.com/questions/37458287/how-to-run-a-cron-job-inside-a-docker-container -
• some cron tripping wires:
  • cron runs as user root and in /root
  • cron does not see your environment variables (you have to read them in inside the script)
  • do print out any paths and files and whether they exist or not - useful for debugging

The simplest implementation for you could be a cronjob running your script from within a second DO droplet (not the one running Central). There's no need to go through Docker and Docker orchestration at that level of complexity.

For a working example of a cronjob running a script, see https://github.com/dbca-wa/etlTurtleNesting/tree/master/cron: The last commands in the Dockerfile set up the cronjob to run the job (in my case job.R) and configure logging.

[alios82]

Thanks a bunch. Agreed with keeping it basic. Will give it a shot.

Look forward to your example and insights in few weeks. GL

[yanokwa]

@Florian_May's answer is a good one, but I wanted to point out that Docker isn't necessary. You can do most of what you want by extending what you have now.

If you are on a *nix system, you can schedule with cron. If you are on Windows, you can use Windows Task Scheduler.

You can poll the server to look for a new submissions. The simplest way is to check the OData endpoint for all submission IDs after a particular datetime. Persist the time and list of submissions, then on the next check, see if any have been added. If you see new submissions, process those new submission IDs and mark them in your persisted list as processed.

You can run these scripts anywhere because it's over HTTP. I usually run scripts like these on the local server itself because then there is no network latency.

[mathieubossaert]

Hi to all.

@alios82

2)Push the CSV as media to one or more existing forms

The general answer is yes. I have already done that using APIary but via remotely hosted server using API key auth.

Very interesting topic but how to "Push the CSV as media to one or more existing forms" in order to update the form.
I can't find anything in the documentation about this : https://odkcentral.docs.apiary.io/#reference/forms/forms/creating-a-new-form

Can someone share more information about that ?

[alios82]

Agreed. Thanks Yaw.
Currently running on local powershell accessing APIs over the internet. Intend to incorporate polling approach to avoid too much traffic and lessen security risks.

[alios82]

Yup there is an API to download submission as CSV and also to update form media in the odk central APIary.
I managed to curl in unix and invoke-rest in powershell to make this work for POC.

[mathieubossaert]

Thanks.
I just found some explicit examples here :

Posting form attachments via ODK Central API

Success! Haha, I know I've been talking to myself on here. But I figured out the solution for regularly updating form attachments in Central via API. The main challenge was that I didn't understand that it's not possible to delete / post to the published form directly. So the process is: Post the form again as a draft Post the attachments to the draft form Publish the form with a new version number (which can be supplied in the API call, no need to update the version number inside the form. …

Will try it soon.

[alios82]

Yup. I am using almost the same functional steps.

[Mauricio_Andres_Arci]

Hi, I have been following this topic and I would like to ask the following question:

Is it possible to update only the media files without updating the version of a form?

In my case, the media files are .csv files that help me to complete the forms more quickly. Although they change the form structure does not change. Through ODK Central, without uploading a new format definition, I get the following message: "The version name you have specified conflicts with a previous version of this form. Please change it to a new one and try again."

Using Curl with Powershell (using the lines indicated by @Nicholai) I get the following message:
{"message": "You have tried to publish the form 'FORM-ID' with version '19.03.2022.1', but there is already a form published in this project with those identifiers. Please choose a new name and try again. You can reapply with ?version=xyz to have the version changed instead.", "code":409.6, "details":{"xmlFormId": "FORM-ID", "version": "19.03.2022.1"}}}

Thanks

[alios82]

Hey Florian
When you put together the example related to API usage- could you post a link on this thread as well? I for one am looking forward to reading and using it.
Ali

[Phil_Burapha]

Did you implement this process? Do you know if this is still the best way to update form attachments, or is there now a more straightforward way to update attachments.

[mathieubossaert]

We did implement this process last year and we described it here :

Updating external datasets from another form's submissions data from within a PostgreSQL database

I will first simply copy/adapt the push_json_media_to_central() function for csv files. I will try it by the end of the day and update the repo to add push_csv_media_to_central() function. In a second time will probably add a generic function to upload csv or geojson or xml attachments that checks the file extension to adapt the Content-type Header.

But in our common use cases the best way to update form attachments will soon be datasets and entities and we will use it as soon as possible instead of API form update.

[Phil_Burapha]

Thanks. I am keeping an eye on entities and datasets and have made my first forms. But I still need the functionality that entities are automatically approved and datasets can be updated from the API, so I may have to wait for a little more development.