Filter Conditions |
Top Previous Next |
The Edit Filter Conditions dialog is used to enter one or more filtering expressions for Queries. Each expression is shown in a grid, with the typical functions to Add, Edit, Copy, Delete and Move the expressions. Since the expressions are used in the order shown here, this can be a speed consideration but otherwise is not an issue (see below).
Filtering conditions determine which records of a Query's Base Table are included in the Query. Without any filtering conditions, ALL records are included (which in some cases is what you want, e.g. to include all Sites).
Each filter condition is an expression that results in a boolean value (True or False). It's important to remember that all of the conditions must be "met", or "True", for a given record to be included in the Query. When filtering a record for the Query, each expression is executed in the order specified, until a False condition is found. If no condition returns False, then the record passes the test and it's included in the Query. If a False is found, it stops executing filter expressions (to save time) and the record is not included.
The most common elements of the filter conditions are the context functions for the data range that the user selects for the Query (e.g. the From and To dates on most Tab views). For instance, in a Reservation Query you can include only Reservations that exist in the date range with the following filter condition:
Resv:Resv_Last_Date >= FromDate() AND Resv:Resv_First_Date <= ToDate()
This is just like the On Site view date filtering. By changing the Last or First date fields, you can change it to include reservations arriving or departing on the selected dates. Similar filtering would be done for Transactions using the Tran:Tran_Date field descriptor.
Other common filter conditions would be for including only active reservations (using the ResvIsActive function), including only checked-in reservations (using the Resv:Resv_Status field descriptor), or including only reservations on a site (using the expression Site() != NullRecord()).
Quick-Filter Auto-Builder
This is a handy shortcut to adding common filtering to a Query. Click that button, and you will be shown the normal Filter dialog according to the Base Table you're using for the Query. This may be used for queries based on Reservations, Sites, Transactions, Inventory Items, or Purchase Orders.
In the Filter dialog, select the filtering you would like to use, for instance just as if you're using the Find Reservation function. When you click OK, one or more Expressions will be added to the filter conditions which will emulate the filtering you selected.
You can use this multiple times if needed, and it will keep adding the appropriate expressions. While once is usually enough to cover everything, you may need to do this to select more than one type of date range filtering for reservations (for instance to select reservations Made during a certain range that were also Cancelled during a certain range).
You'll also note that when a date filter is selected on one of those filters, a special option will be shown to use the Query 'From and 'To' dates rather than a fixed date range. This is important so the Query's From and To dates are adjustable and will affect the filter appropriately, if you want that ability.
Text Search Conditions
If the Query includes a Text filter, then you need to include a condition for that. The text to be searched (entered by the user with the F9 key or Search button) is available in the context function ThisSearchText. Here's an example filter expression to look up transactions that include the entered text in their receipt number:
ThisSearchText() != "" AND Find(Tran:Tran_Invoice,ThisSearchText()) != 0
Note that the expression above also checks for the search text being blank, so that if no text is entered then it will result in False -- no records will pass the test. That keeps the Query from showing all records until search text is entered (which could be rather slow if this is the only condition).
Speed Considerations
Note that any number of filter condition expressions can be used. Technically, you could include all filter conditions in one expression (using "AND" logic), but this can actually result in a slower query because it has to execute every part of the expression. If the filtering is broken up into multiple expressions, then it only executes the expressions until a False condition is found and then it can skip the rest.
If speed is a significant issue, you can rearrange the conditions to put the most likely "False" conditions at the top (so less expressions are executed for most records). Or if some expressions are more complicated than others, you can put the "faster" expressions at the top and leave any really slow ones for last, which would only executed if the record passes all other "quick" tests.
Also note that all filter expressions are pre-parsed -- that is, the parsing portion of the expression processing is done only once each time the Query is refreshed, so it doesn't have to be done every time the expressions are used to filter a record.
Filter Expression Errors
It's important to know that if a filter expression results in an error, then the record is considered to have passed the test (the same as the condition resulting in True). The reason for this is that it's easier to locate the problem if it shows too many records than if it shows none at all.
Additional Topics:
Advanced Customizations Overview & other topics