When Clarify grids go bad
For the most part, Clarify is pretty easy to customize. Occasionally, you run into Clarify forms that just don't behave as you would expect. In particular, the "old" Clarify forms, that were all written in C, long before ClearBasic was considered - those can be problematic. ClearLogistics forms are also typically problematic - the initial ClearLogistics development team seemed to have their own way of doing things back then.
We recently assisted a customer with such an issue, and I'm sharing it here so hopefully it may help others in a similar situation.
Customizing the "Select Inventory Parts" form
The requirement at hand was to exclude inventory parts from a certain inventory location when selecting inventory parts - specifically, on Form 519 (Select Inventory Parts).
Here's the form, showing all inventory parts:
Lets say that we want to exclude those inventory parts that exist in location "12345".
Traditional approach
The traditional approach would be to get the list of records out of the grid (or the contextual object itself), loop through them, remove the ones that match, and repopulate the grid with the updated list of records.
Sub SELECT_Click()
Me.DoDefault
HideInventoryPartsInLocation "12345"
End Sub Sub HideInventoryPartsInLocation(locationName As String)
Dim partsList As List
Dim origCount As Integer
Dim partRecord As Record
Dim index As Integer Set partsList = Cobj_part_inst.Contents origCount = partsList.Count
For index = origCount-1 To 0 Step -1
Set partRecord = partsList.ItemByIndex (index)
If locationName = partRecord.GetField ("location_name") Then
partsList.RemoveByIndex index
End If
Next index cobj_part_inst.Fill partsList
Cobj_TOTAL_NUM.Fill partsList.Count
End Sub |
Unfortunately, this yield a Type Mismatch runtime error on this line:
Set partRecord = partsList.ItemByIndex (index)
A little debug code allows us to take a closer look at the the partsList variable:
MsgBox "partsList.ItemType: " & partsList.ItemType
The ItemType of this list is "long". We're expecting a list of records, not longs. And these longs aren't objids either. I would guess they're pointers.
No love. Lets try something else.
Another traditional approach
Another way to do this would be to do the query ourselves, excluding the parts we want to exclude, and then simply fill the grid.
Sub HideInventoryPartsInLocation(locationName As String)
Dim br As New BulkRetrieve
Dim partsList As List
Dim gridList As List
Dim origCount As Integer
Dim index As Integer
Dim obj As Record
Dim recFilter As Record
Dim str_part_number As String
Dim str_mod_level As String
Dim str_part_descr As String
Dim str_part_serial_no As String
Dim str_location_name As String
Dim str_bin_name As String
Dim str_container_id As String
Dim ascDescString As String
Dim ascDesc As Long
Dim sortBy As String
Set recFilter = Cobj_NEW_FILTER.Contents
str_part_number = Trim(recFilter.GetField("part_number") )
str_mod_level = Trim(recFilter.GetField("mod_level"))
str_part_descr = Trim(recFilter.GetField("part_descr"))
str_part_serial_no = Trim(recFilter.GetField("part_serial_no"))
str_location_name = Trim(recFilter.GetField("location_name"))
str_bin_name = Trim(recFilter.GetField("bin_name"))
str_container_id = Trim(recFilter.GetField("container_id"))
br.SimpleQuery 0, "parts_view"
br.AppendFilter 0, "hdr_ind", cbNotEqual, 1
br.AppendFilter 0, "hdr_ind", cbNotEqual, 3
br.AppendFilter 0, "location_name", cbNotEqual, locationName If (str_part_number <> "") Then
br.AppendFilter 0, "part_number", cbLike, str_part_number & "%"
End If
If (str_mod_level <> "") Then
br.AppendFilter 0, "mod_level", cbLike, str_mod_level & "%"
End If
If (str_part_descr <> "") Then
br.AppendFilter 0, "part_descr", cbLike, str_part_descr & "%"
End If
If (str_part_serial_no <> "") Then
br.AppendFilter 0, "part_serial_no", cbLike, str_part_serial_no & "%"
End If
If (str_location_name <> "") Then
br.AppendFilter 0, "location_name", cbLike, str_location_name & "%"
End If
If (str_bin_name <> "") Then
br.AppendFilter 0, "bin_name", cbLike, str_bin_name & "%"
End If
If (str_container_id <> "") Then
br.AppendFilter 0, "container_id", cbLike, str_container_id & "%"
End If
ascDescString = ASC_DESC.Value
ascDesc = cbDescending
If ascDescString = "Ascending" Then
ascDesc = cbAscending
End If
sortBy = GetStringAfterCharacter(SORT_BY_MBT.UserData, ".")
sortBy = GetStringAfterCharacter(sortBy, ":")
br.AppendSort 0, sortBy, ascDesc br.RetrieveRecords
Set partsList = br.GetRecordList(0)
cobj_part_inst.Fill partsList
Cobj_TOTAL_NUM.Fill partsList.Count
End Sub |
The UI doesn't like this approach at all. Notice that there are the right number of records (2) in the grid, but all of the data is empty:
and double-clicking one of these rows yields this helpful gem:
Ugh. Still no love. So now what?
Approach #1: RemoveByIndex
Perform the same query as the baseline List button. Same filtering, same sorting. Get the list of inventory parts data from the form (from the contextual object). Loop through the list of records that we retrieved from the database. If this is a record to be hidden, then remove it (by index) from the list of data retrieved from the form. Refresh the form data.
One warning here: the query must match exactly - same query, same filtering, same sort order, everything.
| Sub HideInventoryPartsInLocation(locationName As String)
Dim br As New BulkRetrieve
Dim partsList As List
Dim gridList As List
Dim origCount As Integer
Dim index As Integer
Dim obj As Record
Dim recFilter As Record
Dim str_part_number As String
Dim str_mod_level As String
Dim str_part_descr As String
Dim str_part_serial_no As String
Dim str_location_name As String
Dim str_bin_name As String
Dim str_container_id As String
Dim ascDescString As String
Dim ascDesc As Long
Dim sortBy As String 'Get the user's filter criteria
Set recFilter = Cobj_NEW_FILTER.Contents
str_part_number = Trim(recFilter.GetField("part_number") )
str_mod_level = Trim(recFilter.GetField("mod_level"))
str_part_descr = Trim(recFilter.GetField("part_descr"))
str_part_serial_no = Trim(recFilter.GetField("part_serial_no"))
str_location_name = Trim(recFilter.GetField("location_name"))
str_bin_name = Trim(recFilter.GetField("bin_name"))
str_container_id = Trim(recFilter.GetField("container_id")) 'Perform the same query as the List button's default action
br.SimpleQuery 0, "parts_view"
br.AppendFilter 0, "hdr_ind", cbNotEqual, 1
br.AppendFilter 0, "hdr_ind", cbNotEqual, 3 If (str_part_number <> "") Then
br.AppendFilter 0, "part_number", cbLike, str_part_number & "%"
End If
If (str_mod_level <> "") Then
br.AppendFilter 0, "mod_level", cbLike, str_mod_level & "%"
End If
If (str_part_descr <> "") Then
br.AppendFilter 0, "part_descr", cbLike, str_part_descr & "%"
End If
If (str_part_serial_no <> "") Then
br.AppendFilter 0, "part_serial_no", cbLike, str_part_serial_no & "%"
End If
If (str_location_name <> "") Then
br.AppendFilter 0, "location_name", cbLike, str_location_name & "%"
End If
If (str_bin_name <> "") Then
br.AppendFilter 0, "bin_name", cbLike, str_bin_name & "%"
End If
If (str_container_id <> "") Then
br.AppendFilter 0, "container_id", cbLike, str_container_id & "%"
End If 'Set up the ascending/descending sort order
ascDescString = ASC_DESC.Value
ascDesc = cbDescending
If ascDescString = "Ascending" Then
ascDesc = cbAscending
End If 'set up the sort by
sortBy = GetStringAfterCharacter(SORT_BY_MBT.UserData, ".")
sortBy = GetStringAfterCharacter(sortBy, ":") 'In my testing, baseline Clarify uses location_name
'as the sort order for certain sortBy selections
'Not sure why. This may be data related.
'More exploratory testing may be warranted.
If sortBy = "bin_name" Then sortBy = "location_name"
If sortBy = "container_id" Then sortBy = "location_name"
If sortBy = "fixed_bin_name" Then sortBy = "location_name"
If sortBy = "mod_level" Then sortBy = "location_name" 'Apply the sorting criteria
br.AppendSort 0, sortBy, ascDesc br.RetrieveRecords
Set partsList = br.GetRecordList(0) 'Get the data from the form
'This will actually be a list of Longs, not a List of records
'More Clarify weirdness
Set gridList = cobj_part_inst.Contents 'Loop through the list of records that we retrieved from the database
'If this is a record to be hidden, then remove it (by index) from the
'list of data retrieved from the form
origCount = partsList.Count
For index = origCount-1 To 0 Step -1
Set obj = partsList.ItemByIndex (index)
If locationName = obj.GetField ("location_name") Then
gridList.RemoveByIndex index
End If
Next index 'refresh the form data
Cobj_TOTAL_NUM.Fill gridList.Count
cobj_part_inst.Refresh End Sub |
Now we've excluded inventory parts in location "12345":
Success!
Approach #2: A duplicate grid
Here's another approach that should work as well.
- Setup a 2nd grid that looks like the baseline grid, along with a new LIST button.
- Add a new text box.
- Name: FILTER_BY_OBJID
- Destination COBJ: NEW_FILTER
- Destination field name: objid
- Hide this text box in the non visible part of the form.
- When the user clicks the custom LIST button, perform the same query as the baseline List button (bulkretrieve with the appropriate filters & sorting), and fill the cobj of the 2nd grid.
- On the click action for the 2nd grid:
- Get the objid of the selected row.
- Put this value in the FILTER_BY_OBJID textbox.
- Programmatically Click the baseline LIST button.
- Programmatically Click the first (and only) row in the baseline grid
- Clear the value in the FILTER_BY_OBJID textbox.
- On the double-click action for the 2nd grid:
- Programmatically Double-Click the first (and only) row in the baseline grid
We've used this approach in the past, for example on the Dispatch form (form 425).
The code for this is left as an exercise for the reader.
Summary
I hope you don't have to deal with this craziness, but if you do, hopefully you'll find this post helpful.
Sometimes you have to be a little creative, especially when dealing with forms that were written probably 15 years ago.
