Fetching Data From Database to CSV FileLast Updated: 08/17/2016 Introduced in Verision: 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 Add Database 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 Integrations > My Integrations > Database > [Database Name] > [Table Name].
Example
When Fetching Data from external database in the flow it is hard to work with because of its type. There are several options designed by Decisions to handle this.
In this example we are going to fetch data from database into a CSV file and open this file in the form using CSV editor element. For this example we integrated with dbo_entity_account table from decisions database.
First, we begin in Designer Folder by clicking Create Flow to create a new flow.
Next, name the flow and click Create to proceed to the Flow Designer.
Flow Designer opens with Flow Designer Wizard on the workspace. In this Wizard we expand Integrations > My Integrations > Database > Accounts(Database) > dbo_entity_account (Table) category and select Get all from dbo_entity_account step. Click Add to add this element to our workspace.
Next, we need to create data that will hold all rows for our csv file. We add Create Data step from Favorite Steps category.
In the resulting Data Definitions window we click Add New link to configure desirable Data.
Next, we Name our Data, select CSVRowData type, and check Is List and Can Be Null checkboxes.
Our Data defined as an array of CSVRowData. We click Add to continue…
Then, we expand All Steps (Catalog) > Flow Management category and add ForEach Step next to Create Data step. With this step we will iterate through collection of Table definitions that we receive from the Database, and create CSV Rows one by one.
In the resulting Select Array window we choose entity_account_Result collection outcome from the Database.
On the Next outcome from the ForEach Step we add another Create Data step from the Favorite Steps category.
Next, we configure Data Definition for this step in the similar manner we configured Data Definition for the first Create Data step in our Flow. The only difference this time is that we Define Data for single CSVRowData type, therefore we do not check Is List and Can Be Null checkboxes.
Our Data Definition for this step should look like following… We click Add to continue.
Next to Create Data Step we insert Add Item To List Step from All Steps (Catalog) > Data > List category.
Then, we connect Done outcome from Add Item to List Step back to the ForEach Step. With Add Item to List Step selected on the workspace we navigate to its configuration and select CSVRowData type for the ListType property.
Next, we need to map data for our Single Row Data Definition. We call Mapping Editor for the second Create Data Step in our Flow.
In the Mapping Editor we use Build Data mapping type for the Row. Then we expand Item outcome from the ForEach step. This outcome contains a single row from the table with its fields. We map data from the table to the fields in our CSV Row. When finished we close Mapping Editor.
Next, we use Show Mapping Editor Option to configure Add Item to List Step in our Flow.
In the Mapping Editor for this step we use the outcome from the Single Row Data Definition as a New Item for the list, and our first array of CSV Rows Data Definition as an Original List. Note, in order for our list of Rows to accumulate all rows we use Change Value outcome type for this step and map the Outcome to the array of Rows.
Now, we have list of Rows for our csv file accumulated. We need to create a csv file using this list of rows. For this purpose we use List To CSV Mapping step from All Steps (Catalog) > Data > List > Excel and CSV category. We add this step on the Done outcome from the ForEach Step in our Flow.
Next, we use Show Mapping Editor Option for this step to configure Inputs for this step.
In the Mapping Editor we use our list of Rows as Inputs. Close Mapping Editor when finished.
Back in the Flow Designer we click options menu for List To CSV Step again. Mention, that this step has Show Item Mapping Editor. This is because this step accepts an array of Items and then builds csv file row by row. We select Show Item Mapping Editor option.
In the Item Mapping Editor we simply map _INPUT to the Items because our Inputs are CSVRowData type and do not need to be converted. Close Item Mapping Editor when finished.
Back in the Flow Designer we add Show Form step from the Favorite Steps category. This Form should display CSV file.
The Form designed as following. We use CSV Editor component to display csv file.
In the Flow Designer we call Mapping Editor for our Form to configure the File that should be displayed with CSV Editor.
In the Mapping Editor we use the outcome from List To CSV Step as an Input for the CSV Editor component on the Form. Then, we can close Mapping Editor.
Back in the Flow Designer we connect steps in our Flow as following, and click Debug Flow from the top panel of the Flow Designer.
Note that in Decisions version 3.5 and above, you’ll need to click the Test Flow link to access the Debugger.
Our Form opens in the Debugger. We can see that CSV Editor component displays fetched from database accounts as we designed. We click Done on the Form…
And our Flow execute to the end step with no errors…
