Revision as of 22:48, 7 September 2009

Introduction

CrossRoad has a filtering function that permits you to restrict the display of either prospective or current students to those who meet particular criteria.

CrossRoad also allows you to export data in a format that is suitable for mailmerge operations, particularly in Microsoft Word. This is achieved through the use of a filter.

These pages are intended to assist you in utilizing these features.

Criteria

The most important skill is in learning to construct criteria successfully. Constructing this criteria takes a little practice, but fortunately the criteria can be saved for reuse and can be edited (from Support Tables | Criteria).

The criteria must result in a logical result -- a true or false answer where true includes the record in the export, and false excludes it. For the most part this means comparing the contents of database fields to some value. The value depends on the kind of data field (see the tables below for some guidance about what fields are what datatype):

• character fields must be compared to character values (enclosed in "");
• numeric fields must be compared to numbers (typed simply as numbers);
• logical fields do not need to be compared, since they are true or false on their own;
• date fields must be compared to dates, which must be entered using the ctod() function, or compared to the current date by use of the SISDate() function;

Comparisons are made by the use of relational operators such as < > =. A section below gives guidance on the use of these.

Comparisons can be combined by using logical operators such as .and. or .or.. These too are outlined below. The proper use of ( ) to get clauses to be resolved in the right order for your expected result. In general, the order in which things are done is:

1/ Expressions inside ( )

2/ Relational operators (> < =, etc)

3/ .NOT.

4/ .AND.

5/ .OR.

Two operators at the same level are resolved from left to right.

Criteria Editing Screen

This screen has four major parts:

Table Field List

This list displays all the fields available for creating this criteria. The list entries contain two parts, the table name followed by a . and the field name. Clicking the field list entry will insert the field in the editing box.

Function List

The middle box displays a number of functions that are available for creating criteria. Clicking the function will insert the function including the example parameters into the editing box. You would then enter either literal values or field entries for the parameters.

Operator List

The operators that are available are displayed. Again, when those are clicked they are inserted into the editing box.

Editing Box

This is the place the criteria is actually built. Aside from selecting entries from the top three lists, you may also type and edit entries in this box.

Functions Useful In Export Operations.

Note that below arguments for functions are descriptive with a c prefix for those which must be characters, a n for those which must be numbers, a d for dates, and a l for logical values. A field of the correct type can also be used as an argument.

Comparison Operators

"=", equal

This operator works with any data type. This works slightly differently depending on data type. For dates and numbers, the operator works as you would expect.

This operator compares all characters in the string on the right of the operator with the string on the left. If all characters on the right equal those on the left, even if there are still characters remaining on the left, the result is true. If the character string on the right of the operator is an empty string, it is equal to any string on the left of the operator. So “SMITH” = “SMITHY” is true, but “SMITHY” = “SMITH” is not. And “SMITH” = “” is also true.

"==", exactly equal

This operator works with any data type. This is just like the = operator, except that for character strings, they must be the same length to be equal.

"<>", not equal

This works with any data type. It works as you would expect. With character data, it works as the = operator.

">", greater than

">=", greater than or equal to

"<", less than

"<=" less than or equal to

These operators all work with any kind of data. For character data, the comparison is based on the ASCII code, which means that uppercase come before lower, and numbers come before both.

"$", is contained in

This operator works with character data. The test is whether the value on the left of the operator is contained in the value on the right. This too is an ASCII comparison, so case is significant.

Logical Operators

.AND. (note the periods on either side of these -- the operators do not work without them) combines two clauses, both of which must be true for the entire expression to be true.

.OR. combines two clauses, one or more of which must be true for the entire expression to be true.

.NOT. reverses the result of a single clause, so that a true clause becomes false and a false clause true.

( ) can be used to create subclauses, forcing the expressions contained in them to be resolved before those outside them.

Database Fields

Current Student Databases

MASTER Structure

APPLICS Structure

XTRA Structure

REGISTER Structure

REZ Structure

Prospective Student Databases

LIAISON Structure

Archive Student Databases

ARCMAST Structure