The Database Pull task enables customers to create custom, parameterized queries that execute within a process and retrieve query results into a form within the process. Once the Database Pull query results are mapped to a form with executing process all tasks within that process have access to the data. This allows to you define tasks and routing rules based on any piece of data from Integrify or external systems.
Pull data from external systems into a form within Integrify. Define tasks and routing rules based on any piece of data from external systems or Integrify itself. Save time and effort normally spent reviewing information residing outside a process and performing manual data entry.
Below is a step-by-step guide on how to configure the Database Pull task.
For example, you have a project request process where the user submits a new project request for management approval. After the initial project request form is submitted, a Database Pull (Retrieve Budget) task is configured to retrieve the current budget and year-to-date budget by cost center from your financial system:
Once the data is retrieved and mapped into the executing process, the data then becomes available to the rest of the process to base business rules, pre-fill other tasks, etc.
In this scenario a conditional approval has been added to the Process Flow. The VP Approval task will only be started if budget and project cost data submitted on the initial form are at certain thresholds. Otherwise, if a director approves the request and it does not meet this threshold the process will end.
Configuring the Database Pull Task
Once a Database Pull task has been added to your Process Flow select the Configure Task option from the context menu by clicking on the task:
The initial Database Pull Settings tab provides the necessary settings you will need to complete to retrieve the data, what database server you will be connecting to, and the type of database provider you will need to use.
In this particular scenario, a SELECT statement has been added to the Data Query area to retrieve budget information from a custom table within the database. A Query Parameter (@costCenter) is also being used that, at run-time, will be replaced with a value based on user input on a previous form.
The following is a description of each attribute for the Database Pull Settings:
The Query Parameters section allows you to capture data from the request that is executing and use it within the SQL Statement that will retrieve you list of users who will be assigned as recipients to a task.
In the SQL statement below you will notice a @costCenter syntax that has been included in the SQL Statement for this Database Pull configuration:
At run-time Integrify will use a specified value from within the request and insert it into the SQL statement prior to execution. In this case, it will be the value of the unique Cost Center from the initial form in the process.
To add a parameter to your Database Pull configuration select the Query Parameters tab:
The Query Parameters tab will list all parameters you have defined for the Database Pull. With a Query Parameter it is possible to retrieve data from a form, information about the Requester of the process, the manager of the Requester, etc.
In order for the Integrify workflow engine to utilize the query result(s) within a process it requires that the query results be mapped to a form built within Integrify.
The form can be any existing form you have already created or a brand new form specifically built for this Database Pull task.
To map the query results to a form, select the Output Mappings tab:
Based on the form selection you defined on the Database Pull Settings tab, the Output Mappings will have a list of available questions on that form to map. In the example above, only two questions were created on the form:
The assumption for this Database Pull task is that it will return only one row of data with two columns when it executes. In order to map the results of the query to the form, start by clicking in theColumn Name/Number column for a given question on the form. You can enter either the column name based on the query or the ordinal value within the SELECT.
In the example above, for the question Current, the database column CURRENT_BUDGET is explicitly defined, however, for the YTD the question on the form the ordinal value 2 is used.
Note: The ordinal values are 1 based.
You will also need to define a row number in the result set returned for each question. Click on the Row Number column for each question and enter the row the expected query result will be returned.
Note: You can return 1 to many rows of data in your query so if you would like to return 100 rows of data with one column and map all of that into the process you will need to create a form with 100 questions.
Once your mappings have been set, click Save Mappings.
Calling a Store Procedure
It is possible to use a stored procedure to perform you database actions as needed using the Database Pull task. The primary difference is the initial SQL construct and the Command Type option:
In this case, we are calling a SQL Server stored procedure called spRetrieveBudget and passing in a single parameter. Also notice that the Command Type has been changed to Stored Procedure.
If the command type is set to Stored Procedure in a db push or pull, then only the name of the proc should be in the text field. Any parameters in the list will be automatically passed by name to the proc when executed. If you are executing a proc on SQL Server, you can also set the command type to text and enter an inline execute query with parameters specified (i.e. "exec sp_myproc @p1, @p2).
Important! If you are using an Oracle stored procedure, take care on order of the parameters. If you were calling a procedure like:
spInsertSalesForecast @request_id, @sales_forecast, @close_date
Then you must create the parameters in that order in the Query Parameters screen (i.e. @request_id is at the top of the list, then @sales_forecast, etc). This is due to a constraint with the Oracle Provider. SQL Server does not have the same requirements.