Overview

Beginning in version 12.0 of the Mobile Impact Platform, a new way to export data has been added. Titled "Automatic SQL Export", this feature is available from the Results tab of the NextGen Designer. Automatic SQL Exports are designed to automate the export of form data to standard SQL tables for the purposes of easy reporting. The form designer need not create tables or structure within the SQL database nor map fields from the form to it. This article outlines how to setup and configure automatic SQL exports and what the data within those exports looks like after export.


Data Connections

The first thing that must be setup is a Data Connection. Data Connections are administered through the Mi-Enterprise Middleware via the "Data" tab of its admin interface. A Data Connection defines a database where the exported form data will be sent. The screenshot below shows an administrator configuring a data connection:

The fields shown above are as follows:

  • Name - This is the name of the connection as seen by a form designer. Note that if you are provisioning multiple environments (e.g. development, test, and production) then you should create a data connection with the same name on each. The connection string can differ on each in order to target different SQL databases, but the form will be bound to the data connection name.
  • Connection String - This is the SQL connection string used for data exports. If you are using SQL authentication, the username & password should be specified here. If you are using Windows authentication, then the identity running the Mi-Enterprise Middleware Server will be used (typically IISAPPPOOL\MFS). In either case the user must have sufficient permissions to create database structure and insert/update data within the database.
  • Data Connection Created / Modified - Shows when this data connection was initially created and most recently modified and by whom
  • Active - Only active data connections will be provided to form designers.
  • Groups - Form designers will only see data connections that are provisioned to group(s) of which they are a member.


Form Design

Once data connection(s) have been setup, a form designer may navigate to the results tab, check the Automatic SQL Export box, and select the data connection for the form as shown below:


Data Structure

Once data has been submitted to the server, table structure a data rows will be created automatically. In the case of the example form above in addition to fields such as establishment name and address, two grids had been added, one to capture violations and one to capture temperature readings. As such three tables were created for this form, one for the main form's data, and one for each grid as shown in SQL Management Studio:



Within the tables, columns are created for each field (main table) and each column (grid tables) as shown below:



Note that the data types of columns is set automatically based on field type as can be seen from the Date, TimeIn, TimeOut, and Score fields on the main form. The Correction Date column is typed based on setting the grid column's data type property in script code as shown here:


 _form.gridColumnData("Violations", ["Item Number", "Violation", "Correction Date"]);
 _form.gridColumnProperties("Violations", 2, {DataType: "Date"});


Field data types may also be manually changed via the field's properties as shown below:




Changes to Forms & Fields

If a form's name changes, the table(s) associated with that form will be renamed upon subsequent form submission. If fields are added to a form, new column(s) will be created within the associated table. If fields are removed, columns are not removed from the table. Renaming a field will create a new column, but will not remove the previously existing column. 


Metadata

An additional table that may be of interest is the __Session__ table. This table provides information about all submitted form sessions, the user that submitted the session and the date of submission. This can be used to create reports about user productivity. An example of this table is shown below:



A video showing this form in action is available below: