Skip to main content
InSource Solutions

TN - 1269 WorkTasks - Updating Form Information Using SQL Stored Procedure

Description

 

This article from InSource shows...

  • Author: Devin Hepburn
  • Published: 06/23/2022
  • Applies to: AVEVA WorkTask / Skelta BPM

Details

Forms in WorkTasks will often connect with SQL Databases.  For repeat procedures, it is beneficial to generate a Store Procedure to handle those transactions.  This example will show how to interface with those store procedures and return information to the form dynamically.

1.Store Procedure Generation:

  • This simple stored procedure accepts two parameters (@in1 and @in2).  It adds those together and returns the sum of those two numbers as an output.  Our goal for this exercise will be ingesting those two numbers VIA a worktask form and update another box with the output.
    • SP.png

2. Create WorkFlow

  • We will create a WorkFlow that maps inputs from the form to variables within the workflow.  It will then pass those into the store procedure as inputs and store the returned integer into a variable that be passed back into the form.
    • Create Input XML Variables
      • XML Variable Creation.png
    • Create WorkFlow
      • WorkFlow.png
    • Set the variable SCHEMA for stored procedure.
      • Query Builder.png
    • Configure the BlockingOutput that will be used to pass data back into the form.  Notice here that the XML Variable "Sum" that was created in the previous step has a table/column selection due to the way the Stored Procedure feeds the information back into worktasks.  We will be selecting the only column here for this example.
      • BlockingOutput.png

3. Create Form

  • This form will invoke the WorkFlow we just created after the two inputs have been populated.  The returned value will be passed into the third numerical control.
    • Add Controls.  This example has 3 numerical controls and 1 control to invoke the workflow.
      • Form.png
    • Configure InvokeWorkflow Control.  Here, we will select the workflow that we just created and the input XML variable that we generated in the start properties of that XML.  This will allow us to map our form variables to the workflow variables.
      • InvokeWorkflowSettings.png
    • Configure WorkFlow XML Mapping.
      • InvokeWorkFlowMapping.png
    • Script for Post Workflow Execution.  This will allow us to update the third numerical control with the BlockingOutput of the workflow.
      • ScriptsSheet.png
      • Click on Post-Workflow Execution to Edit Script.  Use the following.
      • Script.png

 

  • Was this article helpful?