Updating external media files for select questions from another form using Central's API

Showcase
1

Some work in progress.
I was looking for a solution to upload and publish forms from the command line using the API, in order to update references lists used by select_one questions (like studies lists, places...)

I found this discussion and those curl examples :slight_smile:

Playing a little with this and the new map appearance for select_one_from_file, I achieved to do it.
I created a form to plot places on fields :
create_places.xlsx (6,6 Ko)

The second form uses a select_one_from_file question, with map appearance and needs a geojson file called places.geojson (updated by the first form).
select_from_geojson.xlsx (6,4 Ko)

On the database side, I pull the data from Central's "create_places" form submissions :

SELECT odk_central.odk_central_to_pg('my.email@adress.com', 'my_password>', 'myodk.server.fr',3, 'places','odk_central','localisation');
-- see https://forum.getodk.org/t/postgresql-set-of-functions-to-get-data-from-central/33350/15 for function details

then I can export data as a geojson file :

COPY (
	WITH places AS (SELECT id, st_force2d(st_geomfromgeojson(replace(localisation,'\','')::json)), title, description
	FROM odk_central.form_create_places_places_data)
SELECT
  json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*)::json)
  )
FROM places AS t
	  ) to '/path/to/file/places.geojson';

And in the end publish a new from version with updated media file :

curl --include --request POST --header 'Authorization: Bearer azertyuiopqsdfgh' --header "Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" --header "X-XlsForm-FormId-Fallback: geojson" --data-binary "@/path/to/file/select_from_geojson.xlsx" 'https://myodk.server.fr/v1/projects/3/forms/select_from_geojson/draft?ignoreWarnings=true'
curl --include --request POST --header 'Authorization: Bearer azertyuiopqsdfgh' --header "Content-Type: application/json" --data-binary "@/path/to/file/places.geojson" 'https://myodk.server.fr/v1/projects/3/forms/select_from_geojson/draft/attachments/places.geojson'
curl --include --request POST --header 'Authorization: Bearer azertyuiopqsdfgh' 'https://myodk.server.fr/v1/projects/3/forms/select_from_geojson/draft/publish?version=2022040717'

I will now try to do integrate curl calls into SQL functions !

4 Likes
2

Very cool! By the way, in case it helps, note that you can create a draft without re-sending the XLSForm. In that case, the draft will use the existing form definition.

1 Like
3

Thanks Matthew.
I was not sure about it.
Thanks for the tip.

1 Like
4

Thanks for sharing.
I am doing csv media update workflow from remote server via APIs amd sftp to cross link submitted data as well.
I very much look forward to seeing your integrated curl calls. Please share when done. Thanks in advance.

1 Like
5

here it is : Updating external datasets from another form's submissions data from within a PostgreSQL database

1 Like