Alternate Access Pattern For Table Integrations

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

This tutorial demonstrates how to use Alternate Access Pattern for database table integrations. Alternate Access Pattern generates a step that can be used in the workflow to retrieve information from the integrated table based on input fields that are defined at the time of Alternate Access creation.

 

Example:

Note:

1) Go through the following document to avoid SQL Server Database Permissions errors: SQL Server Database Permisions

2) The following link will guide you through the same functionality by writing a SQL Query: Parameterized Queries

3)  This example assumes the Northwind database has been integrated with the Portal. For your own purposes, you can use your default Decisions database or you can integrate with another database. For more information on integrating a database, see Integrating an External Database with a Flow.

In this example we will add table to the database that we are already integrated with. Then, we will create an Alternate Access to the Table. Finally, we will test Alternate Access Pattern in the Flow.

We navigate to the System > Integrations > Databases > Northwind. Then, we click Add Table on the Folder Actions Panel.

 

2017-01-06_130119

 

Alternatively, in any designer folder we navigate to Datatypes/Integrations > Database Integration > Add Table.

 

2017-01-17_120819

 

In the Add Table pop-up window we pick available table from the Table Name dropdown list.

 

2017-01-06_130319

 

Then, we pick Table Fields that will be available for this integration, Table Keys, and Steps to be generated by the System for working with this table. Click Ok to save integration.

 

2017-01-06_1343352017-01-06_134500

 

Next, navigate to System > Integrations > Databases > [Database Name] > [Table Name].   We select our Table Integration and select Manage Additional Access action.

 

2017-01-20_111502

 

In the resulting Folder we click Add Access Pattern button on Folder Actions Panel.

 

2017-01-31_154838

 

In the Add Access Pattern configuration window we Name our Access Definition and pick what Fields should be Inputs for the step that will be generated by the System. Click Ok to save and continue.

 

2017-01-31_155042

 

Our Additional Access Pattern is created. The step that was generated in this case will work as a query for the Customers table that should pull all customers where Customer.country equals Input to the step. We can close Northwind_Customers Folder.

 

2017-01-06_135836

 

Next, we navigate to a Designer Folder and click Create Flow from the Folder Actions Panel.

createFlow

 

In the resulting pop-up window we Name the Flow and click Create to proceed to the Flow Designer.

 

2017-01-06_140828

 

In the Flow Designer we add Show Form step from Favorite Steps category. This Form will serve to grab user’s input.

 

2017-01-06_140926

 

In the resulting window we Name the Form and click Create to proceed to the Form Designer.

 

2017-01-06_141018

 

In the Form Designer we simply add Label, Textbox, and Button to the workspace. This Form asks user to input Country and submit to display results on the next Form. We can save the Form and close Form Designer.

 

2017-01-06_141158

 

Then, in the Flow Designer we add our AccessCustomersByCountry step that was generated by the Additional Access Pattern from the Integrations > My Integrations > Database > Northwind (database) > dbo.Customers (table) category.

 

2017-01-06_145110

 

On the Done outcome from the AccessCustomersByCountry step we add another Show Form step from the Favorite Steps. This Form should be designed to display the result of AccessCustomersByCountry step.

 

2017-01-06_145433

 

We Name the Form and click Create to proceed to the Form Designer.

 

2017-01-06_145527

 

In the Form Designer we add a Button to the Form. Next, we add a Data Grid component from the List category. In the Input Data section enter the Data Name, then using the Type picker we pick Northwind_dboCustomers type from the Table Definition Types.

 

2017-01-06_150051

 

Data Grid creates Columns that will represent fields in our Table. We can change their order or remove any of them if we do not wish to display them on Data Grid component. This completes our second Form design. We can save the Form and close Form Designer.

 

2017-01-06_150223

 

Back in the Flow Designer we add Show Popup step from the Favorite Steps category on the Failed outcome from the AccessCostumersByCountry step. This pop-up will alert the user in the case if AccessCostumerByCountry step fails for some reason (like failed connection).

 

2017-01-06_150617

 

Next, we configure Subject and Message for the Popup step and click Add.

 

2017-01-06_150726

 

Then, we connect steps in our Flow as following. When all steps in the Flow are connected, we select AccessCostumerByCoumtry step on the workspace to configure it.

 

2017-01-06_150909

 

We locate Inputs configuration. For the Country input we use Select Value Mapping type and Pick Country data from the Textbox on the First Form.

 

2017-01-06_151022

 

Then, we select Second Form step on the workspace and locate Inputs configuration. For CustomersByCountryGrid element on the Form we use Select Value Mapping type and pick Costumers_Result output from the AccessCustomerByCountry step.

 

2017-01-06_151313

 

 This completes our Flow Design. We click Debug Flow link on the top panel of the Flow Designer to test our Flow.

 

2017-01-06_151457

 

Our first Form opens in the Debugger. We enter Country in the text box and click Show Customers.

 

2017-01-06_151602

 

Second Form opens with all Customers from USA displayed. Click Done.

 

2017-01-06_151701

 

Additional Resources