If the Find Data / Filter Record button appears on the top button bar of a record or journal viewer, you can quickly narrow down (filter) the records that are displayed on the listing. When you click the Filter Record button, a screen will appear allowing you to enter criteria to filter the records by. You may assign a name to and save your customized filter range settings as discussed in Saving a Filter Range. . Ctrl+F may be used to toggle in and out of filter view.
Filter Keys:
The ‘Filter Keys’ selector pane is similar to the sort order selector at the bottom of the viewer record listing, and will initially be set to the same value. The Filter Keys pane controls how the list of records will be ordered, as well as which fields will be displayed; simply click on the desired order to select it.
Filter Ranges:
The ‘Filter Ranges’ selector pane enables you to select a customized range for the records. When you click on a range, the criteria defining it will appear in the Id, Date and Text ranges below. Creating a customized filter range is discussed below.
By double-clicking on an Id range, you will display its "Take Back" viewer. At this point, simply double-click on the Id you wish to take back to the filter.
TIP: Using SQL’s if an Id Range is Not Listed
SQL’s can be run from the SAM Pro range filter screens to effectively filter on columns that do not have separate range fields. Click Using SQL statements in Viewer Ranges for details.
When you initially access a filter, only active records will be selected. For more information on the automatic filtering of active records, click Active Record Criteria.
All Id's will be displayed if the Id field is blank.
A Single Id is specified by simply entering the Id (or double-click on the range to select from a list of Id's).
Several Id's can be specified by using a comma to separate the individual Id's.
Example: to include the items with the Id's EX1, SA3 and LT5 on the list, they would be entered as follows in the Id field: EX1,SA3,LT5
A Range of Id's is defined by the first and last items of the range separated by an underscore.
Example: To include all ID's from Adams to Collins, fill in the Id field as follows:
Adams_Collins
A string search for specific characters can be implemented by surrounding the text you wish to search for with the % sign.
Examples: To select only those inventory items whose Name includes the string ring, you would enter %ring% in the Name range. To include only those items whose Name begins with ring, you would enter ring%. To include only those items whose Name ends with ring, you would enter %ring.
A similar procedure would be used to filter all telephone numbers beginning with the 216 area code: %(216)%. Notice that we included parentheses around the area code so that it would appear as it does in the database.
Ampersand (&) in Filter Range implements 'AND' option. When the Ampersand character (&) is used as the first character in a filter range list, ALL of the elements in the list must be true to return a result.
Example: Inventory Item
In the Inventory Item Name range: &1%,%pex%,%g
The system will only return items whose name begins with '1', ends with 'g', and has 'pex' somewhere in the middle.
Disable the Following Characters from Search: The double hyphen character combination may be used to disable the rest of the characters on the line. Some examples are shown below.
'--A,B,C' ... removes the filter value entirely
'A,B --,C' ... removes 'C' from the list
Special Filtering for encapsulated GL Accounts has been added to the AP Journal, AR Cash Receipts Journal, AR Sales Journal, Bank Deposit and Bank Reconciliation Journals. Since multiple GL accounts are present in the ‘parent’ tables of the several of the Journals, simple range filters (where you simply enter the GL Account Id) do not work for these "encalcapulated" fields. To enable the user to filter on a specific parent GL Account, special filter ranges with the necessary SQL to filter on a specific GL Account have been added to following Journals:
AP Journal: GL AP Control Account and GL Bank Account filters
AR Cash Receipts Journal: GL Bank Account filter
AR Sales Journal: GL AR Control Account and GL Bank Account (for COD Cash Receipts) filters
Bank Deposit: GL Bank Account and GL Sweep Account Filters
Bank Reconciliation Journal: GL Bank Acount, GL Fees Account and GL Interest Account filters
Example on Using Filters for Encapsulated Fields: Bank Reconciliation Journal
Range filters for the GL Bank Acount Id:, GL Fees Account Id: and GL Interest Account Id: have been added to the range filter screen. When the range filter is initially displayed, the GL Bank Account range appears as follows:
GL Bank Account Id:
sql= gl_bnk_glaccnt_rn in (select glaccnt_rn from glaccnt where glaccnt_id like '%')
Suppose that your firm uses more then one Bank Account. To filter on Bank Reconciliation Entries for a specific GL Bank Account, simply replace the % in the SQL statement with the GL Bank Account Id you wish to filter on. For example, to filter on the GL Bank Account Id 1005, simply change the SQL statement in the range to:
sql= gl_bnk_glaccnt_rn in (select glaccnt_rn from glaccnt where glaccnt_id like '1005')
> (Greater Than)
< (Less Than)
>= (Greater Than or Equal To)
<= (Less Than or Equal To)
For example, <abcde means that the field value must be less than abcde to appear in the list or report.
= (Equal)
To query for blank fields, enter a filter line containing only = (the equal sign). Entering no operator is equivalent to preceding the value with =. For example, entering RT567 in a filter is equivalent to entering =RT567.
! (Not)
This is the NOT operator. For example, !> (not greater than) is equivalent to <= (less than or equal to). The ! may also precede a range so that !ddd_fff will include all values of the field except those in the range ddd to fff.
<> (operator)
This is synonymous with ! or !=. You may query for non-blank fields by entering a query line containing only !, !=, or <>.
If the entered query clause contains commas (to make an OR query) then the above operators may be applied to each sub-field. For example, the following two entries select values outside of a range and are equivalent:
!ddd_fff
<ddd,>fff
~ (Global Not)
The tilde (~) is also a NOT operator. When the first character is a ~ the rest of the filter indicates which records to exclude. For example, ~a,p,m will include all values except a, p or m.
After specifying the Id(s) of the record(s) you wish to appear, click the Show Data button to return to the record listing.
This button enables you to assign a name to and save your customized filter range settings.
To do so, begin by completing all range fields as desired.
Next, select a Filter Key to control the sort order of the records that you are filtering on.
Click the Save Range button.
A box will appear requesting that you enter the name of the range to save. Type in a descriptive range name, and click the OK button.
Filter Ranges for All Users
You can create user-defined filter ranges that are available to all users with access to these records. To do so, the first character of the named range should be an asterisk (e.g., *overhead jobs for Ohio). Named ranges beginning with an asterisk will appear on all users' filter ranges. The user that created the filter range is allowed to alter it. Also, users flagged as a Notes Administrator may delete global ranges defined by other users.
Your custom range will be listed in the Filter Ranges pane. When you access this range viewer in the future, you can select a saved range by clicking on it in the Filter Ranges pane.
To Remove or Clone a Report Range (V9.0+):
When the user right-clicks a saved Range, they will be prompted to Delete or Clone the Range for use by another User.
If Delete is selected, the range will be deleted if allowed: users may delete their own ranges. Notes Administrators may also delete Global ranges created by other Users.
If Clone is selected, the User Id field must be completed with the User the range should be given to (if the User already has a range of this name, a warning will be displayed). This User will 'own' their copy of the Range and may deleted if desired.