Formula Based Filter Properties Window

Filter Name

Accepts a name for the filter. If you do not enter a filter name, Monarch Classic will automatically assign a name using the first field name it encounters in your filter expression. If this name has already been used to name another filter, you will be prompted for a name.

Note: Filter names must adhere to the naming conventions used for long names.

Comment

Displays comments that may have been added to the filter during creation.

Fields

Displays a list of all fields, which you can use in your filter expressions.

Hover your mouse cursor on a field to display  its field type.

Operators

Displays a list of available comparison operators and logical operators. The operators list also includes parenthesis, braces (to enclose date values), quotes (to enclose character strings) and special comments brackets.

Hover your mouse cursor on an operator to display a brief description of the operator.

Functions

Displays a list of the available database functions. When a function is selected, its syntax and a brief description of the function displays in the dialog's status bar.

Expression

Accepts a valid filter expression. Filter expressions may be up to 32768 characters in length and may include field names, operators, functions, and constants. For more information, see Creating expressions.

Clear

Deletes the contents of the Expression box.

Zoom Slider

Slide to the left or right to zoom the expression box (i.e., make text smaller or larger).

Make this the current filter

Check this box to make the filter the current filter.

Duplicate Handling options (formula-based filters only)

Don’t care about duplicates

Select if you want duplicates to be ignored (i.e., duplicate fields will not be displayed).

Select rows according to their uniqueness with respect to the specified keys

Select if you want Monarch Classic to select only rows that satisfy the filter expression AND have unique values with respect to the selected keys. When selecting this option, select one or both of the following:

• Unique rows:  Select to have Monarch Classic show only unique rows (i.e., no duplicate rows) for the specified keys.

• Duplicated rows: Select to have Monarch Classic show only rows that satisfy the filter expression AND are such that there exist only rows which have duplicate key values.

• • One row only: Select to have Monarch Classic display only one of the duplicate rows for any of the specified key(s). If there is an active sort, then the first row with respect to the sort will be selected.

  • All duplicated rows: Select to have Monarch Classic display all of the duplicated rows for the specified key(s).

Note: An example of utilizing a sort with a duplicate filter might be a list of equipment with a customer and service date. If you want to show only the last service date of each piece of equipment for each customer, then you would select the Customer and Equipment fields as keys, select the one row only option in addition to creating a descending sort on the Service Date field.

Specified keys

Click on the adjacent check boxes to specify the fields (keys) to which you want to apply the selected duplicate handling option. For this option to be available, you must select the Select rows according to their uniqueness with respect to the specified keys option above. Note: At most, 32 keys may be specified. If more than 32 are checked, then an error message displays when switching to the Formula tab or pressing the OK button.

n specified

Indicates how many fields (keys) have been selected.

Row Count Limit

These options are provided to allow you to place an absolute limit on the number of records available to the table window. You can use this feature to perform auditing, where you might want to work with only a sample of the available data, or you might use this feature when defining summaries and calculated fields (by limiting the amount of data, Monarch Classic will build calculated fields and summaries faster). After you complete your summary or calculated field definitions, you can remove the limit to work with all data.

No limit

Use this setting to return all records that pass the filter expression.

First n Rows

Use this setting to limit the number of records returned by the filter. Monarch Classic first applies the filter expressions to all records, then the table is sorted (if a sort definition is active), then the table is truncated to the number of records you specify. If the filter returns fewer records than you specified, this option has no effect. You may specify a number in the range 1 – 150000.

Make this the current filter

Click to make this the current filter. The filter is applied once you click OK on the Filter Definition dialog.