Work out how many forms have been submitted for various projects

Slightly odd query.
We have set up an ODK service at our university.
We would like to get some metrics out about the number of submissions for each project.
We seem to get different values/columns and I'm not sure I quite follow them all:
Forms - Presume this is the number of Master XForms)
total records - Presume this is the number of currently uploaded forms?
_form_data_model ?? Old forms now deleted??

Essentially for each project we would like the total number of forms that have EVER been uploaded.

Any guidance gratefully received,

Michael

Hi Michael,


describes the DB structure. I haven't looked at the tables recently,
but try _form_info_submission_association.

Yaw

··· -- Need ODK consultants? Nafundi provides form design, server setup, in-field training, and software development for ODK. Go to https://nafundi.com to get started.

On Sun, Mar 6, 2016 at 9:36 PM, michaeledwardmarks@gmail.com wrote:

Slightly odd query.
We have set up an ODK service at our university.
We would like to get some metrics out about the number of submissions for each project.
We seem to get different values/columns and I'm not sure I quite follow them all:
Forms - Presume this is the number of Master XForms)
total records - Presume this is the number of currently uploaded forms?
_form_data_model ?? Old forms now deleted??

Essentially for each project we would like the total number of forms that have EVER been uploaded.

Any guidance gratefully received,

Michael

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en


You received this message because you are subscribed to the Google Groups "ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

There is no overall count of submitted records. If you have never purged
records from the database, then you can get the total count of submissions
for that form by counting the total number of records in its _CORE table.

Otherwise, if you are purging submissions, you would need to set up a
database trigger to increment a counter that you maintain to track when a
record is inserted (assuming you are using your own MySQL or PostgreSQL
database), and manage and track the counts through those counters.

Since each uploaded form has its own _CORE table, and these are created
on-the-fly, you would have to set up the trigger against the a _CORE table
at the time a new _form_info_submission_association table entry is written;
that record is written when all the data tables for a form have been
successfully created, and also when a revision to an existing form (e.g.,
typo corrections) has been successfully processed.

As noted toward the bottom of that page, you would start with

_form_info

for the list of form definitions uploaded to the server.

Then use that to reference

_form_info_submission_association

To get the key into the _form_data_model table (URI_SUBMISSION_DATA_MODEL).

And then find the name of the top-level (..._CORE) table in that via a
query where
( PARENT_URI_FORM_DATA_MODEL == URI_SUBMISSION_DATA_MODEL ) and looking at
PERSIST_AS_TABLE_NAME

Then you can select count(*) from that table to get the number of
submissions for that form id or set up your trigger against that table.

··· On Sun, Mar 6, 2016 at 10:06 PM, Yaw Anokwa wrote:

Hi Michael,

https://github.com/opendatakit/opendatakit/wiki/Aggregate-Database-Structure
describes the DB structure. I haven't looked at the tables recently,
but try _form_info_submission_association.

Yaw

Need ODK consultants? Nafundi provides form design, server setup,
in-field training, and software development for ODK. Go to
https://nafundi.com to get started.

On Sun, Mar 6, 2016 at 9:36 PM, michaeledwardmarks@gmail.com wrote:

Slightly odd query.
We have set up an ODK service at our university.
We would like to get some metrics out about the number of submissions
for each project.
We seem to get different values/columns and I'm not sure I quite follow
them all:
Forms - Presume this is the number of Master XForms)
total records - Presume this is the number of currently uploaded forms?
_form_data_model ?? Old forms now deleted??

Essentially for each project we would like the total number of forms
that have EVER been uploaded.

Any guidance gratefully received,

Michael

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en


You received this message because you are subscribed to the Google
Groups "ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en


You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com