Create an Output Table with the CreateOutputTableFromView() Function
Often the result of a business process is the creation of a database table that contains the resulting data of your process. CreateOutputTableFromView() creates an output table in the business processor database based on a view in the application database. A view is a virtual table that consists of columns from one or more tables. The CreateOutputTableFromView() function utilizes the columns defined within the view to create the structure/columns of the table. The table is created with no data. After it is created, you can populate the table with data within your business processor code.
The function is overloaded, which means the function has more than one definition. The table name has a suffix containing the primary key of the current business process status. The length of the table name should not exceed 128 characters.
The CreateOutputTableFromView() function accepts a ByRef array of TableColumn classes as a parameter. Since this parameter is marked as ByRef, your business processor code can pass the "returned" array of columns into the base class's CreateFieldsList() function, which builds a comma delimited list of fields. The comma delimited list of fields can be used within a Transact-SQL data manipulation statement, such as an INSERT statement, to populate the table with data.
Below is a table with a description of the parameters for the version of the CreateOutputTableFromView function that we are using in the code sample below.
|
Parameter Name |
Description |
ByVal/ByRef |
|---|---|---|
|
viewName |
The name of the source view to be used to populate the table fields |
ByVal |
|
tableNamePrefix |
The prefix of the table name to be created. Table name prefix will be concatenated with the ID of the current business process run. The length of the table name should not exceed 128 characters |
ByVal |
|
tableKey |
Used to uniquely identify the output table when a business process creates multiple output tables in a single process run. |
ByVal |
|
tableColumns |
A ByRef array of TableColumn classes that make up the output table. In the example below this collection is passed to the CreateFieldsList function which returns a string of column named used within the dynamically generated Transact-SQL. |
ByRef |
Below is a code sample from the sub-class which inherits from AppBusinessProcess. Note where we override the StartBusinessProcess() function. In the blue highlight, we pass the name of the view. The view name is derived from the Output Format parameter. The function returns the name of an output table named USR_INVENTORYPROCESS_<GUID> table name. The value "USR_INVENTORYPROCESS" is used for the tableNamePrefix parameter. Within the base class's CreateOutputTableFromView function, the BusinessProcessStatusID value is concatenated to the end of the table prefix.
Example: USR_INVENTORYPROCESS_03aab75b_798a_404b_962b_63a4f0ddcc10
Figure: Create the output table using the columns defined within the "Output Format" query view