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.

Filters and Selections

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

 

il_filters_table02_01.png

 

Click open a table with a selection in it.

 

il_filters_table02_02.png

 

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

 

il_filters_table02_03.png

 

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.

 

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.

 

il_filters_table01_01.png

 

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

 

il_filters_table01_02.png

 

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

 

il_filters_table01_03.png

 

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?

 

il_filters_table01_04.png

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

 

il_filters_table01_05.png

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

 

il_filters_table01_06.png

 

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.

 

 

il_filters_table01_07.png

 

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.

 

il_filters_table01_08.png

 

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.

 

il_filters_table01_09.png

 

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.

 

 

il_filters_table01_10.png

 

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

 

il_filters_table01_11.png

 

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.

 

il_filters_table01_12.png

 

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

 

il_filters_table01_13.png

 

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

 

il_filters_table01_14.png

 

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.

 

il_filters_table01_15.png

 

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

 

il_filters_table01_16.png

 

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.

 

il_filters_table01_17.png

 

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.

 

il_filters_table01_18.png

 

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.

 

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.

 

 

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.