Many times when processing invoices, customers want to compare line items on the invoice to items in a database. The GridChoiceDialog allows the template designer to create a series of rows and columns that the Verify operator may select. With this script, a check box of Full Payment, Partial Payment, of Flagged is filled based on the line item selected to match the current line of the invoice displayed in the Verifier.
This feature requires OCR for AnyDoc 5.0 or higher. This is an interactive script that takes place as the verify operator is moving from check box to check box. The check boxes themselves are populated based on the choices made in the GridChoiceDialog. Since the check boxes are not part of the line items, the line number of the current line must be calculated based on the zone number of the check box. The script will show several for/next loops that contain a calculation for the line number. See comments in the script for details.
Further, the Pass 1 operator is validating the line items, and the Pass 2 operator is matching the line items to the items retrieved from the database.
GUI Product Features Used in this Script:
-
A custom verify screen is used.
-
Key VB Script Features Demonstrated in this Script:
-
Application.InPassTwoVerification - script only executes in pass 2 verification.
-
GridChoiceDialog.SetInitialViewArea - if using a display image, defines what it is.
-
GridChoiceDialog.DisplayImage - Boolean for showing an image.
-
GridChoiceDialog.SetTitle - Sets the title for the dialog.
-
GridChoiceDialog.NumberCols - Sets number of columns - "1" based - 4 means 4 columns, for example.
-
GridChoiceDialog.ColHeading(#) "String" - Assigns column heading - # is "0" based (i.e., First Column is column 0).
-
GridChoiceDialog.AddItem "String" - here, populated from a database, but items are skipped if already used.
-
GridChoiceDialog.CellBackColor - Assigns color of cell - in this example used "VBColor." Hex values can be used as well.
-
GridChoiceDialog.CellTextColor - Assigns color of text - see above.
-
GridChoiceDialog.DoModal - A selection was made.
-
GridChoiceDialog.GetIndex and .GetSelection - Allow retrieval of the item the verification operator selected in the dialog.
-
GridChoiceDialog.FitToWindow and .FitToRows(#) - Allows the dialog to "shrink to fit" the data displayed.
-
GridChoiceDialog.CellText(Column#, Row#) - Retrieves or sets the text in the column and row - "0" based count.
-
Application.NavigateTo("Zone Name", iLineNumber) - Functions as a GOTO in the Verifier. If iLineNumber is not present, Line 1 is assumed.
-
To match the current line item, Black Badge Holder, press Enter.
The GridChoiceDialog window is displayed.
-
Double click the selection that matches.
Example Script:
If Application.InPassTwoVerification Then
bSkip = False
nCurrentZone = This.ZoneNumber
nCurrentLine = (((nCurrentZone - 19)/2) + 1)
nMaxLines = Form.Zone(10).LineCount
strCurrentString = Item Number.Line(nCurrentLine).Value & Chr(124) & Item Description.Line(nCurrentLine).Value & Chr(124) & Qty.Line(nCurrentLine).Value & Chr(124) & Extended Cost.Line(nCurrentLine).Value
GridChoiceDialog.SetInitialViewArea 450, 950, 2537, 1150
GridChoiceDialog.DisplayImage = False
GridChoiceDialog.SetTitle "Select database line item that matches the currently selected line. Most likely match is highlighted."
Set objConnPO = CreateObject("ADODB.CONNECTION")
Set objRS = CreateObject("ADODB.Recordset")
objConnPO.ConnectionString = strDBConnTerms
objConnPO.Open
objRS.Cursortype = 3 'asUseClient
strFilter = "SELECT ITEM_NUMBER AS ITEM, ITEM_DESC AS DESCRIPTION, QTY AS QTY, EXT_PRICE AS Extended FROM DETAIL WHERE VENDOR_ID = '" & VendorID.Value & "' ORDER BY QTY"
objRS.Open strFilter, objConnPO
gridchoicedialog.numbercols = 4
gridchoicedialog.colheading(0) = "Item"
gridchoicedialog.colheading(1) = "Description"
gridchoicedialog.colheading(2) = "Quantity"
gridchoicedialog.colheading(3) = "Price"
While Not objRS.EOF
strLineItem = objRS.Fields("ITEM") & Chr(124) & objRS.Fields("DESCRIPTION") & Chr(124) & objRS.Fields("QTY") & Chr(124) & objRS.Fields("Extended")
strPartial = objRS.fields("ITEM")
If Not IsNull(strPartial) Then strPartial = Replace(strPartial, " ", "")
bSkip = False
'If the current item has a check next to it, don't use it
For kk = 19 To 37 Step 2
If Form.Zone(kk).Box(1).Hidden = False Then
If Form.Zone(kk).Box(1).Checked = True Then
'Since check boxes are not part of the group zone
'calculate which line line this is from the box number
nUsedLine = ((kk - 19)/2) + 1
strUsedValue = Item Number.Line(nUsedLine).Value
strUsedValue = Replace(strUsedValue, " ", "")
If strPartial = strUsedValue Then
bSkip = True
End If
End If
End If
Next
For jj = 41 To 50
If Form.Zone(jj).Box(1).Hidden = False Then
If Form.Zone(jj).Box(1).Checked = True Then
nUsedLine = jj - 40
strUsedValue = Item Number.Line(nUsedLine).Value
strUsedValue = Replace(strUsedValue, " ", "")
If strPartial = strUsedValue Then
bSkip = True
End If
End If
End If
Next
If bSkip = False Then
'The following block of code checks to see if there is a solid match of the line item or a soft match.
'A solid match is given a green background
'A soft match is given a yellow background
intLineNo = intLineNo + 1
gridChoiceDialog.AddItem strLineItem
strLineItem = Replace(strLineItem, " ", "")
strCurrentString = Replace(strCurrentString, " ", "")
strCurrentItem = Replace(Item Number.Line(nCurrentLine).Value, " ", "")
If strLineItem = strCurrentString Then
For yy = 0 To 3
gridchoicedialog.cellbackcolor(IntlineNo - 1, yy) = vbGreen
Next
ElseIf strPartial = strCurrentItem Then
For yy = 0 To 3
gridchoicedialog.cellbackcolor(IntlineNo - 1, yy) = vbyellow
Next
End If
End If
bAddedRecord = True
objRS.MoveNext
Wend
'This block adds the "ACCEPT ALL" item last and makes it black background white letters.
gridChoiceDialog.AddItem "ACCEPT ALL GRID LINES"
For xx = 0 To 3
Gridchoicedialog.cellbackcolor(IntLineNo, xx) = vbBlack
Gridchoicedialog.celltextcolor(IntLineNo, xx) = vbWhite
Next
GridChoiceDialog.fittowindow(2)
gridchoicedialog.fittorows(IntLineNo + 1)
'"DO MODEL" indicates a choice has been made - from this point on, code is a reaction to the selection
vbret = GridChoiceDialog.DoModal 'returns vbTRUE or vbFALSE
If vbret Then
n = GridChoiceDialog.GetIndex
s = GridChoiceDialog.GetSelection
strQtyDB = gridchoicedialog.celltext(n, 2)
strQty = Qty.Line(nCurrentLine).Value
'If Accept All selected
If n = intLineNo Then
For kk = 19 To 27 Step 2
If Form.Zone(kk).Box(1).Hidden = False Then
Form.Zone(kk).Box(1).Checked = True
Else
'When we hit a hidden exit out and navi to button
Application.NavigateTo("Accept")
Exit For
End If
Next
Else
'If not all accept, then accept the one we are on and navi to next row if not last row
If CInt(strQtyDB) = CInt(strQty) Then
Form.Zone(19 + (2 * (nCurrentLine - 1))).Box(1).Checked = True
Form.Zone(20 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Hidden = True
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Checked = False
ElseIf CInt(strQtyDB) > CInt(strQty) Then
strSitch = "Database Items = " & strQtyDB & vbCrLf & "Invoice Items = " & strQty
strSitch = "Please select NO to FLAG this line item for review in workflow." & vbCrLf & vbCrLf & strSitch
vbResult = MsgBox ("Please select YES to accept a partial payment." & vbCrLf & strSitch , 36, "Partial Payment?")
If vbResult = vbYes Then
Form.Zone(20 + (2 * (nCurrentLine - 1))).Box(1).Checked = True
Form.Zone(19 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Hidden = True
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Checked = False
ElseIf vbResult = vbNo Then
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Hidden = False
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Checked = True
Form.Zone(20 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
Form.Zone(19 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
End If
Else
MsgBox "Shipped items exceeds number ordered - item flagged for workflow.", 48, "FLAGGED LINE ITEM"
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Hidden = False
Form.Zone(41 + (nCurrentLine - 1)).Box(1).Checked = True
Form.Zone(20 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
Form.Zone(19 + (2 * (nCurrentLine - 1))).Box(1).Checked = False
End If
If nCurrentLine <> nMaxLines Then
Form.Zone(nCurrentZone + 2).Box(1).Flagged = True
Form.Zone(nCurrentZone + 1).Box(1).Skip = True
Else
Application.NavigateTo("Accept")
End If
End If
Else
This.Box(1).Flagged = True
End If
End If