Filters

Filters are a great way to show only desired records in a table window.  They are incredibly fast and handy because they require no keyboarding.

Two Ways to Use Filters

 

 

See this examples in this topic repeated step by step in the Manifold Future - Five Minute Filters Quickstart video.

 

 When filters are active in a table window, press the Filter button in the main toolbar to re-apply a filter to account for any changes in a table.  For example, if we have the Selected filter active to show only selected records in a table, and then we change the selection using the Select pane or by clicking associated records in a map window or other window, pressing the Filter button will update the table to show the new selection.

View - Filter Menu

The View - Filter command appears in the main menu for table windows, providing commands to manage filters.

 

 

 Reapply Filter

Reapply the current filter settings.   Used to refresh filters when the data in tables may have changed.   This is not a command to restore previous filters that may have been cleared.    Same as pressing the Filter button in the main toolbar.

Clear Filter

Clear any filters applied.

All

Show records regardless of selected or unselected status.

Selected

Show only selected records.

Unselected

Show only records that are not selected.

<filters list>

A list of all filters that have been applied.  

Filter Fetched Records Only

When checked (the default) only those records currently fetched for display in the table window will be filtered, for example, the first 50,000 records of large tables.  Unchecking this box filters all records in the table, and will show those records up to the display limit, for example, the first 50,000 records that the filter displays.

Filter using Query

Open the Command Window loaded with a query which performs the current filter settings.

 

Filters and Selections

Filters can instantly show us only selected records, only unselected records or all records in a table.

 

 

Click open a table with a selection in it.

 

 

With the focus on the table, choose View - Filter and then the Selected option.

 

 

That filters the table to show only selected records.  The upper left corner of the table shows a filter icon to indicate the display has been filtered.

 

To get rid of the selection filter, choose View - Filter - Clear Filter, or choose View - Filter - All.

Using Filters with Cells in a Table

Right-clicking on a cell we can use that cell's content to automatically create and apply a filter.  

 

To add a filter

 

  1. In the column for the desired field, right-click on a cell that has the desired value.

  2. In the Add Filter menu of options that pops up, choose the desired expression.

  3. If desired, in the View - Filter command, check or uncheck the Filter Fetched Records Only setting as desired.

 

To remove a filter

 

  1. Choose View - Filter

  2. In the menu click the filter not desired (same as unchecking it).

 

Filter Expressions

The value that is in the cell we right-click is the cell value.  The Add Filter menu provides the following expressions as options:

 

field = cell value

Display all records where the value in the field is the same as the value in the right-clicked cell.

field <= cell value

Display all records where the value in the field is less than or equal to the value in the right-clicked cell.

field >= cell value

Display all records where the value in the field is greater than or equal to the value in the right-clicked cell.

field: not NULL

Display all records where the value in the field is not NULL.

field <> cell value

Display all records where the value in the field is not the same as the value in the right-clicked cell.   

field: NULL

Display all records where the value in the field is NULL.   

 

Expressions that will cause the record for the right-clicked cell to not be displayed will be grouped below a dividing line in the context menu for expressions.

 

Examples

Consider a table window containing points of interest imported from OpenStreetMap.   We would like to display only those records for which the fclass field contains the value supermarket.

 

 

 We right-click on a cell for the fclass field that contains supermarket.

 

 

In the Add Filter menu we click on fclass='supermarket' as the desired filter.  

 

 

The table window immediately re-displays to show only those records for which the fclass field contains supermarket.

 

Suppose now we would like to add another filter, to display only those supermarkets that are not part of the Spar chain?

 

We right-click on one of the cells for the name field that has Spar as a value.

 

In the Add Filter menu we choose name <> 'Spar', that is, the value in the name field is not Spar.

 

 

The table window immediately re-displays to show only those records that have supermarket in the fclass field and do not have Spar in the name field.

 

 

 

To remove one of the two filters we have added we choose View - Filter and click the one we do not want, to uncheck it and remove it.   For example, to eliminate the filter to show only those records that do not have the name Spar we click on that filter to remove it.

 

 

The table window now re-displays using the only filter which remains, which is to show only those records for which the fclass field has a value of supermarket.

Combining Filters with Selection

We can combine the effects of filters with selections as well.

 

 

Suppose in our table window that uses a filter to display only those records that are supermarkets we ctrl-click two of the records to select them.

 

 

 

We now choose View - Filter - Selected to command the table window to display only selected records.

 

 

The result is that we have a table window that shows only those records that are both selected and for which the fclass field value is supermarket.

Applying Multiple Filters using the Same Field

In the example above we added a second filter using a different field.  We can also add filters that use the same field.    Suppose we have a table that shows the provinces of Mexico with the size of each in square kilometers.   We would like to display only those provinces that are between the sizes of the provinces of Zacatecas and Durango, inclusive.

 

 

We right-click into the SQKM cell value for Zacatecas province.

 

 

From the Add-Filter menu we choose SQKM >= 73252.  

 

 

The table window re-displays to show only those provinces which are greater than or equal to in size to Zacatecas.    We now right-click on the SQKM value for Durango.

 

 

From the Add-Filter menu we choose SQKM <= 123181.  

 

 

The table window immediately re-displays to show only those provinces that have SQKM values greater than or equal to Zacatecas and less than or equal to Durango.

 

 

Choosing View - Filter shows we have two filters that are active, both of which filter on the SQKM field.   We can click on the Filter using Query entry to command Manifold to write for us the SQL query which achieves the results of the filter.

 

 

Choosing Filter using Query opens a Command Window that is loaded with the query:

 

SELECT * FROM [Mexico Table]

 WHERE [SQKM] >= 73252

   AND [SQKM] <= 123181;

 

The above query implements the filters that we have set.   If we press the ! run button to run the query, we see that the results table selects those records that the two combined filters display in the table window.

Changes to Data

Filters show a snapshot of the state of the data when they are applied.  Changes such as renaming components or alterations in selections in related components will clear a filter such as View - Filter - Selected.  This avoids potentially slow update operations on every small change when working with larger data.  Future builds of Manifold may add persistence of filter settings and automatic refresh, if the community prefers.

Notes

Big data - As noted in the Big Tables and Table Fill Strategies sections of the Tables topic, as well as in the Table Windows and Big Data essay, viewing table windows by eye is not a practical way of browsing larger tables.   Filters can be used with bigger tables, of course, which is why there is a Filter Fetched Records Only setting option in the View - Filter command that can be turned off.   But turning that option off is something only to consider in cases where we know the filter hits will be a small enough subset of the entire, big table that it makes sense to scan them by eye.    A rule of thumb is that if we are using Filters, we are not working with larger data.   When working with larger data, the right tool is a query, using the power of SQL to extract subsets of data from much bigger data.

See Also

Getting Started

 

User Interface Basics

 

Tables

 

Sorting Columns

 

Selection

 

Table Windows and Big Data

 

Editing Tables

 

Schema

 

View - Filter

 

View - Order

 

Examples - Do not miss!  Browse through the many examples for step by step tutorials.

 

Example: Closing without Saving - An example that shows how File - Close without saving the project can affect local tables and components differently from those saved already into a data source, such as an .mdb file database.

 

Table Windows and Big Data

Videos

Manifold Future - Five Minute Filters Quickstart - This five minute video shows how, using points of interest in Monaco to show how to combine filters on two different fields, and then we switch gears to show how two filters on the same field can be applied in seconds to get exactly the records we want.