Copy Project Results to SQL Server Action

The Project Results to SQL server action takes form responses and outputs them to a relational database structure. The action is responsible for creating and maintaining a database schema as the form is created and maintained during its lifetime.

The action can be executed at the completion of a workflow state and or the final completion of a form. When the action is executed multiple times throughout a workflow the data kept will represent only the most recent submission.

Along with the actual response data, a collection of metadata fields such as user information, submission times and in progress/completion data is stored automatically with the records too.

As indicated but it's title the action creates a copy of the submitted data, it does not substitute data kept in the SmartIQ logs or database. It saves every field captured by the SmartIQ form so that all the data is available for present and future business analysis. Multiple actions can be added to output to multiple stores.

At a high level, the database schema will comprise of a base table that has a 1:* (one to many) relationship with any repeating/nested data contained within the form. Tables are named using the Form's title combined with a unique identifier and column names are titled using the Answer Name concatenated with the answers question reference. Columns are typed to match their question type. Each record has a unique 'rowId' so to make each record unique together with a runId representing the workflow transaction in SmartIQ

Along with the tables for the actual Project Results a Schema Update table is kept to ensure that changes made to the form are reflected in the database form.

How to Create a Default Connection String

1. Go to Manage > Settings > Project Results to SQL.
2. Enter the Connection Attributes as a semi colon delimited string containing whatever settings are required, for example:
   Server=myserverlocation;Initial Catalog=MyCatalog;Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;
3. Set the user id and password for the connection string.
4. (Optional) For multiple accounts:
   1. Click the New Set button to create additional accounts. For example, Sandbox and Production.
   2. Click the Set as Default button to change the default connector set. If the previous Default was set in Design, SmartIQ will now use the new Default connector values.
5. Click Save.

📘

Permissions

Note that the user for the connection string must have permission to create and alter tables on the database (in addition to reading/writing data) in order to create and modify the database schema to match the project.

How to use the Copy Project Results to SQL Action

1. Drag and Drop a copy of Project Results to SQL action either onto workflow step or the Finish page depending on at what point along the workflow you process requires the data.

2. The Action contains only one configurable setting, the connection string to which the results should be copied. A default can be applied at the environment level or applied individually at the individual-action level.

3. Complete your form in Produce and ensure the data appears as expected in your SQL server instance.

Question Types and Database Schemas

Different question types are mapped differently to their SQL equivalent as per the table below.

📘

Notes

• Tables are automatically titled using the Forms name concatenated with a unique identifier where necessary. For Example, MyForm
• Columns are titled based on the questions answer name concatenated with the question's unique reference.
• Separate parent/tables are created for Project Fragments and Repeating Sections.
• Each child table maintains a relationship to its parent via a 'ParentRowId' column.
• Infinite levels of data is supported by the parent/child table structure. (i.e. when a repeating section is located in a repeating section).
• The various question types have unique behavior depending on the data that collect for example a Text Box vs Data Source vs Geolocation. See the table below for specifics.
• If question(s) are added to the form coinciding columns will be added to the database schema, records previous to the question's addition will have NULL values.
• If a question's datatype changes, for example from date to number. A new column will be created for the new data. the existing column will be kept but renamed.
• A separate 'SchemaUpdate' table stores the last time the schema was updated.
• Database administrators should only need to manage user access to the table, they should not need to make schema changes.
• A maximum of 1024 columns is possible for any individual table.
• Access to the data is possible only via a direct SQL Server connection