Collect Offline Form Submissions with ODK & Synchronize with APEX

Collect Offline Form Submissions with ODK & Synchronize with APEX

Matt Mulvaney's photo
Matt Mulvaney
·

5 min read

Need a simple form so that users can collect data on their Mobile whilst in a zone where there is no data reception - which then syncs back when there is reception?

I actually first heard of this approach on Joel Kallman Day 2023 where Anton Kwang wrote about KOBO toolbox - and the subject is quite fascinating. However, for me, the biggest problem I have with KOBO is that customer data is exposed on service that I do not own. The data itself is protected by a HEX string API Key and you can read this excerpt from their privacy policy is here:

Registered users are responsible for the data they collect, and KoboToolbox employs security measures to protect user data, but absolute security cannot be guaranteed

After some looking for alternatives, I found ODK (Open Data Kit) which has an open source version available which I installed on an OCI compute box.

Both KOBO and ODK share a bunch of similarities and the basics are:

  1. Create a Form in Excel using the https://xlsform.org/en/ standard. In this link it describes how to build an example using pizza 🍕 as examples. In this blog, you'll download one I prepared that describes the EMP table.

  2. Upload this XLSX to ODK. Its then converted to XML which for evermore will the the standard. So ensure you keep a copy of the source XLSX

  3. Use the mobile app to start entering data. The app looks after syncing back to the ODK server when in data reception.

  4. APEX uses a Rest Endpoint Synchronization to pull the submissions in to APEX.

The remainder of this blog describes how to configure a Form with the EMP table.

Prerequisites

  1. Access to a ODK Central server, or install your own version on Oracle Cloud following my guide.

  2. Download this EMP.xlsx

ODK Project & Form Setup

  1. Log in to ODK Central Dashboard

  2. Click Projects > + New ...

  3. Enter HR Emps

  4. Click Create

  5. Click Forms > + New ...

  6. Select EMP.xlsx (the one you downloaded earlier)

  7. Click Upload

  8. This Form is now in draft status.

  9. Click Publish Draft and then Proceed

  10. You can Preview if you wish, close the form down.

  11. Click on Projects > HR Emps > App Users > Create App User

  12. Create a user called Users

  13. You'll now see a QR code

  14. Don't scan it. Click Done

  15. Click Form Access and click the checkbox under Users

  16. Click Save

  17. Go back to App Users and click See Code next to Users

Device Setup

  1. Install ODK Connect on your Android Device or GIC Collect on your iOS device
    - Google Play
    - iOS App Store

  2. On your device: Start the App, click Configure with QR Code and scan the code on the screen (note: you won't be able to scan the one in my blog)

  3. Quit the App and turn Flight Mode on to simulate Offline Mode.

  4. On your device: Click Start New Form

  5. On your device: Click on EMP

  6. Complete a data Entry for a fictitious person GNONTO with random values. Notice that Commission is not asked for unless they are a SALESMAN.

  7. At the end of the form, click Finalize

  8. Your submission will be in Ready to Send.

  9. Click Ready to Send > Select All > Send Selected > You won't be able to do it

  10. Turn Flight Mode off

  11. Notice that the submission has automatically been sent.

  12. On your Desktop: Go to HR Emps > EMP > Submissions and notice that GNONTO is now present

Configure API Access to fetch the Submissions

  1. Open your APEX Application / Create a new one called ODK

  2. Click Shared Components > Credentials > Create

  3. Complete the Form like this using your ODK Connect Credentials - i.e your Dashboard username and password.

  4. Click Create

  5. Go back to Shared Components (I just use the browser history).

  6. Click REST Data Sources > Next > Next

  7. Enter a Name of ODK EMP Form Submissions

  8. Enter the URL of your ODK Server like this

     https://[MY URL]/v1/projects/[PROJECT ID]/forms/[FORM ID]/submissions.csv

    i.e

     https://odk.leedsunited.com/v1/projects/1/forms/EMP/submissions.csv

  9. Click Next, Next, Next

  10. Select your Authentication

  11. Click Discover and you'll see your submission

  12. Click Create REST Data Source

  13. Click on ODK EMP Form Submissions

  14. Click on Edit Data Profile

  15. Set the META_INSTANCEID to be the Primary Key

  16. Click Apply Changes

  17. Click Manage Syncronizations

  18. In Table Name type ODK_EMP_FEED

  19. Click Save

  20. Click Create Table

  21. Set the Synchronization Type to be Merge

  22. Click the Wrench/Clock icon next to the Synchronization Schedule

  23. Click Hourly

  24. Click Set Execution Interval

  25. Click Save and Run

  26. Have a look at your ODK_EMP_FEED in Object Browser

  27. We have data!

Next steps - well you'll probably want to integrate these now with your EMP records. You also have a REVIEWSTATE and STATUS columns. You may decide to only allow Reviewed records to merge with your EMP table. This can be handled in ODK and because our sync merges records, they will be updated.

I would use a Trigger on ODK_EMP_FEED to Attempt to add them to EMP. Submissions may be edited. So you may wish to handle these in some way.

API

There is a metadata API that could be useful. The API Documentation is pretty useful

Here are some useful curl commands that you can amend.

# Retrive and snip the token 
TOKEN=$(curl -s -X POST -H "Content-Type: application/json" -d '{
  "email": "myemail@example.com",
  "password": "mypassword"
}' https://odk.leedsunited.com/v1/sessions | jq -r '.token')

# Get Submissions
curl -H "Authorization: Bearer $TOKEN" https://odk.leedsunited.com/v1/projects/1/forms/EMP/submissions

There doesn't seem to be a way to remove submissions. The only way I found is to use the API to remove and recreate the Form. Still looking for solutions.

Future enhancements

I'd like to have dynamic data. e.g If a new Job appears, I'd like this to be updated on ODK with a new form. This can be accomplished in a series of API calls, initially creating a Draft Form, creating a Draft Attachment (which is a CSV) with a unique file name for each version and then finally publishing it. The user would see a new version in their ODK application. It's too much for this blog... so watch this space.

ENJOY!

Whats the picture? Sunset over The Stray. Visit Yorkshire!

orclapex