MvFILTER#

If you have several open database aliases, each one can have a filter associated with it. However, a single alias can have only one filter applied to it at a time. Subsequent <MvFILTER> tags referring to that alias will replace the current filter.

To remove a filter and not replace it, omit the FILTER attribute. If both NAME and FILTER are omitted, the current filter for the primary database will be removed.

The variables alias.d.totrec and alias.d.recno will continue to refer to the complete, unfiltered database.

Syntax

<MvFILTER NAME = "string: {  expression } | literal"
          FILTER_TYPE = "expression | variable"
          FILTER = "{ expression }">

Attributes#

Attribute	Description
`NAME`	(Optional) Database alias. When a database is opened with `<MvOPEN>`, it is referenced in all other database commands by this alias. If omitted, the filter applies to the primary database. The database must be open.
`FILTER_TYPE`	(Optional) Literal keywords: `expression` or `variable`. • `expression` (default): `FILTER` contains an expression. • `variable`: `FILTER` contains the name of a variable holding an expression string.
`FILTER`	The filter to apply, either an expression (e.g. `"{ NOT alias.d.deleted }"`) or the name of a variable containing such an expression (e.g. `"l.filter"`).

Operation#

The FILTER expression is retrieved once, stored internally, and re-executed for the life of the filter. Starting from the current record in the database, records are searched until the first match is found.

This can impact performance: if the filter yields few or no matches, much of the database may be scanned. In the example above, most records are not deleted, so the filter will find a match quickly.

If you need to find a series of matching records, you have two options:

Create an index on the desired field(s), use <MvFIND> to locate the first match, then loop with <MvWHILE> and <MvFIND> to find subsequent matches.
Use <MvFILTER> to make non-matching records invisible, then use <MvFIND> directly.

In this example, the database contains these records (sorted by salary):

Name	Title	Salary
Diamond	Bottle Washer	12000
Novak	Plongeur	15000
Maloney	Dish Washer	15000
Barr	Maitre d’	35000
Tchobanian	Chief Cook	40000
Rabinovitch	Window Washer	45000

The tag <MvFIND VALUE="15000"> moves the pointer to record #2 (Novak).

Applying <MvFILTER FILTER="{'Washer' CIN db_alias.d.title}"> makes non-matching records invisible. The database then appears as:

Name	Title	Salary
Diamond	Bottle Washer	12000
Maloney	Dish Washer	15000
Rabinovitch	Window Washer	45000

Running <MvFIND VALUE="15000"> again now lands on Maloney. Records invisible to the filter remain in the database but are skipped by <MvFIND>. Note that alias.d.recno cannot be used in a filter expression. Accessing a record excluded by the filter has undefined behavior.

Examples#

FILTER_TYPE = “variable”#

This mode lets you build the filter expression dynamically in a variable and then apply it.

<MvASSIGN NAME="l.filter_str"
          VALUE="{ '(NOT employees.d.deleted) AND (toupper(substring(employees.d.name,1,1)) EQ \'R\')' }">
<MvFILTER NAME="Products"
          FILTER="l.filter_str"
          FILTER_TYPE="variable">

Or, using a user-defined function to generate the filter:

<MvASSIGN NAME="l.filter"
          VALUE="{ Product_Filter(l.search) }">
<MvFILTER NAME="Products"
          FILTER="l.filter"
          FILTER_TYPE="variable">

FILTER_TYPE = “expression”#

If omitted, FILTER_TYPE defaults to "expression".

Since deleted records are not physically removed from the database until an <MvPACK> tag is executed, it is common practice to filter out deleted records, making them invisible to <MvFIND> and <MvSKIP>. In this example, deleted employees will not be returned when searched for with <MvFIND>.

<MvOPEN NAME="employees"
        DATABASE="{ g.path $ 'workers.dbf' }"
        INDEXES="emp_name.mvx">

  <MvFILTER NAME="employees"
            FILTER="{ NOT employees.d.deleted }">

  <MvFIND NAME="employees"
          VALUE="{ l.name }" 
          EXACT>