Filtering and Sorting Data

Overview

Column criteria and script files assist you in filtering and sorting the data returned from the server, helping to streamline your work priorities.

Column Filtering using Column Criteria

Column criteria filtering comes into affect when the LoadBrowser program setting for the particular program is set to enabled. In this case, the browse screen has a File » Properties menu option that can be used to filter records in the available record set, for display, reporting, etc.

This filter option is used to selectively choose records in the recordset using SQL script. This affects other properties such as AbsolutePosition, AbsolutePage, RecordCound, and PageCount that return values based on the cursor. This is because when a Filter property is set to a specific value, the cursor moves to point to the first record that satisfies the new value.

The criteria string is made up of clauses in the format FieldName-Operator-Value (for example, LastName = 'Smith'). Compound clauses can be created by concatenating individual clauses with AND (for example LastName = 'Smith' AND FirstName = 'John') or OR (for example, LastName = 'Smith' OR LastName = 'Jones').

Use the following guidelines for criteria strings:

• FieldName must be a valid field name from the appropriate CCLAS database table. If the field name contains spaces, enclose the name in square brackets.
• Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.
• Value is the value which the field values are compared with (for example, 'Smith', #8/24/00#, 12.345 or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can contain wild cards. Only the percent sign (%) wildcard is allowed. Value cannot be Null.

There is no precedence between AND and OR. Clauses can be grouped within parentheses. Note that clauses cannot be grouped when they are joined by an OR and then the group joined to another clause with an AND.

Example:

('LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, the filter would be constructed as:

(LastName = 'Smith' AND FirstName = 'John') OR (LastName = 'Jones' AND FirstName = 'John')

In a LIKE clause, a wildcard can be used at the beginning and end of the statement (for example, LastName LIKE '%mit%').

Setting the Filter property to a zero-length string has the affect of clearing the filter, and the current record position moves to the first record in the record set.

Column Filtering using Script Files

Filtering data using script files comes into affect when the LoadBrowser program setting for the particular program is set to disabled. In this case, the list of records displayed in the list-view can be filtered by using the particular user query script.

The % wild card is generally used when running a query to load all records for the list-view panel. It operates on the same principle as any SQL query. Queries can be run filling in one or more of the available fields, only records that satisfy those criteria are loaded into the browser screen.

Process

To add a filter, perform the following:

1. Click Add to add a new criterion.
2. Enter the filter a name and the SQL query.
3. Select the filter that is to be made active.
4. Click Set Default.
5. Click Apply.
6. Click OK.

To cancel the current filter, perform the following:

1. Click Clear Default.

To modify the current filter, perform the following:

1. Click Modify.

To delete the current filter, perform the following:

1. Click Delete.

To sort data in list views:

1. Click a column heading in the list view in the right-hand pane to sort the list by the data in the column. The data is sorted alpha-numericaly, except for date/time columns that are sorted by the date, provided that the dates are formatted using either:
   • YYYY/MM/DD
   • YY/MM/DD
   • DD/MM/YYYY
   • DD/MM/YY
   • MM/DD/YYYY
   • MM/DD/YY