Entity Data Management is a feature in FlowForma that allows you to connect to a Microsoft SQL database and create entities to store FlowForma form data. Using Entity Data Management, you can send and receive data between forms and a SQL database without creating SQL stored procedures or relying on the Execute SQL stored procedure rule.
It supports create, update, get, and delete operations.
Creating a connection
Access FlowForma settings, click Global Connections, and scroll to the EDM connections section.
You will need to enter the following details to create a connection:
Server: the server where the database is hosted
Database: the name of the database where the FlowForma entities will be created
SQL user name: the name of an SQL user with owner access to the database, since tables and relationships are created during the setup process
Password: the password for the SQL user above
Database components
When the database is created, whether a FlowForma database or a custom database, the following items are added:
- Database table dbo.Sys_Flows
- Database table dbo.Sys_Forms
- Relationship between dbo.Sys_Forms and dbo.Sys_Flows
dbo.Sys_Flows
This table contains details of the FlowForma flow that was used to generate forms that use the entity data management database.
ID: a sequential number starting at 1. The first time a flow uses the database, a new record is added to the entity and the next sequential number is used.
SubscriptionID: the subscription for the FlowForma instance being used
Title: the title of the flow
AppUrl: the URL of your FlowForma site where the forms are being generated
dbo.Sys_Forms
When forms are created, the entity is updated as follows:
ID: a sequential number starting at 1. Each time a form that uses the entity data manager is created, an item is added to this entity and the next number in the sequence is used. In effect, this is the entity form ID.
FlowID: the unique ID of the FlowForma flow that was used to generate the form
FlowFormaFormID: the unique ID of the FlowForma form created; this is used in the FlowForma site
Title: the title of the form created, a combination of the form prefix (set in the flow definition, defaulting to Form) and the unique form FlowFormaFormID
Relationship
A relationship between the two entities is also created:
Sys_Forms.FlowID to Sys_Flows.ID
Creating entity
Once you have connected to your database, the Entity Data Manager is enabled in the FlowForma Flow Designer.
Open the Flow Designer. You will now see the Entity Manager icon on the flow-level toolbar.
You will now see the Entity Data Manager panel. The panel defaults to “Add entity”, but there are also options to update an entity and delete an entity.
Entity Name: enter a unique entity name; it cannot include spaces, and this will be the name used when the entity is created in the database
Entity fields: displays all the steps in the flow with all the questions contained on those steps
The first step is expanded by default. To see the questions on other steps, click the + sign next to the step name to expand it.
Select the questions you want included in your entity by checking the checkbox next to the question.
Supported question types and database properties
The following FlowForma question types are currently supported by the Entity Data Manager, along with the database datatype used for each.
| FlowForma question type | SQL datatype |
|---|
| Single line of text | nvarchar(MAX) |
| Calculated | nvarchar(MAX) |
| Choice | nvarchar(MAX) |
| Date and time | datetime |
| File upload | nvarchar(MAX) |
| Lookup | nvarchar(MAX) |
| Multiple lines of text | nvarchar(MAX) |
| Number | Float |
| Person or Group | nvarchar(MAX) |
| Yes No | bit |
Fields added to the entity must have unique names, so questions you want included in the entity must have unique names.
Any supported question in the flow can be included in an entity. You can also create a number of different entities from one flow.
When you have selected all the questions you want to include in your entity, click Save. If the entity is created successfully, you will see the pop-up below.
Your entity has now been created. The following entities have been created in the database:
- dbo.Entity_EntityName (the entity name being the Entity Name you have just entered, in the example above this was Customers)
This entity contains a column for a unique entity ID and columns for all the questions selected above.
- dbo.FormEntity_EntityName (the entity name being the Entity Name you have just entered, in the example above this was Customers)
This entity has relationships to both the Entity_EntityName and dbo.Sys_Forms entities, as well as columns that retain details of form creation and modification.
Business rule
For convenience, a step-completed Execute EDM operations - Create new entity item business rule is added to the last step of the flow that contains questions included in the entity.
All the items selected when you created the entity are included, with flow questions mapped to the database entity fields.
This rule may not meet your business requirements. If not, you can edit, remove, or disable it as required.
For more information on the Execute EDM operations business rule see Execute EDM operations in the business rules section
Update entity
Update entity allows you to add and/or remove questions from an existing entity.
Open the Entity Data Manager from within FlowForma Settings. By default, the editor opens in Add entity mode; select Update entity.
The editor now displays a drop-down that contains all the entities present in the database.
Select the entity that you require.
The Fields section displays all the form questions currently included in the entity, each with a checked checkbox next to it.
The Add field section lists all questions contained in the current flow.
Add an item
To add an item that isn’t included, check the checkbox next to the question you want added. It will now be added to the entity, and a new column will be added to the table in the database.
Remove an item
To remove an item from the entity, uncheck the checkbox next to a question included in the entity. The column in the database relating to this item is retained, but no further items will be added to it.
Using the Customers entity as an example, the entity before any changes is as below.
Open the Entity Data Manager and add or remove questions as required.
In this case, the questions ‘Address line 2’ and ‘Contact name’ have been removed, and ‘Credit limit’ and ‘Added to accounts receivable’ have been added:
Click Save to confirm the changes. The following pop-up is displayed to confirm the changes.
In the database, the entity dbo.Entity_Customers is now as below.
[Credit Limit] and [Added to accounts receivable] have been added to the database. ‘Address line 2’ and ‘Contact name’ are still in the entity, but no further updates will be possible.
Delete entity
Enter the Entity Data Manager from the FlowForma settings panel.
When the editor opens, select Delete entity.
Select the entity you wish to delete from the drop-down, then click the Delete button at the bottom of the screen.
The pop-up below is displayed to warn that deleted entities cannot be restored. If you wish to continue and delete the entity, click OK.
Another pop-up is displayed confirming the deletion.
In the database, both the dbo.Entity_EntityName and dbo.FormEntity_EntityName entities are removed and will no longer be available.
Care should be taken when selecting Delete entity: the database tables relating to that entity will be removed, and any data stored in those tables will be lost.
