Filtering Items

As the number of items in your database increases, it becomes essential to filter out some to focus on others.
When you create a new grid, you're actually creating a filter whereby, by default, only items you create in that grid will be shown there.
Nevertheless, the numbers of items in a grid may become large and filtering in such cases can be useful. Grids support a number of different kinds of filters:
  1. Column filters
  2. Grid filter
  3. Hoist filter
  4. Alpha-numeric filter toolbar
  5. Date filter toolbar
  6. Advanced filters using the Source bar
     

1. Grid Column Filters

1.1 Filter by Selection 

The easiest way to filter a grid is to filter by selection . To do so:
  1. Select a field value (item text or another cell
  2. Click on the Column Filter button (Ctrl+Shift+G or Grid > Column Filter)
  3. The list will instantly filter to show only items that have the selected value
  4. You can select 2 or more values to show all items that have any of the selected values
  5. For multi-select text fields, selecting one or more values will show all items that have any of the selected tags, even if the cell contains more than one tag
  6. Click the Column Filter button again to turn of the filter
When a column filter is active, the filter bar (below the grid) is shown, with a yellow background.
Depending on the filter type, the filter bar can be edited directly.
Click the X on this filter bar to close it.
 

1.2 Column Header Filter Menu

Each column also has a filter dropdown button, which is shown when hovering over the column header: 
Click the dropdown button to show a list of filter items:

  • The drop down can show or not the list of values. To show the list, select the column and do Grid > Column filter and check "Show Values in column dropdown filter ". Holding the Shift key when clicking on the filter dropdown button will toggle the behavior
  • If the list is shown, you can select one or more values in the list. Press Enter or click anywhere on the grid to apply the filter (small bug alert: if you press Enter, the mouse pointer must still be over the list, or else the filter is not applied)
  • If the list is not shown, a filter text box is shown. Enter filter criteria (e.g. text column: *TextSomewhere*, number column: >50. For date columns, a mini-calendar is shown, click or click and drag to select 1 or more dates
  • You can also select (All), (Empty) and (Not Empty). (All) removes that column filter
  • "Exclude" inverts the filter. All items are shown except checked ones
  • The list is sizable (handle is on the bottom-left corner)
  • The list varies depending on the field type (text, date, number, yes/no)

 1.3 Filter Options

The Column Filter command is a split button with a set of sub-commands:

a

  • Grid Column Filter...: Shows the Grid Filter dialog (see the next section for details)
  • Grid Column Filter ON / OFF: Toggles between the filtered and non-filtered list
  • Filter by Selection: Filter is applied to show only selected values
  • Filter First Level Items Only: Filter is applied to first level items only. Only root items that meet the filter will be displayed
  • Filter Items at any levels: All items that meet the filter will be displayed. In addition, parent items will be shown even if they don't meet the filter
  • Hide non-matching subitemsFilter: If an item meets the filter, determines if its sub-items are shown or not
  • Show Values in the column filter dropdown: If checked, the grid column filter will list all unique values, with a checkbox to select individual values
You can filter more than one column at once. The filters are logical AND (Items must meet all column filters to be displayed)
 
Hint:
  • The filter can list all loaded items or just visible ones (i.e. filtered items and collapsed sub-items are not shown). Hold the Ctrl key to toggle between each list
  • If Grid > Properties > Auto-Updates is checked (default), the grid will load all items and sub-items, even sub-items of collapsed items (up to 9 levels deep)
     

2. Grid Filter

Grid filter provides an easy way to filter the list of items shown.
Use the command Grid > Column Filters > Grid Column Filter... (Ctrl+G) to show the dialog:
a
 
and enter the filter criteria and click on OK to apply the filter. At the bottom of the grid, the filter bar will be shown in yellow (in this case, the criteria was "format"):
 
Things to know about the Grid Filter:
  1. The wildcards characters are ?, #, *
    • ?: Any single character
    • #: Any single digit
    • *: 0 or more characters
  2. To clear the filter, you can show the dialog again, erase the criteria and hit enter.
  3. You can also clear the filter by clicking on the X on the filter bar.
  4. The filter bar is actually an edit box. So you can edit it. It does filter-on-type, so the list will live update as you type.
  5. By default, the filter looks into all grid columns. If you select some columns, filtering will be limited to those columns only.
  6. If there are no wildcard characters in the criteria, the filter will look anywhere in the text and do a logical AND of all words.
  7. If there are at least one wildcard character in the criteria, the filter will do a logical OR and respect the wildcard characters.
Some examples:
  1. format : shows all items that string anywhere in the text.
  2. format*: shows all items that start with "format".
  3. *format?ing: shows all items that have the string "format?ing" where ? can be any character.
  4. format the item: shows all item that have those 3 words in the text.
  5. *format* form*: shows all items that have the word "format" somewhere or that starts with "form".
  6. *format#: shows all items that contain "format" followed by any digit (0-9)
     

3. Hoist Filter

Details here: Hoist Filter 

4. Alphanumeric filter toolbar

The AlphaNumeric filter (Grid >> Alpha-numeric filter) is typically used for address book management, but it can be used anytime you want to filter on text (or numbers).  To quickly find contacts: simply click on a letter to show contacts starting with that letter.

In this example, contacts starting with the letter C would be shown. You may also type in the "C" manually into the Filter field and press Enter to do the same thing
You can use this filter on other grids, to filter on number values, addresses, postal codes, project name, etc. This can be of great value in any grid. For example, entering "John Doe would" would show only tasks assigned to him. 
The Alphanumeric filter will find items that start with the text. To find anywhere in the text, precede with *. 
  • e.g. to find all contacts with last name Smith, enter "* smith" (no quotes) and hit Enter

5. Date filter toolbar

The DateFilter toolbar (Grid >> Date Filter)  is a great tool to see information relating to a specific time-frame.
  • Want to know what you did last week?
  • Your agenda for this week?
  • Your current ToDo's?
  • Work done and not billed?
All those are very easily answered using the DateFilter toolbar:
 

In this example, appointments for the month of October 2007 will be displayed. Any date field can be used as filter (and multiple fields can be used)
  • Appointments
  • ToDo
  • ItemCreated
  • ItemModified
  • DueDate, etc
So this toolbar is ideal to see a journal of activity. Details here

6. Advanced filters using the Source bar

The Source bar can be used to select the grid Source, but also the SQL-like Filter and Sort. Use Grid > View Source bar or click the folder icon (top-left corner of the grid window)