How to: Filter tree list columns

Let users narrow down the nodes of a tree list with conditions on individual columns, either typed into a filter row or picked from a value list in the column header, and set the same conditions from VBA. A node that meets the conditions stays in its place in the tree.

Requires WKL Forms Object Library 1.64 or later.

ShowFilterRow shows a row below the column headers with one condition per column. AllowColumnFilter adds a funnel icon to every column header, which opens the column's value list, and the filter commands to the header's shortcut menu. ShowFilterBar shows the active conditions below the records, with a switch and Clear filter. FilterIncludesChildren decides whether a matching node brings its child nodes along. In the designer, these four settings are in the Filter section of the Grid, grouping and export page.

From VBA, ColumnFilter and ColumnFilterValues set the conditions, ColumnFilterEnabled suspends them, and ClearColumnFilters removes them. The members and events have the same names as in WklGrid.

Private Sub Form_Load()
    With Me!List1.Object
        .ShowFilterRow = True
        .AllowColumnFilter = True
        .ColumnFilter("Database") = "BikeBuilder"   ' begins with
        .ColumnFilterValues("Outcome") = Array("refused")
        Debug.Print .ColumnFilterText
    End With
End Sub
 
Private Sub List1_BeforeColumnFilter(ByVal Column As String, ByVal Text As String, Cancel As Integer)
    If Column = "Database" And Text = "" Then Cancel = True     ' keep this condition
End Sub

Conditions in the filter row: text alone selects values that begin with it (Bike); a pattern with * or ? (*Builder); a number or date alone selects equal values (100); a comparison (>= 100, <> refused); and leer (empty) or not empty. Numbers and dates are read in the Windows regional format, without thousands separators.

• A node that meets every condition stays visible together with its parent nodes, which open so that the node can be seen in its place. Other nodes are hidden. With FilterIncludesChildren, the child nodes of a matching node are shown as well, including those that load later when the user expands it.
• The column filter applies in addition to Filter. Matches only in the search bar shows the matches among the nodes that the column filter leaves.
• A list that loads lazily (LazyLoad) sends the conditions to the record source as part of a query, so it also finds nodes that aren't loaded yet. With more than 1,000 matches, it loads the whole tree once. A list that holds every record (LazyLoad off, GroupFields, a Recordset, or a file) compares the loaded values.
• Group nodes of GroupFields have no field values. They stay visible as parents of matches.
• A condition that doesn't fit the column type is shown in red while typing and doesn't filter; from VBA, it raises wkeInvalidValue. If the record source refuses a condition, the filter stays as it was; the user gets the error message, and VBA gets wkeSourceError.
• BeforeColumnFilter and AfterColumnFilter report changes the user makes in the filter row, the value list, the header's shortcut menu, and the filter bar, not changes made from VBA.
• With AutoSaveLayout, the list remembers the conditions per Windows user. Reset removes them.
• WklGantt inherits the column filter: the timeline shows the same tasks as the list.

• API reference: ShowFilterRow, ColumnFilter, FilterIncludesChildren
• How to: Search and filter nodes (search bar and Filter)
• How to: Filter grid columns (the same filter in WklGrid)
• How to: Keep the user's arrangement
• WklTreeList