Can ODK be used as a mobile Front End for a SQL backend? If yes how can I link the columns in SQL to the choices sheet in the excel form?
A link to a website outlining the steps would be great.
hi @Curtly,
ODK is software comprising of different tools, there is odk collect which is a mobile app used for data collection, the responses can the be uploaded to aggregate, another ODK tool. The aggregate is the back-end of the mobile app. And it support SQL. The aggregate also provide an API which can be put to use if you want a custom solution over the aggregate.
There is no link with such information.
1 Like
Thanks for your reply. How can I add sql to aggregate? and how can I link the questions from build or xlsforms to the sql?
My agency (Government) routinely visit farmers and advise them on how to improve production. I would like to use ODK to collect data but in SQL since the data collection is continueous and not one off. One of the challenge is how do i link the choices sheet in excel to the a sql table.
I think you need to read more how the platform works. You need to deploy it in a server. It can be a custom server or google app engine. If you are going to deploy your own aggregate you need some IT skills to do that.
Other than that you can use other platform that can host these form. You can search the forum, you will find great answer. One popular platform is ona.io.
Note that all these cost money whether you go for cloud server, app engine or ona.
Checkout the odk docs as well , https://docs.opendatakit.org/
3 Likes
In a manner of speaking... But its really more accurate to say SQL can be used as a backend for ODK Collect front end. Basically, ODK Collect will fill in data for a given form and push it up to ODK Aggregate, which will persist each result in a flat SQL table, one per form. Each table looks a bit like this:
+-------------------------------------------+-------------------+----------------------------+-----------------------+----------------------------+----------------+-------------+--------------+----------------------------+----------------------------+------+-------+--------+------+------------------+
| _URI | _CREATOR_URI_USER | _CREATION_DATE | _LAST_UPDATE_URI_USER | _LAST_UPDATE_DATE | _MODEL_VERSION | _UI_VERSION | _IS_COMPLETE | _SUBMISSION_DATE | _MARKED_AS_COMPLETE_DATE | DOB | COLOR | NAME | AGE | META_INSTANCE_ID |
+-------------------------------------------+-------------------+----------------------------+-----------------------+----------------------------+----------------+-------------+--------------+----------------------------+----------------------------+------+-------+--------+------+------------------+
| uuid:7719855f-ef70-491c-a803-d2cbeccb9976 | anonymousUser | 2018-05-15 12:08:59.776000 | NULL | 2018-05-15 12:08:59.776000 | NULL | NULL | 1 | 2018-05-15 12:08:59.754000 | 2018-05-15 12:08:59.754000 | NULL | green | Gareth | 21 | NULL |
| uuid:0881df47-61a1-480a-83ce-de4b2ddb5196 | anonymousUser | 2018-05-15 14:40:46.759000 | NULL | 2018-05-15 14:40:46.759000 | NULL | NULL | 1 | 2018-05-15 14:40:46.747000 | 2018-05-15 14:40:46.747000 | NULL | green | Brenda | 21 | NULL |
+-------------------------------------------+-------------------+----------------------------+-----------------------+----------------------------+----------------+-------------+--------------+----------------------------+----------------------------+------+-------+--------+------+------------------+
So, if you create a form that basically mimics the columns in your target SQL table - eg uses the same instance values as your SQL column names, then it will likely be a relatively simple matter to map the resulting ODK Aggregate SQL table, containing all the submitted form instance results, to your own SQL table definition. But, as you can see in the example above, ODK adds a bunch of custom fields in its form instances (eg _MARKED_AS_COMPLETE_DATE), so you'll never be able to get ODK Aggregate to write directly into your target SQL database table [at least not without a lot of hacking of the Aggregate code].
So I might recommend making a form that mimics your SQL table as best as possible, use ODK to collect your data out in the field, and then do a simple export-remapping-import of the resulting ODK Aggregate SQL table into your own. This could probably even be automated.
Note, ODK Aggregate already supports exporting submitted records as CSV (or JSON), which should be trivial to import into your own (SQL?) database, if you dont want to deal directly with the ODK Aggregate MySQL database itself.