This article will demonstrate using Archestra Workflow to read data from a simple SQL Server database and show the data within a data grid on a Workflow Form.
- Author: Michael Walker
- Published: 10/22/2014
- Applies to: Archestra Workflow
How and When to Use this Guide
Use this guide as a starting point when developing Workflow Forms that need access to an external database system. Note that the software used here is Archestra Workflow 2014 and Microsoft SQL Server 2012.
1. Validate the data in the external database system
Prior to configuring Archestra Workflow take a look at the data within the SQL Server database. Even write a small query that returns the data in the format needed by the workflow form. The figure below queries a database named WODB and returns WorkOrders. This is the database and data that will be used within the Workflow Form.
2. Create the database connection in Archestra Workflow
Connect to the Archestra Workflow Enterprise Console. From the menu selection choose Settings - Database Connections.
Create a new connection lookup by clicking the New menu.
In the new database connection, uncheck the option for "Connect to repository datasource". Fill the rest of the required options out to connect to the external database. In the example below we are using the WODB using SQL Server Authentication. Do not forget to test the connection before proceeding.
3. Create a lookup List
Now that a database connection is available, a lookup list needs to be created. A lookup list is essentially a result set from a database query that will later be used within a data grid configuration. Click on Settings -- Lookup Settings.
In the Lookup Settings menu, click on New -- Database.
Complete the New Database Lookup menu options. Use the database connection defined in step two. Note the query used here is the same as the one outlined in SQL Server Management studio from step 1. Be sure to validate and view the data prior to saving. The figure below shows the completed lookup list configuration.
4. Create the Grid Configuration
Now that the lookup list is created, the next step is to create a Grid Configuration. The Grid Configuration defines the data source for populating a Data Grid control within a Workflow Form. Click on Settings -- Data Grid -- Grid Configuration.
Click on New - Grid Configuration
Within the New Grid Configuration configure the options appropriately. Choose the Data Source to use the Lookup Configuration created in Step 3. The figure below displays the necessary configuration.
5. Create and Design the Workflow Form
Now that the grid configuration is created, the workflow form can be developed. Create a new form.
Name the form WorkOrderForm and save. Continue into the forms designer.
In the forms designed, add the Grid control.
In the properties of the Grid Control, configure the appropriately. The figure below shows the necessary configuration. Note the configuration name is the Grid Configuration created in step 4.
Use the form preview capabilities to see the data in a browser, or a mobile device.
Confirmation of Success
If data is being displayed from the external database inside an Archestra Workflow Form, the necessary steps have been completed. The next step will be to use the form control within a Workflow. Lookout for another article that demonstrates this capability.