Publishing form data into server datasets
As discussed in the previous help topic, you can manually create and manage server datasets from the Your forms and datasets section of your server console's Design tab. And if you attach a server dataset to a form, that dataset's data will be available as pre-loaded data that can be pulled into calculated form fields, used to dynamically populate multiple-choice option lists, or even pre-loaded as default values for user-editable survey fields. But you might also want to go in the other direction: publish data from a form's submissions into a dataset (which could then, in turn, be attached to one or more forms).
If you publish a form's submissions to a server dataset, then any submissions received for that form will automatically add to or update the existing dataset. If you want, you can configure multiple forms to publish to a single dataset, which can then serve as an attachment to one or more other forms. Additionally, you can choose to have such workflows operate entirely offline in SurveyCTO Collect (without a connection to the Internet) when enabling offline dataset publishing. These powerful features can be used to design a wide range of workflows.
Click here to check out our webinar on server dataset workflows.To publish form data to a server dataset, first find the dataset in the Your forms and datasets section of the Design tab (or add it there, giving it a title and unique ID). Then click Publish into and select the form from which you would like to publish data.
After that, you will need to decide exactly which form fields to publish, to exactly which fields in the dataset (we call this process "mapping"). There is an Add all button to simply add all form fields at once (or, in the case of encrypted forms, all form fields that have been marked as publishable), plus an Add button to add fields one-by-one. For each added field, SurveyCTO will default to publishing to a field name in the dataset that matches the field name in the form – but you can override this default in cases where you want the column name in the dataset to be different.
Note that a field must have been relevant in a form submission for it to be published.
Format
When configuring field mapping you must choose either "wide" or "long" as your data publishing format. When you choose a wide format, a single form submission will populate a single row in the server dataset. Fields inside repeat groups will be listed with * at the end of their name and will be output so that the first instance replaces * with _1, the second instance with _2, and so on — all inside a single row of data.
Alternatively, choose long format publishing, where one form submission can populate multiple rows in a server dataset. Fields inside repeat groups will also be listed with * at the end of their name, but instead of adding a column per repeat instance, a new row of repeated data will be added per repeat instance. If you choose to include non-repeated fields, the same value will be repeated in each row in the dataset.
Long format form data publishing also has some special requirements:
- Repeated fields must all be from the same repeat group or from a parent repeat group (in the case that your repeat group for publishing is nested inside another repeat group).
- The Form field to identify unique records option (discussed below) is not optional when you choose long format, and it must be a repeated field.
If you would like to publish repeated fields from more than one repeat group on the same repeat level, you can use the indexed-repeat() function to combine repeated values into a single repeat group for publishing.
Other options
Finally, you have a few other options available:
- For each field that is mapped you can choose to have new values Replace existing values (the default), Add to have the new value added to the existing value (in the case of numeric values), or Concatenate to have the new value appended before or after the existing value using the separator of your choice. Note that concatenated lists will be truncated at the end after 255 characters, so choose to append to the front to avoid the loss of the most recently added values.
- You can indicate one of your form fields to use in uniquely identifying records in the dataset. If you do, then new form submissions will either update an existing row in the dataset – if a row with a matching value in the corresponding field already exists – or insert a new row. If you don't specify a unique identifier, then new submissions will simply publish as new rows. If you nominated a Unique ID field in your dataset's Settings, you must choose it as the Form field to identify unique records. You would likely want to use a unique identifier if you were using a single dataset to merge data from multiple forms. (This unique identifier has to be one of the form fields that you listed in the field mapping. I.e., you also have to publish whatever field is being used to uniquely identify rows.) This option is required when publishing data from repeated fields in long format.
- You can indicate one of your form fields to use as a kind of filter using Include form submissions whenever this field is 1. If you do, only submissions for which the specified field contains the value 1 will be published. For example, if you wanted to only publish data for children, you might select an "ischild" field as the filter; in your form, that could be a calculate field with a calculation expression like "if(${age} < 18, 1, 0)". When publishing in long format, the filter field must be located inside the repeat group from which fields are being published, from a parent repeat group, or from outside any repeat groups.
- You can check Publish existing data if you want to publish existing form submissions. If you don't check this option, then only new submissions that come in (after you configure publishing) will publish to the dataset.
Publishing
As form submissions come in to the server, they will be published to your datasets according to the mappings that you have configured – but there will be a brief delay. The datasets, the forms to which they are attached, and the record counts on the Export tab will only update 5-10 minutes after the most recent submission has been received. This means that, as data-collectors are actively uploading their submissions, the datasets will not update again and again and again, but will instead update only after 5-10 quiet minutes have passed. So, if you download a form or a dataset and find that it doesn't have up-to-the-minute data, please wait a few minutes for the dataset to update.
Alternatively, if you are working in a disconnected environment or need to guarantee that locally generated data is immediately available for pre-loading, consider enabling offline server dataset publishing. When enabled, all publishing that would take place on the server occurs locally on SurveyCTO Collect with no delays.
If you would like to further publish your datasets on to Google Sheets or another outside system for real-time monitoring or visualization, see Publishing server datasets to the cloud.
Troubleshooting
Careful planning and testing of form data publishing-enabled workflows should be enough to ensure that most work well. However, with more complex data collection workflows, you may need to be able to audit the updates that have been made to the contents of a server dataset. Through auditing changes to the contents of a server dataset, you may also be able to recover data that had been overwritten by subsequent dataset updates.
To audit changes to the contents of a server dataset, visit the Design tab of the server console, go to the server dataset, click Download, and then Data changes. You will have to supply at least one of the following query criteria in order to view data changes:
A date range for updates.
A unique record ID (if a Unique ID field had been specified).
A form submission's unique KEY value.
Click on Download and you will receive a .csv data export which contains a list of changes. If you need help interpreting this data, submit a support request.
Collect also has troubleshooting options available to admins. From the main Collect menu, tap on the three-dot icon in the upper-right and select Admin Settings. Again, tap on the three-dot icon from inside Admin Settings and select Manage Datasets. You will see a list of all datasets in the current workspace. Tap on a dataset to see a list of changes represented by their dataset IDs. You can select one or more changes to abandon, or you can tap on the three-dot menu again and select the option to Reset Dataset to abandon all local changes. In either case, changes will be abandoned during the device's next server synchronization.
