FlowForma Reporting - Exporting form data to SQL database

FlowForma now allows you to set a connection to an SQL database and export form data to tables that are created and populated by FlowForma. When the reporting feature is enabled in FlowForma, a link labeled FlowForma reporting connections is displayed in the general panel of FlowForma settings. To create a connection to a database, click the link. The screen below is displayed. Click the Add SQL connection button. The following screen is displayed. AppInstanceId: This provides a user-friendly name for the site so it can be identified easily in the database, and it can be used as a filter in reports to return information from this site only
Server: The URL of the server hosting your SQL database
Database: The name of the database where you want the FlowForma tables created
SQL user name: The name of a SQL user that has permissions to update the database and tables above
Password: The password of the SQL user above
Region: You can select the region that your database is hosted in
Include Archive: You can decide to include or exclude archived forms from the extract
Include Auditing: If you have auditing enabled on your site, you can decide to create a table to store form audit data
Include Voting: If you have the Collaborative Decision Making feature enabled on your site, you can decide to create a table to store data from the voting questions used in your forms. If any of the fields are blank, you’ll see the first pop-up above; if any of the fields are invalid, you’ll see the second pop-up above. If all the fields are entered correctly, you’ll see the following pop-up: Click OK in the pop-up. If you don’t want to create the connection to the SQL database, click Cancel. The connection won’t be saved. If you want to save the connection, click Save. The connection is now displayed. If you want to edit the connection, click Edit. The connection screen is displayed with the existing connection details. If you want to remove the connection, click Remove. The connection is no longer displayed.

​

Tables created in the SQL database

When you create a connection, the following tables are added to the database you selected.

​

dbo.Forms

FormID: The unique Form Id FlowID: The Id of the flow used to create the form FlowTitle: The title of the flow used to create the form Title: The form title, a combination of the form prefix and unique form Id Status: The current status of the form, the current open step in the form, or completed Completed: Yes/No EstimatedCompleteDate: The estimated time to complete a form as defined in the flow settings. Overdue: Yes / No CurrentStepId: The id of the step that the form is currently at Modified: The date and time the form was last modified, step saved or submitted InternalID: AppInstanceID: The connection name for the app where the form was created.

​

Table relationships

​

dbo.Flows

FlowID: The unique Id for the Flow Title: The title of the flow AppInstanceID: The connection name for the app where the flow was created.

​

Table relationships

​

dbo.Steps

UniqueID: the Unique ID, a combination of the form Id and the step Id StepID: the unique step Id from the steps list FormID: the unique Id of the form where the step was used Title: the title of the step Started: the date and time the first step in the flow was saved / submitted Description: any description that has been added in the step definition to be displayed on the form Comments: any comments that have been added into the comments field at the foot of the step Visible: is the step hidden or visible Completed: has the step been completed, submitted EstimatedCompleteDate: the estimated time to complete the step set in the step definition Overdue: was the step completed within the estimated time to complete AssignTo: Who is / was the step assigned to Modified: The date and time the form was last modified, step saved or submitted AppInstanceID: The connection name for the app where the flow was created.

​

Table relationships

​

dbo.Questions

UniqueID: The Unique ID, a combination of the form Id, step Id and the question Id StepUniqueID: The unique step id, a combination of the form id and step id where the question has been added FormID: the unique Id of the form where the step / question was used StepID: the unique step Id from the steps list QuestionID: the unique question id from the questions list Title: the title of the question Code: the unique internal question code, by default a combination of a system generated step code and question code Answer: the answer that has been entered into the question AnswerFullValue: Description: any description that has been added in the question definition to be displayed on the form as a tooltip Visible: is the step hidden or visible Enabled: is the question enabled or disabled Type: the question type Question Type: the question type ParentQuestionID: Used when the question is a repeating table, as the question is the parent of the repeating table’s sub questions Modified: The date and time the form was last modified, step saved or submitted AppInstanceID: The connection name for the app where the flow was created.

​

Table relationships

​

dbo.RepeatingTableQuestions

UniqueID: the Unique ID, a combination of the form Id, step Id, question Id and repeating table row number FormID: the unique Id of the form where the step / question was used StepID: the unique step Id from the steps list Row Index: the reference to the row in the repeating table RepeatingTableRowEntityId: The Unique ID, a combination of the form Id, step Id, question Id and repeating table row number RepeatingTableEntity: a combination of the Form Id, step Id and parent question id ParentQuestionID: the id of the Repeating table question that contains all the sub questions. Answer: a concatenation of the answers to all the sub questions in the repeating table. AnswerFullValue: Column0: The answer of the sub question in the first column of the repeating table for this row in the repeating table This repeats up to Column20
StepUniqueID: A combination of the FormId and StepId that contains the repeating table question Title: Code: Description: any description that has been added in the question definition to be displayed on the form as a tooltip Visible: is the step hidden or visible Enabled: is the question enabled or disabled Type: the question type Question Type: the question type Modified: The date and time the form was last modified, step saved or submitted AppInstanceID: The connection name for the app where the flow was created. If you have selected to include audit data, an audit table will be added.

​

Table relationships

​

dbo.Auditing

If you have selected to include voting data, a voting table will be added.

​

Table relationships

​

dbo.Voting

​

Table relationships

​

Form data extract

​

First time connection

When you first add a connection and the update procedure runs, all form data from the site will be copied to the SQL tables.

​

Subsequent connection

The OData update then runs every hour, and the SQL tables are updated to reflect any changes that have been made to the form data (forms, flows, steps, questions, repeating table questions, auditing, voting).

​

Removing the connection

If you remove the connection, all data updated to this point is retained but no further updates will take place.