Insert, Update, Delete Steps For Integration With Database (3.2.0)Last Updated: 12/03/2015 Introduced in Verision: 2.0 |
When integrating with Database in Decisions it is possible to generate Update, Delete and Insert steps to interact with integrated Database from Decisions’ Flow. This tutorial demonstrates how to generate these steps and use them in the Flow in Decisions version 3.2. Moreover, this tutorial demonstrates how to display data from integrated database using Data Grid component on the Form.
Example:
For this example we will integrate with a simple database table that looks as following.
In Decisions Portal we navigate to the System > Integrations > Databases folder and click Create Connection button from Folder Actions panel.
Then, we input required information for the connection to the Database we want to integrate with and click Ok.
Our database connection shows up in System > Integrations > Databases folder. We select it and click Add Table.
In the Tables Integration window we pick a desirable Table from our database and select Steps that should be generated by the System for interactions with the database. When finished, we can click Ok to create Integration.
And our Table is being created by the System in the Database Folder.
Next, we will create a Flow where we will use Insert, Update and Delete steps against the Database we just integrated with. We navigate to the Designer Folder and click Create Flow button from the Folder Actions Panel.
In the resulting window we Name our Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we expand Integrations > My Integrations > Database > [Integrated Db Name](Database) > [integrated table name] category. Here we can find all steps to interact with the integrated Database. First, we add Delete [table name] step.
Then, we connect Failed outcome from the Delete step to the End step in our Flow. When we click on the Delete step on the workspace, Step Information Layer for this step pops up. The record that will be deleted with this step should be identified by the Primary Key ID from the table (in this case UnitID is a primary key in the integrated table). For the UnitID we select Constant Mapping type and provide desirable Value. Then, we click Add Step.
Next, we add Insert to [table name] step from Integrations > My Integrations > Database > [Integrated Db Name](Database) > [integrated table name] category on the Deleted Outcome from Delete step.
In the same manner as we did with the Delete step, we connect Failed outcome from Insert step to the End step, and click Insert step on the workspace to call Step Information Layer. Next, we click Show Mapping Editor option.
This step is able to insert an Array of records into the table. In the Mapping Editor we select Build Array option for Inserted Objects. Then for the first record (Item 0) in the Array we select Build Data, and create a Record by filling in each field with Constant Values. Click Ok to save and close Mapping Editor when finished.
Back in the Flow Designer we add Update [table name] step from the Integrations > My Integrations > Database > [Integrated Db Name](Database) > [integrated table name] category on the Inserted Outcome from Insert step.
Again, we connect Failed outcome from the Update step to the End step in the Flow, call Step Information Layer, and click Show Mapping Editor option.
Update step is very similar to Insert step. In the Mapping Editor we configure data for this step in the exactly same manner as we did for the Insert step. Click Ok to save and close Mapping Editor.
To display data from the table we need to query it first. In order to obtain data from the table we add Get all from [table name] step from the Integrations > My Integrations > Database > [Integrated Db Name](Database) > [integrated table name] category on the Updated Outcome from Update step.
We connect Failed outcome from Get all from [table name] step to the End step in the Flow, and call Step Information Layer for this step. This step does not require specific Input configuration. The only input we can configure is Limit of Records. In this case we just ignore this Input. Mention, the data type that is being returned from this step. We will use this data type for the Data Grid in the Form. Click Add Step to add final step on the Done outcome from Get all step.
Finally, we are going to create a Form to display all records from the table in the Grid. Add Show Form step from Favorite Steps category and provide this step with a Name.
In the resulting window Name the Form and click Create to proceed to the Form Designer.
Each Form should have at least one outcome. We add Button component from the Actions category in the ToolBox to our Form. Then, we replace text on the Button to “Done”.
To Display data from our table we use Data Grid component from the List category in the ToolBox.
Next, we need to configure our Data Grid component. In order to display data from our integrated table we need to configure Data Grid component to accept the appropriate Type. In this case Type should match Data Type that is coming out of the Get All step. We locate this Type in the Table Definition Types category using Type picker.
Mention that we can configure what columns we want to be displayed on the Data Grid. Next, we need to Name our Data Grid component. Additionally, we can configure the output Mode from the Data Grid component (in this case we simply use Info Only mode).
Moreover, we can add Row actions and Row Links to the Data Grid component. When we finished with Data Grid component, we can Save and Close Form Designer to return back to the Flow Designer.
In the Flow Designer we connect the Outcome from our Form to the End Step. Then, we click on the Form Step and select Show Mapping Editor option.
In the Mapping Editor we map outcome data from the Get all step to the Data Grid component Name in our Form. Then, click Ok to close Mapping Editor.
This completes our Flow. To test it we click Debug Flow link on the top panel of the Flow Designer.
Our Flow runs in the Debugger and first thing that we see is our Form. Behind the scenes our Flow does all work as we designed. Our Form displays the records from our database table with changes that were done by our Flow. We click Done…
Debugger indicates that our Flow ran with no errors…
If we look into our original database we can confirm that our Flow did designed changes to the table.
