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:
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.
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
Use the mobile app to start entering data. The app looks after syncing back to the ODK server when in data reception.
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
Access to a ODK Central server, or install your own version on Oracle Cloud following my guide.
Download this EMP.xlsx
ODK Project & Form Setup
Log in to ODK Central Dashboard
Click Projects > + New ...
Enter HR Emps
Click Create
Click Forms > + New ...
Select EMP.xlsx (the one you downloaded earlier)
Click Upload
This Form is now in draft status.
Click Publish Draft and then Proceed
You can Preview if you wish, close the form down.
Click on Projects > HR Emps > App Users > Create App User
Create a user called Users
You'll now see a QR code
Don't scan it. Click Done
Click Form Access and click the checkbox under Users
Click Save
Go back to App Users and click See Code next to Users
Device Setup
Install ODK Connect on your Android Device or GIC Collect on your iOS device
- Google Play
- iOS App StoreOn 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)
Quit the App and turn Flight Mode on to simulate Offline Mode.
On your device: Click Start New Form
On your device: Click on EMP
Complete a data Entry for a fictitious person GNONTO with random values. Notice that Commission is not asked for unless they are a SALESMAN.
At the end of the form, click Finalize
Your submission will be in Ready to Send.
Click Ready to Send > Select All > Send Selected > You won't be able to do it
Turn Flight Mode off
Notice that the submission has automatically been sent.
On your Desktop: Go to HR Emps > EMP > Submissions and notice that GNONTO is now present
Configure API Access to fetch the Submissions
Open your APEX Application / Create a new one called ODK
Click Shared Components > Credentials > Create
Complete the Form like this using your ODK Connect Credentials - i.e your Dashboard username and password.
Click Create
Go back to Shared Components (I just use the browser history).
Click REST Data Sources > Next > Next
Enter a Name of ODK EMP Form Submissions
Enter the URL of your ODK Server like this
https://[MY URL]/v1/projects/[PROJECT ID]/forms/[FORM ID]/submissions.csvi.e
https://odk.leedsunited.com/v1/projects/1/forms/EMP/submissions.csvClick Next, Next, Next
Select your Authentication
Click Discover and you'll see your submission
Click Create REST Data Source
Click on ODK EMP Form Submissions
Click on Edit Data Profile
Set the META_INSTANCEID to be the Primary Key
Click Apply Changes
Click Manage Syncronizations
In Table Name type ODK_EMP_FEED
Click Save
Click Create Table
Set the Synchronization Type to be Merge
Click the Wrench/Clock icon next to the Synchronization Schedule
Click Hourly
Click Set Execution Interval
Click Save and Run
Have a look at your ODK_EMP_FEED in Object Browser
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!