Table Updates Activity
The Table Update activity can be used to perform simple database operations against a single table using model information. Although not as powerful as the VBScript activity which allows the user to literally write code, the Table Update permits the user to avoid actually programming this functionality into a Workflow model.
Any BusinessPlus table can be defined for inserting / updating and the user can assign values from a variety of Workflow model instance data, including values, variables (data elements), list items (SQL Activity), and table information (table-triggered models). Business rules are applied as a result of the insert or update for that particular table. Errors are logged and can be displayed to the user. As with the VBScript Activity, an error in the business rules causes the activity to end in an error condition.
Properties
The Who section of the Workflow Activity settings is ignored for this activity type. The Insert / Update request will be executed as the user defined in the Connection Settings, App User, Workflow field. In order to execute the table update / insert as another user, BusinessPlus recommends using a C# (C-Sharp) activity.
Pressing the Settings… button underneath the Action control in the Workflow Activity settings dialog box to display the Table Updates dialog box.
Subsystem: This field defines the BusinessPlus subsystem from which the table is selected. When the user selects a new subsystem, the table combo box is repopulated with the BusinessPlus tables for that subsystem. In addition, the Assignment table is updated with the properties for the initial table in the combo box.
Table: This field defines the BusinessPlus table to be updated or inserted as a result of the execution of this activity. When the user selects a new table from the combo box, the Assignment table is repopulated with the properties for the selected table.
Event: Defines the event that will occur for the above table as a result of the execution of this activity. Currently, only UPDATE and INSERT are supported.
- INSERT: If a table is being inserted, the unique key property is not a required field. However, any key property is (i.e., PENameMaster.PeId).
- UPDATE: If the target table is being updated the unique key property requires the assignment of a known table record unique key from the Workflow Instance record data. This key is assigned from the Workflow Instance record data (i.e., table data, list data, data element...) and the assignment can be made from the unique key(s) of table data (i.e., POIItemDetail), from item(s) in an SQL List (i.e., poi_item_dtl.unique_key), from a variable data element (i.e., "UniqueKey") or even a hard coded value (i.e., not recommended). In the case of table data or SQL List data, if multiple occurrences are seen at execution time, each unique key found will result in that table being updated.
Delete Data: The Delete Data selection is used to delete a property assignment. When a property assignment is selected, selecting the "Delete Data" button causes the property assignment to be deleted.
Assign Data: The Assign Data field is used to make a property assignment. When a property assignment row is selected, selecting the "Assign Data…" button will result in the Instance Information dialog box appearing. The user can then select a source for the value to be assigned to the property. The source can be a value, variable, list item, or table property from the model instance information depending on how the model has been defined. Once the source has been selected, press the OK button and actually make the property assignment.
Assignment Area: Contains the properties for the table currently selected along with possible assignments. Properties can be assigned values by importing model data.
Property: This column represents the table property name for the table selected.
Type: This column represents the type of assignment that has been made to the property. The possible values are "Value," "Variable," "List" and "Table."
Table/List: This column represents the table or list name of the assignment if the source is a list item or table property from the model instance information.
Assignment: This column represents the source of the assignment for the property. This can be a value, variable name, list item, or table property. Note: This is a read only table. The editing of property assignments can only occur using the "Delete Data" and "Assign Data" buttons. Only properties that have assignments will be affected during the inserting or updating of the table.
Execution
When the Table Update activity is executed within a Workflow model, the BT20 object that represents the table being updated or inserted is created first. Then field assignments are made, using the specified Workflow model internal data defined in the properties dialog box. If any of the information is not available at run time, the activity fails with an error status.
Once the fields are updated, the action is attempted by the Workflow Engine (insert/update/delete). If, at any point, the action fails due to a business rule or SQL statement error, the activity fails with an error status and turned over to the Error Handler, if defined.
Table Data
When a Workflow model is table-triggered, the information on the triggering record along with related table information is also made available to the VBScript Activity. A BusinessPlus Table Data Object is passed to the VBScript activity as a global variable object called "Source." The Source object represents the table trigger itself and properties from the table trigger are retrieved in the following manner.
Dim PRNumber
PRNumber = Source.PrNo
In addition, the Global Variable Object "Source" contains two calls that allow the user to access additional related tables and their properties. They are "Count" and "Item." The method call "Count" allows the user to determine if any related table information exists and if so, how many are there. The method call "Item" allows the user to access a particular record in general or a specific property of the table. The following is an example of the logic that would be required to access the quantity of each PR Detail record within a purchase request:
Dim TotalDetailCount, TotalQuantityRequested, Index
TotalDetailCount = Source.POIItemDetail.Count
TotalQuantityRequested = 0
For Index = 1 to TotalDetailCount
Dim Quantity
Quantity = CDbl(Source.POIItemDetail.Item(Index).Qty)
TotalQuantityRequested = TotalQuantityRequested + Quantity
Next
There is no limit to the amount of associated record data that can be included in the instance record and accessed by a VBScript. The user can reference as far down as the user wants. However, the user must make sure that each parent table exists before attempting to access the next one. Each successive table object has the "Count" and "Item" methods attached to it.
Dim Key
Key = Source.POIItemDetail.Item(Index).PONEnDetail.Item(Index2).GlKey
The above example will work as long as "Index" points to a valid POIItemDetail Record and "Index2" points to a valid PONEnDetail Record nested under the POIItemDetail record being referenced.
Table Relationships
When a Workflow model is defined as table triggered, the user defines a table (i.e., POPPvDetail) and event (i.e., UPDATE) in the Model Properties Trigger Tab in the Workflow Designer. Once the Workflow model becomes active, each time a record on the associated BusinessPlus page satisfies the event, the information in the table record will be passed to the Workflow model and processed according to the defined conditions in the Workflow model.
Unfortunately, most of the time the information in the table trigger record is not sufficient enough for the Workflow model to make complex decisions on. An example would be a PR record lacking the detailed information that directs the Workflow model in making the correct Task List assignment based on detailed information. Therefore, Workflow defines tables and any associated information along with their links to the table trigger record in an XML Document that resides on the Web Server in the /inetpub/wwwroot/ifas7/xml/schema directory. This file is loaded and cached every time the Workflow services are started. When a Workflow Instance is processed and information loaded, not only is the table trigger record information loaded, but so is all of the associated information as defined in the DBCustom.xml file. This information is also loaded into the Workflow Designer when a table trigger is defined for a Workflow model. This provides the user the ability to define conditions not only based on the table trigger information, but also any pertinent information that might exist.
The following is an example of the DBCustom.xml file layout including a table relationship definition for the PE Name Master table. Included is a description of the nodes and attributes.
<table_schema hfs="0007" xmlns="x-schema:TableRelationshipSchema.xml">
<table id="pe_name_mstr" object="BT20.PENameMaster">
<key prop="PeId"/>
<table id="pe_addr_dtl" object="BT20.PEAddrDetail">
<key prop="PeId" parent_prop="PeId"/>|
</table>
<table id="pe_assoc_dtl" object="BT20.PEAssocDetail">
<key prop="PeId" parent_prop="PeId"/>
</table>
<table id="pe_log_dtl" object="BT20.PELogDetail">
<key prop="PeId" parent_prop="PeId"/>
</table> <table id="pe_peprod_dtl" object="BT20.PEPeprodDetail">
<key prop="PeId" parent_prop="PeId"/>
</table>
<table id="pe_text_dtl" object="BT20.PETextDetail">
<key prop="PeId" parent_prop="PeId"/>
</table> </table>
</table_schema>
Table_schema: This is the root node of the XML Document and references the schema that defines the acceptable nodes and attributes within the XML Document. All child nodes to the table_schema node represent individual table trigger relationship definitions. In the above example, the included table relationship definition is for the PENameMaster table.
Xmlns: This attribute defines the schema to be used when defining the XML Document. This file is located in the same directory as the DBCustom.xml file.
Table: This node defines the table being referenced. The initial table node will always define the table trigger record. Nested table nodes are used to define the associated tables.
Id: This attribute represents the Data Base name of the table. This is a required field since it is used to map the table (pe_name_mstr) to the internal object that represents the table (i.e., BT20.PENameMaster).
Object: This attribute represents the internal object name of the table. This is a required field since it is used to locate the internal object.
Key: This node defines an individual link between the parent and child tables. Each associated table that is included in the table relationship definition must contain a child key node for each and every property that links the parent and child table records. This information is used to locate and load the appropriate associated information when processing a Workflow Instance. Without these links correctly defined, the Workflow model cannot locate the correct associated information or can even incorrectly load unrelated information. Therefore, the links are critical in the table relationship definition. In the case above there is only one link per associated table and it is the Person/Entity ID field for both tables.
Parent_prop: This attribute represents the property of the parent record. Its value will be compared to the value in the child record when locating and loading the associated records. In the above example the parent_prop values map to the PENameMaster table.
Prop: This attribute represents the property of the child record. This property will be referenced when locating and loading the associated records.