Automation Solution for �Rapid Kobo Form Deployment

​

Last Updated: October 2021

Confidential Open Function Group Version 2.0

Confidential Open Function Group Version 2.0

Rapid Kobo Deployment Form Automation

Don’t have a destination database configured yet? Not sure how to structure your data?

→ Go from… Kobo form → Database configuration + Integration job in minutes!

​

​

​

​

​

​

​

​

​

​

​

​

​

⚡

✨

✨

Delivers...

• Automated database configuration
• Automated integration → creates Job templates

​

This automated is powered by multiple OpenFn jobs. Job “A1” triggers the others…

Rapid Kobo Deployment Form Automation

Don’t have a destination database configured yet? Not sure how to structure your data?

→ Go from… Kobo form → Database configuration + Integration job in minutes!

​

​

​

​

​

​

​

​

​

​

​

​

​

⚡

✨

✨

Delivers...

• Automated database configuration
• Automated integration → creates Job templates

​

This automated is powered by multiple OpenFn jobs. See below for the technical details.

The original requirements.

• As a WCS data manager, I want to automatically integrate data from Kobo surveys collected across different partners and sites so that I can regularly monitor and report across these data sources.

From WCS….

• Identify an automated or semi-automated process for going from a Kobo form to a Postgres set of holding tables.
• We should assume:
1. We can define the table names and field names based on the Kobo form
2. There are no column-level transformations needed as a default beyond what is required to create relational tables using WCS DB standards & reference tables with “lookup” values from Kobo “select” questions
3. Form updates should be supported

Confidential Open Function Group Version 2.0

Automation to sync Kobo forms to DB metadata...

OpenFn will auto-generate SQL scripts to create database tables and columns that reflect the Kobo form structure.

• If the Kobo form has nested repeat groups, then nested child tables will be inserted into the DB.
• If the Kobo form has “select_one” or “select_multiple” questions linked to “lists” of choice values, then separate lookup reference tables will be created to capture these lists of choice values (1 reference table per list)..

Confidential Open Function Group Version 2.0

Automated vs. Manual Admin Steps

Confidential Open Function Group Version 2.0

Kobo “select” questions → DB Lookup Tables

region select_one �landscape select_multiple

Confidential Open Function Group Version 2.0

Syncing Metadata�Automation to configure DB metadata & integration scripts

​

Confidential Open Function Group Version 2.0

New Kobo Forms: Generating new metadata...

Confidential Open Function Group Version 2.0

A1: Generate Jobs, DB Tables, & Dictionary

Enter forms into job “A1” in order to:�1. Generate SQL based on the �form definition �2. Generate new OpenFn job �(to later sync data)

3. Export the Kobo form definition� (to prepare to later sync with the DB’s Data Dictionary)

Confidential Open Function Group Version 2.0

A3: Generates SQL Script

You can choose…

1. To auto-execute the SQL script in target DB, OR...
2. To export the SQL script to manually edit & execute in your target DB

Confidential Open Function Group Version 2.0

Default job options:

{ writeSql: true, execute: false }

A4: Generates OpenFn Job

A4 run will include the name of the job.�Once created...

• Update the Credentials to the target DB
• Edit the job as needed to reflect any manual SQL changes

Confidential Open Function Group Version 2.0

More on the Kobo <> SQL sync.

Mapping Input: �{ “p1”: “WCSPROGRAMS”, �“p2”: “marche”, �“tableId: “MarketSurvey” } �→ outputs: WCSPROGRAMS_marche_MarketSurvey

{formName_groupName}

{formName_groupName}

Confidential Open Function Group Version 2.0

p1_p2_tableId

�uuid

jsonb (full JSON export)

Q1...

Kobo {FormName}

​

Q1�Q2�> repeat groupName

>> nested groupName�

p1_p2_{NestedRGroupName}

​

p1_KoboData

�datasetId�formName�...

p1_p2_{RepeatGroupName}

​

​

A5: Prepare Form for Data Dictionary

A5 job run is the last step triggered in the automation flow. It will…�

• Create the Data Dictionary tables via SQL script (if they don’t already exist in the DB)
• Export the Kobo form definition (and post as a Message to the OpenFn inbox to later be loaded into the tables - see 1 - when admin is ready)

Confidential Open Function Group Version 2.0

Job A5 “Prepare Form for Data Dictionary” will sync the Kobo metadata to 3 tables in the connected database:

• kobotoolbox_forms
• kobotoolbox_questions
• kobotoolbox_choices

​

See this spreadsheet for the specification for how these tables are configured: https://docs.google.com/spreadsheets/d/1MemLYtWkzQosf0jlpVnWLepAuwnUHohCCcUG4tE4MR8/edit#gid=151768242

A5: Prepare Form for Data Dictionary

​

Confidential Open Function Group Version 2.0

If you would like the Automation solution to continually change for changes to Kobo form metadata…

1. Add the form to the “A1” job
2. Clear state (if syncing metadata for the first time) & run the A1 job to trigger the automation solution
3. Verify that the “A5” job runs successfully
4. Check the DB connected to the A5 job to inspect the “kobotoolbox_forms”, “...questions”, and “...choices” tables to view the output
5. Keep the “A1” job running to repeated check if any metadata changes have been made to the form. (If yes, then the full automation solution will run again, including the A5 step to update the metadata tables.)

A5: Prepare Form for Data Dictionary

​

Confidential Open Function Group Version 2.0

Syncing Data

​

Confidential Open Function Group Version 2.0

Ready to sync data...

Once you have…

1. Run the automation jobs (A1-A5) for a form
2. Made manual edits to the SQL scripts and corresponding OpenFn job (as needed)
3. Executed the SQL scripts in the target DB �

… the target metadata is now set up!��Now you are ready to sync data.

Confidential Open Function Group Version 2.0

Sync Data (Kobo submissions)

You can choose to…�1. Run forms in a one-time sync to migrate historical data after a specific date. (Use this option for archived forms or for migration of historical data before today’s date.)�

2. Add forms to a job that runs on an ongoing basis (e.g., every 3 hrs) to regularly check for new submissions. (Use this option for forms where data collection is active and ongoing.)

Confidential Open Function Group Version 2.0

Kobo Metadata & �Form Management

​

“We will define the table names and field names based on the Kobo form “ →

What metadata in the Kobo forms should be used to define the DB configuration?

​

Confidential Open Function Group Version 2.0

WCS Kobo form management.

• WCS creates different survey types or form templates (e.g., “BNS Survey”)
• These form templates will be deployed across different sites and partners for data collection. When deployed...
• These forms might be renamed → e.g., “BNS Ndoki 2020”
• Form questions might be modified/ added
• Data collection for these forms may run for different time periods and may have already started → we still need to map all of this data into 1 database.

Confidential Open Function Group Version 2.0

Available Kobo metadata.

• When we fetch forms from Kobo Toolbox, we are able to extract the following metadata…
• _id
• _uuid
• _notes
• _tags
• _validation_status
• _attachments"
• Submission details (_submission_time, _submitted by, _geolocation)
• Any other metadata needs to be added to the form definition, e.g.:
• form name
• form owner

​

​

Confidential Open Function Group Version 2.0

How to add Kobo metadata to the form definition?

• “Hidden” values can be added using a “Calculate” field
• This can be done both in the Form Builder or via XLSForm
• (A dedicated “hidden” field exists in XLSForm but not supported by the Kobo Form Builder to date)

Confidential Open Function Group Version 2.0

Adding a “Calculate” field through the Kobo form builder

​

Adding a “Calculate” field in XLSForm

​

​