Workflow Functionality in the Issue Tracking Sample Application

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The main interface to the workflow functionality in the Issue Tracking sample application is the workflow control bar that is displayed on the Grid View and Detail Form pages. This bar, with a yellow background, displays the available workflow events defined in the database. When an event is selected for an issue, the Issue Tracking sample application displays the Detail form for that issue and changes the value of the Status field to the correct value based on the event selected.

The action toolbar in the Issue Tracking sample application automatically updates the enabled events based on the currently selected issue in the grid. This implicitly enforces the workflow logic defined by the workflow process.

For example, the workflow logic specifies that an issue can change from an Active state to a Resolved state but cannot change from an Active state to a Closed state. The Issue Tracking application communicates this state flow to users by enabling only those events permitted on the currently selected issue. As the user highlights different issues, the action bar is updated automatically.

The actions available on the Issue Tracking action toolbar are defined by the events in the workflow actions table. This table contains a row for each event available on the workflow bar. It links to the workflow table, which defines the workflow logic between state changes.

The routines for generating the workflow bar are consolidated into one Microsoft® Visual Basic® Scripting Edition (VBScript) file, Common.vbs. This file is included in the Web pages using a <SCRIPT> tag.

<SCRIPT id=CommonVBS language=vbscript src="./common.vbs"></SCRIPT>

The subroutine that does the work of generating the workflow bar from the captions in the workflow actions table is Sub BuildActionToolbar. This routine dynamically builds an HTML string that displays the workflow bar. It contains an HTML table with a <TD> element for each caption. When the string is built, dynamic HTML is used to place the HTML string into the document header section using the InnerHTML property.

The routine requires one input value, which is the name of the base table on which the workflow bar should be built. This is required, because it is possible the page could contain two different workflow bars that are acting on two different tables. In the case of the Grid View page, the strBaseTable is always "Issues."

Sub BuildActionToolbar(strBaseTable)

The routine identifies the names of the workflow and workflow actions table associated with the base table. In the case of the Issue Tracking sample application, these are IssuesWorkflow and IssuesWorkflowActions, but to keep the code generic, the routine calls to the modObjects table to find the table names.

To accomplish this, the variable oWorkFlowRS is set to a new ADO recordset object.

Set oWorkFlowRS = CreateObject("ADODB.RecordSet")

The source of the recordset object is set to an SQL SELECT command that searches modObjects for a workflow table (with a type of "WF") associated with the base table name passed to the subroutine. Note that the SELECT command does a join with itself to return only those objects that are associated with the base table name.

oWorkflowRS.Source="SELECT modWorkflow.Name FROM modObjects INNER JOIN modObjects modWorkflow ON modObjects.Id = modWorkflow.Parent_id WHERE (modWorkflow.Type = 'WF') AND (modObjects.Name = '" + strBaseTable + "')"

The recordset is executed, and the name of the workflow table is placed into the variable strWorkflowTable. This is a global variable, so after it is retrieved, other VBScript functions can use it without having to execute the same query again.

Set oWorkflowRS.ActiveConnection=oConnection
oWorkflowRS.Open
strWorkflowTable = oWorkflowRS.Fields("Name").value

The same recordset is closed and reused to find the workflow actions table (with a type of "WA"). Again, this is placed into a global variable, strWorkflowActions, so it is available to other subroutines.

oWorkflowRS.Close
oWorkflowRS.Source="SELECT modWorkflow.Name FROM modObjects INNER JOIN
modObjects modWorkflow ON modObjects.Id = modWorkflow.Parent_id WHERE (modWorkflow.Type = 'WA') AND (modObjects.Name = '" + strBaseTable + "')"
Set oWorkflowRS.ActiveConnection=oConnection
oWorkflowRS.Open
strWorkflowActions = oWorkflowRS.Fields("Name").value
oWorkflowRS.Close

A list of the workflow event captions and their desired positions must be retrieved. A SQL SELECT query is executed against the workflow and workflow actions tables to get this list.

The query does a join between the workflow actions and the workflow table to include the event associated with the action button. For example, if the event is OnCreate, then the action button will create a new record rather than edit an existing one (OnChange). The query is built dynamically, so the names of the workflow and workflow actions tables retrieved earlier are used in the query.

oWorkflowRS.Source="SELECT " + strWorkflowActions + ".Caption, Min(Event) As Event, Min(Position) FROM " + _
   strWorkflowActions + " INNER JOIN " + strWorkflowTable + " ON " +
   strWorkflowActions + ".WorkflowID = " + _
   strWorkflowTable + ".Id GROUP BY " + strWorkflowActions + ".Caption
   ORDER BY 3"
oWorkflowRS.CursorLocation = 3 'adUseClientSide

oWorkflowRS.Open
oWorkflowRS.MoveLast
numBtns = oWorkflowRS.RecordCount
oWorkflowRS.MoveFirst

All the information required to create the action toolbar is now available. Next, an HTML string is generated to represent the action toolbar.

The action toolbar is generated as an HTML table. The HTML <TABLE> tag is set to the variable codeStr. The tag names the table with the base table name appended with "ActionToolbar."

codeStr = "<table ID=" + strBaseTable + "ActionToolBar cellspacing=0 cellpadding=0 border=0 style=""font-size:" & L_WorkflowBarFontSize_Text & "; cursor:default; color:#999999""><tr id=dtnRow>"

To generate the rest of the HTML, the recordset is walked through, and a <TD> tag is generated for each caption. A loop is used to walk the recordset and add an HTML <TD> to the codeStr string for each caption.

While Not oWorkflowRS.EOF
   strCaptionName = Replace(oWorkflowRS.Fields("Caption").value," ","_")
   strCaption = oWorkflowRS.Fields("Caption").value
   codeStr = codeStr & "<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
   <td ID=btn" & strBaseTable + strCaptionName + " WorkflowEvent=" + _
   oWorkflowRS.Fields("Event").Value + " basetable=" + strBaseTable + ">"
   & strCaption & "</td>"
   oWorkflowRS.MoveNext
WEnd

After the HTML string is created, it is placed into the document using dynamic HTML. The Grid View and Detail Form pages use an HTML table at the top of the document to display the page title and the standard toolbar. Within this table is a <TD> tag with the ID of ActionToolbar. This is where the HTML string is placed and the workflow bar is displayed.

actionToolbar.innerHTML = codeStr & "</tr></table>"

The enabling and disabling of appropriate actions occurs in another subroutine. However, to increase performance, a global recordset is created in this routine with all of the information from the workflow table. This is so the enabling and disabling routine is not required to recreate a recordset (which can be a time-consuming process) every time the captions must be enabled and disabled.

set oNextCaptionsRS = CreateObject("ADODB.RecordSet")
set oNextCaptionsRS.ActiveConnection=oConnection

strQuery = "SELECT " + strWorkflowActions + ".Caption, " +
   strWorkflowTable + ".State FROM " + strWorkflowTable + " INNER JOIN " _
   + strWorkflowActions + " ON " + strWorkflowTable + ".Id = " +
   strWorkflowActions + _
   ".WorkflowID"

oNextCaptionsRS.Source= strQuery
set oNextCaptionsRS.ActiveConnection= oConnection
oNextCaptionsRS.Open

When the Issue Tracking action toolbar is displayed on the page, only those events appropriate for a selected issue should be available. All other events must be unavailable. The subroutine in GridView.htm that does this is Sub UpdateActionToolbar.

The routine requires two input values. The first is the name of the base table on which the workflow bar is based. This value is required, so the routine can update the proper workflow bar — in case there are multiple workflow bars on the same page. The second is the StatusID of the issue that the workflow bar should be updated to reflect. For example, in the Issue Tracking sample application, if the status of the issue were set to 0 for Active, then the Close and Delete action buttons would be disabled.

Sub UpdateActionToolbar (strBaseTable,intStatusID)

This routine is called whenever the current issue is changed. For the Grid View page, when the selected row in the issues grid is changed the IssuesGrid_RowColChange event is triggered.

The workflow bar is placed into a known state by disabling all the captions displayed in the bar. A caption is disabled on the workflow bar when the <TD> is set to just the text of the caption. To enable a caption, an anchor tag <A> is placed around the text.

Set oRow = document.all(strBaseTable + "ActionToolBar").rows(0)
For i = 0 to oRow.cells.length - 1
   If oRow.cells(i).children.length > 0 Then
      oRow.cells(i).innerHTML = oRow.cells(i).innerText
   End If
Next

The recordset created in the BuildActionBar routine is used to find out the valid states based on the StatusID passed into the routine. This is done by adding a dynamic filter to the recordset that limits the Caption list to only those where the State field is equal to the StatusID or – 1, which means it is valid for any state.

oNextCaptionsRS.Filter = "(State = " + cStr(intStatusID) + ") OR (State = -1)"

When that information is available, a loop checks through the recordset. For each record returned, the Caption field in the record indicates the event is available, so the caption is enabled. A caption is enabled when the text is contained within an anchor tag. One of the parameters on the anchor tag is to execute the function DoAction when the onClick event is executed.

While Not oNextCaptionsRS.EOF
   strCaptionName = Replace(oNextCaptionsRS.Fields("Caption").value,"
   ","_")
   strCaption = oNextCaptionsRS.Fields("Caption").value
   document.all("btn" + strBaseTable + strCaptionName).innerHTML = 
   "<a language=javascript onclick='DoAction(""" & document.all("btn" + 
   strBaseTable + strCaptionName).basetable & """, """ & strCaption & 
   """)' onmouseover = ""this.style.textDecorationUnderline=true"" 
   onmouseout = ""this.style.textDecorationUnderline=false"" 
   onkeyup=""if(event.keyCode==13){this.click();}"" tabIndex=0 
   style=""color:black; cursor:hand"">" & strCaption & "</a>"
   oNextCaptionsRS.MoveNext
WEnd

After the Issue Tracking action toolbar and the appropriate events are displayed, the event must be executed when it is selected. Executing events involves the following steps:

Identifying the issue and its current state and initiating the event for that issue.
Displaying the DetailForm.htm in a new window.
In the Detail Form, navigating to the proper issue and retrieving the event and current state from the Grid View.
Changing the StatusID to the next state based on the current state.
Enabling fields on the Detail Form based on the next state.

These steps are completed using a series of subroutines on the GridView.htm and the DetailForm.htm pages.

Initiating the Event

An event is initiated when the user clicks the desired action button on the workflow bar. Selecting the event executes the OnClick event for the caption, which calls the DoAction function. The parameters passed into the DoAction function identify the event to be executed.

The DoAction function lives in the main Web page that includes the Common.vbs file. For example, if you execute an event from the workflow bar on GridView.htm, then there is a DoAction function in the GridView.htm file that is executed. In addition, if you execute an event from the DetailForm.htm, then the DoAction function that is executed is in the DetailForm.htm.

Displaying the Detail Form

If the event is executed from the GridView.htm page, then the DoAction function is used to display DetailForm.htm. The subroutine in the Grid View page that identifies the current base table and the event to execute is the Sub DoAction routine. This routine sets up global variables with the information required to pass to the Detail Form and then displays the Detail Form in a new window.

Sub DoAction (strBaseTable,strDoAction)

In addition to the base table and the event to execute, additional information is passed to the Detail Form. This includes the ItemID of the current issue, so the Detail Form can navigate to the correct issue, and the current state of this issue.

The current issue is based on what record is selected in the issues grid. To get the ItemID and the current state, two custom functions return this information by querying properties of the Microsoft® ActiveX® ModHFGrid control.

intDoItemID = Grid_GetCurrentItemID()
intDoState = Grid_GetCurrentState(strBaseTable)

Another requirement is to identify which event is associated with the action button. This is required to know whether to act on the current issue or a new issue. For example, if the event is OnCreate, it creates a new issue.

This information is stored as a custom tag on the <TD> element, so to retrieve it, the srcElement (the element the user clicked) is used. In case there is no srcElement, any errors generated are suppressed, and the strEvent variable will be empty.

On Error Resume Next    'Error will occur if no srcElement
strEvent = ""
If window.event.srcElement.tagName = "TD" Then
   strEvent = window.event.srcElement.workflowevent
End If
On Error Goto 0

One standard event is called "modView." This is used by the Grid View page to signify the user double-clicked the issue to view the issue and will not be editing it. If the event is "modView" or the event is to create a new issue, no row-level permissions must be verified. However, if the user is executing an event that can modify the issue, the row-level permissions must be verified to make sure the user has valid write permissions to the specific issue. If the user does not, the event is changed to "modView."

If strDoAction <> "modView" And strEvent <> "OnCreate" Then
   If CheckWriteState(intDoItemID) = False Then
      msgbox L_NoPermissions_Message,vbOKOnly + vbExclamation,
      gDialogTitle
      strDoAction = "modView"
   End If
End If

Finally, the information required by the Detail Form is placed into a cookie that can be retrieved from the Detail Form page, and the Detail Form page is opened in a new window.

document.cookie = ("Action=" & strDoAction)
document.cookie = ("ID=" & intDoItemID)

set oDetailForm = window.open("detailform.htm","_blank","toolbar=no,location=no,status=no,scrollBars=no,menubar=no,resizable=no,width=506px,height=530px")

Initializing the Detail Form

The Detail Form makes it possible for the user to make modifications to an issue and then complete the state change by saving the changes.

Upon opening, the Detail Form retrieves information to identify what issue to display and what event to execute. If the Detail Form was opened from the Grid View page, this information is available in the cookie defined by the Grid View page. Another way this information is passed in is through the URL to the Detail Form — for example, by using the following URL:

http://servername/detailform.htm?ID=54;Action=Edit

To retrieve this information, the onLoad event for the Detail Form page first checks to see if the URL contains the extra information. This is done by parsing through the location.search property of the window. Otherwise, it checks the document.cookie property. Finally, if the information is not found in either of these properties, it defaults to an item with an ID value of 1 and the default view of "modView."

The GetValue function is a common function available from the Common.vbs file that parses through a string looking for string pair values separated by semi-colons.

strValues = location.search 
   If GetValue(strValues,"ID") = "" Then 
     strValues = document.cookie 
       If GetValue(strValues,"ID") = "" Then 
         strValues = "ID=1;Action=modView" 
       End If 
   End If

The Detail Form must have the same filter and sort order as the Grid View page. So, if the user uses the Next and Previous buttons, the Issues are displayed in the order they are listed in the Grid View page. To do this, retrieve the filter and sort order used on the Grid View page, and apply them on the Detail Form.

If IsObject(window.opener) Then
   Set oDSC = window.opener.document.all.MSODSC
   strFilter = oDSC.RecordSetDefs(0).serverFilter
   MSODSC.RecordSetDefs(0).serverFilter = strFilter
   MSODSC.DataPages.Item(0).Recordset.Sort = strViewSort
End If

When the form is initialized, the main workflow work is done in the subroutine DoAction in the Detail Form. This routine is called with the information retrieved from the Grid View page.

DoAction strAction,intState,intItemID

Completing the Workflow

To complete the workflow, the DoAction function on DetailForm.htm is used. If the workflow event was executed from an action toolbar displayed on DetailForm.htm, this function is executed initially.

The DoAction subroutine takes three inputs: the event to be executed, the current state of the Issue, and the ItemID of the issue.

Sub DoAction (strAction, intState, intItemID)

If the event to execute is a workflow event, meaning it is not "modView," the next state for the issue must be identified. This is available from the workflow table based on the current state of the issue and the event to be executed.

A query is executed against the workflow and workflow action tables to retrieve this information.

If strAction <> "modView" Then

   set oStateRS = CreateObject("ADODB.RecordSet")

      oStateRS.Source = "SELECT " + strWorkflowTable + ".Id, 
      Status.Status, " + strWorkflowTable + _
      ".State, " + strWorkflowActions + ".Caption, " + strWorkflowTable + 
      ".Next_State, " + strWorkflowTable + _
      ".Event FROM " + strWorkflowTable + " LEFT OUTER JOIN Status ON " + 
      strWorkflowTable + _
      ".Next_State = Status.StatusID INNER JOIN " + strWorkflowActions + 
      " ON " + _
      strWorkflowTable + ".Id = " + strWorkflowActions + ".WorkflowID 
      WHERE (" + strWorkflowActions + _
      ".Caption = '" + strAction + "') AND (" + strWorkflowTable + 
      ".State = " + cStr(intState) + _
      " OR " + strWorkflowTable + ".State = -1)"

   Set oStateRS.ActiveConnection=oConnection
   oStateRS.Open

Three items are retrieved from the query: the next state for the issue, the text representation of the next state, and the event name associated with the action.

intNextState = oStateRS.Fields("Next_State").value
strNextState = oStateRS.Fields("Status").value
strEvent = oStateRS.Fields("Event").value

Executing the modView event

If the event is "modView," then all that must be done is navigate to the desired issue and disable all of the fields on the form. The custom subroutine EnableBoundFields with an input value of "none" checks through all of the objects on the page and disables all fields that are bound to data. Because this is required to view the issue, the subroutine is exited immediately after this.

Else
   MSODSC.DataPages.Item(0).Recordset.Find "ItemID = " + cStr(intItemID)

   document.title =
   MSODSC.DataPages.Item(0).Recordset.Fields("Subject").value
   EnabledBoundFields "None"

   Exit Sub
End If

Executing workflow events

Workflow events are executed differently depending on whether the event is OnCreate, OnChange, or OnDelete.

For the OnCreate event, a new row is inserted using the AddNew method of the ADO recordset, and the StatusID field is set to the first selected state.

Select Case strEvent
   Case "OnCreate"
      MSODSC.RecordsetDefs(0).serverFilter = ""
      MSODSC.DataPages(0).RecordSet.AddNew
      MSODSC.DataPages(0).RecordSet.Fields("StatusID").value =
      intNextState
      document.title = L_NewMessage_DialogTitle

For the OnChange event, the StatusID field is set to the next state.

      MSODSC.DataPages.Item(0).Recordset.Find "ItemID = " + 
      cStr(intItemID)
      MSODSC.DataPages(0).RecordSet.fields("StatusID").value=intNextState
         document.title=
         msodsc.DataPages(0).RecordSet.fields("Subject").value

For the OnDelete event, a message box is displayed to verify the issue is to be deleted. If the user selects Yes, several DELETE SQL statements are executed to delete the issue and any related information in the comments or linked files tables. In addition, any associated records in the modPermissions table must be removed. All of these commands are wrapped in a transaction, so if the issue is not deleted properly, the transaction can be rolled back.

As each DELETE statement is executed, errors are checked and handled using the HandleDBErrs routine that is available from the Connect.vbs file.

When completed, the window is closed and the function is exited, because no other processing is required.

if msgbox(L_VerifyDelete_StaticText,vbExclamation +
   vbYesNo,GetDialogTitle()) = vbYes Then

   oConnection.BeginTrans
   strTables = Array("IssuesComments","IssuesLinkedFiles","Issues")
   On Error Resume Next
   For Each strTable in strTables
      strQuery = "DELETE FROM " & strTable & " WHERE ItemID = " & 
      cStr(intItemID)
      oConnection.Execute strQuery
      If Err.Number <> 0 Then
         HandleDBErrs L_DeleteError_Message
         oConnection.RollbackTrans
         window.close
         Exit Sub
      End If
   Next

   strQuery = "DELETE FROM modPermissions WHERE RowID = " & 
   cStr(intItemID) & GetTableID("Issues")
   oConnection.Execute strQuery
   If Err.Number <> 0 Then
      HandleDBErrors L_DeleteError_Message
      oConnection.RollbackTrans
      window.close
      Exit Sub
   End If

   oConnection.CommitTrans
   On Error GoTo 0
   If IsObject(window.opener) Then
      window.opener.refreshGrid()
   End If
   window.close
   Exit Sub
End if

Finally, the list of fields that should be enabled for the event is retrieved from the workflow table. This is used by the custom EnabledBoundFields subroutine that enables the list of fields provided as input.

oStateRS.Source = "SELECT Enabled_Columns, Hidden_Columns FROM " +
   strWorkflowActions + _
   " WHERE Caption = '" + strAction + "' AND WorkflowID = " +
   cStr(intWorkflowID)
oStateRS.Open

EnabledBoundFields oStateRS.Fields("Enabled_Columns").value

End Sub

Workflow Functionality in the Issue Tracking Sample Application

Initiating the Event

Displaying the Detail Form

Initializing the Detail Form

Completing the Workflow

See Also

Additional resources

Workflow Functionality in the Issue Tracking Sample Application

Issue Tracking Action Toolbar

Generating the Action Toolbar

Updating the Action Toolbar

Executing an Event on the Action Toolbar

Initiating the Event

Displaying the Detail Form

Initializing the Detail Form

Completing the Workflow

See Also

Additional resources