Copy Project Results to SQL Server
This action requires the Analytics Module for your SmartIQ environment.
The Project Results to SQL server action takes form responses and outputs a copy of them to a relational database SQL server. 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.
The action is responsible for creating and maintaining the database schema, as the form is created and maintained during its lifetime.
Usage
Before using the Action you must first configure the Project Results to SQL Settings
Only configure this action within properties in design if you are overriding the default for a specific case
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.
At a high level, the database schema will comprise of a base table that has a 'one: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
| RowId | RunId | Client Name_q1.1 | Value_Q3.3 | Yes_q3.3 | No_q4.5 | DOB_q5.6 |
|---|---|---|---|---|---|---|
| abc123 | def456 | SmartIQ | 500000.000000 | 0 | 1 | 2000-01-01 00:00:00.000 |
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.
Question Types and Database Schemas
Different question types are mapped differently to their SQL equivalent as per the table below.
| Type | Description |
|---|---|
| Text Box | Creates a string, date or numeric field as per the question's properties. |
| Multiple Choice | Creates a column for each possible selection of a bit (Boolean) data type indicating a selection. For the single-select question, a maximum of one will be selected, whereas a multiple-selection could have many. |
| Data Source | Stores the unique identifier of the row selected. |
| Rich Text | Stores the rich text answer as an HTML string. |
| Variable | Stores the variable's answer value as a string. Note as variables are calculated from other sources by default they are configured to not save. To change this, adjust the answer file properties to 'Save by built-in Answer ID' |
| Geo Location | Stores the captured latitude and longitude as separate columns. |
Notes
- Tables are automatically titled using the form name concatenated with a unique identifier where necessary.
- 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
Updated 4 months ago