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.
Selections: Choose View - Filter and then All, Selected or Unselected.
Cell values: Right-click on a cell that has a desired value and then choose a filtering expression based on that value. Only records which have that expression value for that field will be displayed.
See this examples in this topic repeated step by step in the Manifold Future - Five Minute Filters Quickstart video.
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.
Right-clicking on a cell we can use that cell's content to automatically create and apply a filter.
In the column for the desired field, right-click on a cell that has the desired value.
In the Add Filter menu of options that pops up, choose the desired expression.
Choose View - Filter
In the menu click the filter not desired (same as unchecking it).
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.
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.
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.
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.
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.
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.
User Interface Basics
Table Windows and Big Data
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
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.