Workflow Support in the Issue Tracking Solution

Issue Tracking Action Toolbar

The action toolbar in the Issue Tracking solution automatically updates the enabled actions based on the currently selected issue in the grid. This implicitly enforces the workflow logic defined by the workflow table.

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 Close state. The Issue Tracking solution communicates this state flow to users by enabling only those permitted actions on the currently selected issue. As the user highlights different issues, the action bar is automatically updated.

Generating the Action Toolbar

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

Multiple actions in the workflow actions table can be associated with the same workflow row in the workflow table. For example, one OnUpdate workflow event could be associated with two workflow actions — such as Edit and Assign — but each action would enable different fields for modification on the Detail Form.

Because this functionality is used both by the Grid View and Detail Form pages, the routines for generating the workflow bar are consolidated into one 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. Once 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 your base table the workflow bar should be built on. 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 solution, 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, once 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 action 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. For example, if the event is OnCreate, then the action will create a new record rather than edit an existing one (OnUpdate) or execute a transition (OnTransition). The query is dynamically built, 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 necessary 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

Once 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

Updating the Action Toolbar

Once the Issue Tracking action toolbar is displayed on the page, only those actions appropriate for a selected issue should be available. All other actions must be disabled. 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 the workflow bar is based on. 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 solution, if the status of the issue were set to 0 for Active, then the Close and Delete actions 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.

First, 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)"

Once that information is available, a loop walks through the recordset. For each record returned, the Caption field in the record indicates the action 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

Executing an Action on the Action Toolbar

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

Identifying the issue and its current state and initiating the action for that issue.
Displaying the DetailForm.htm in a new window.
In the Detail Form, navigating to the proper issue and retrieving the action 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 used on the GridView.htm and the DetailForm.htm pages.

Initiating the Action

An action is initiated when the user clicks the desired action on the workflow bar. Selecting the action executes the

OnClick

event for the caption, which calls the

DoAction

function. The parameters passed into the

DoAction

function identify the action to be executed.

The

DoAction

function lives in the main Web page that includes the Common.vbs file. For example, if you execute an action from the workflow bar on GridView.htm, there is a

DoAction

function in the GridView.htm file that is executed. In addition, if you execute an action from the DetailForm.htm, the

DoAction

function that is executed is in the DetailForm.htm.

Displaying the Detail Form

If the action 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 action 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 action 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 ActiveX ModHFGrid control.

    intDoItemID = Grid_GetCurrentItemID()
    intDoState = Grid_GetCurrentState(strBaseTable)

Another requirement is to identify which event is associated with the action. 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 action is called "modView." This is used by the Grid View page to signify the user double-clicked the issue to view the issue and has no intention of editing it. If the action 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 action that can modify the issue, the row-level permissions must be verified to ensure the user has valid write permissions to the specific issue. If the user does not, the action 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 action 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

Once 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 action was executed from an action toolbar displayed on DetailForm.htm, this is the function that is initially executed.

The

DoAction

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

Sub DoAction (strAction, intState, intItemID)

If the action to execute is a workflow action, 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 action 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 action. If the action 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" walks 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 actions. If it is a workflow action, depending on whether the event is OnCreate, OnTransition, or OnDelete, the action is executed in a different manner.

For the OnCreate event action, a new row is inserted using the

AddNew

method of the ADO recordset, and the StatusID field is set to the next state — which is 1 for a newly created issue.

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 OnTransition event action, 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 action, a message box is displayed to verify that 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

For the OnUpdate event action, no special code is required.

Finally, the list of fields that should be enabled for the action 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