Alternate Access Pattern For Table IntegrationsLast Updated: 07/26/2016 Introduced in Verision: 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: go through the following document to avoid SQL Server Database Permissions errors: http://decdocs3x.wpengine.com/sql-server-database-permissions/
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.
In the Add Table pop-up window we pick available table from the Table Name dropdown list.
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.
Next, we click our Table Integration and select Manage Additional Access action.
In the resulting Folder we click Add Access Pattern button on Folder Actions Panel.
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.
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.
Next, we navigate to the Designer Folder and click Create Flow from the Folder Actions Panel.
In the resulting pop-up window we Name the Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we add Show Form step from Favorite Steps category. This Form will serve to grab user’s input.
In the resulting window we Name the Form and click Create to proceed to the Form Designer.
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.
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.
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.
We Name the Form and click Create to proceed to the Form Designer.
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.
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.
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).
Next, we configure Subject and Message for the Popup step and click Add.
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.
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.
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.
This completes our Flow Design. We click Debug Flow link on the top panel of the Flow Designer to test our Flow.
Note that in Decisions version 3.5 and above, you’ll need to click Test Flow to access the Debugger.
Our first Form opens in the Debugger. We enter Country in the text box and click Show Customers.
Second Form opens with all Customers from USA displayed. Click Done.
Debugger Diagram indicates that our Flow was executed successfully with no errors.
