Integrating an External Database with a Flow

Last Updated: 05/02/2018 Introduced in Version: 2.0

Database entities represent external databases which can be integrated with a flow. To add a database within the portal, navigate to the folder System > Designers > Integrations > Databases, click the Create Connection button, and supply the necessary details. Once a database is introduced, components to query, insert, or update data to or from the database within the Flow Designer can be used. They are found under Integration > Integration Database > [Database Name] > [Table Name].

Example

Our example flow will access a local database called Basic, retrieve all of the records from the table MockData, and display them in a data grid form.

 

2016-12-16_104618

 

Begin in the portal by navigating to the folder System > Designers > Integrations > Databases. Click the  Create Connection button.

 

2016-12-16_092044

 

 

In the resulting Database Integration pop-up, define the details of the connection. Give this connection the same name as the database to integrate – “Basic.”

 

2016-12-16_091819

 
 

Then, click the Test Connection button to see if we can connect to our database with these details. If the platform can connect to the database, a message appears in the lower left corner of the pop up. Click OK to continue.

 

2016-12-16_091924

 

Next, Navigate to a Designer Folder.  Select Datatypes/Integrations > Database Integration > Add Table

 

 
2017-01-19_092213
 
 
In the resulting pop up, select the desired Table.  In our case, we choose dbo.MOCK_DATA.
 
 
2016-12-16_093028
 
 
By default, all fields and all components are enabled to generate for each selected table. To specify only certain fields, click on the Browse button in the Fields field and deselect the undesired fields. Additionally, to specify only certain components to generate, scroll right and deselect the undesired components. Select which field to use as the Table Key.
 
 
2016-12-16_094432
 
 
Click Next.  You are given the option to Define Flow Steps.  By Defult, all are selected.
 
 
2016-12-16_094652
 
 
Click Save.
 
Our Basic database entity is complete and ready to be integrated into the example flow.
 
 
2016-12-16_094831
 

Begin building our example flow in a Designer Folder by clicking the Create Flow button.

 

2016-12-16_095121

 

In the resulting pop-up, name the example flow “External DB Flow” and click OK to create it.

 

2016-12-16_095314

 

First, it is necessary to retrieve all records from the MOCK_DATA table. This is done using the component Get all from dbo.MOCK_DATA. Find this component in the Toolbox panel, under the category Integration > Database > Basic (Database) > dbo.MOCK_DATA (Table).

 

2016-12-16_101654

 

Next, add a form in which records from the MOCK_DATA table will be displayed. From the Toolbox panel, expand Forms [Interaction] and drag and drop the [Pick or Create Form] component into the workspace.
 
 
2016-12-16_101738
 
 
Next, we will connect each step’s outcomes as follows:
 
 
2016-12-16_101852 
 
 
Next, build the form. From the form Properties panel, click on Pick or Create Form. Name the form DisplayMockData and click OK.
 
 
2016-12-16_102115
 
 
Make sure the form has at least one outcome, such as an OK button.
 
2016-12-16_102242
 
 
Expand the Data > Data Grid category in the form Toolbox and drag a Data Grid component into the workspace. In the properties for the data grid, configure the Input Data > Type as the data type of the Mock Data table, since that is the type output by the Get all from dbo.MOCK_DATA step. Note that you can right-click the form in the flow and browse the data upstream of the form; this is a way to check data type.
 
 
2016-12-16_102530
 
 
In the Input Data > Data Name field for the grid, enter MockData. Save and close the form.
 
In order for the form to display the records collected by Get all from dbo_Product, its inputs must be mapped to the Get all from dbo_Product step’s outputs.
 
Click on the form and in the Properties pop up and click the link Edit Input Mapping.
 
To map Get all from dbo.MOCK_DATA‘s outputted collection of MOCK_DATA_Result objects, drag its anchor to our form’s sole input Mock Data.
 
 
2016-12-16_103155
 

This completes our mapping; click OK to close the Mapping Editor. Connect the output path from the form to the End step.

This completes our flow. We can test it by clicking the link Debug Flow at the top of the Flow Designer.

In the debugger, our flow displays a form grid containing all of the records in the Basic database’s MockData table.
 
 2016-12-16_103434

 

Additional Resources