Latest revision as of 05:03, 3 February 2012

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.

Replaceable parameters are available. Anything entered between square brackets [ ] will generate a query for those values when the criteria is first evaluated. The text in the square brackets will be used as a prompt.

Three specialized parameters exist and are marked by special characters at the beginning of the string inside the square brackets. AY: will ask for an academic year, AT: for an academic term, and D: for a date. Anything appearing after the colon will be used as text for the prompt.

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.

Text Manipulation Functions

ALLTRIM( cString )

Remove leading and trailing spaces from a character string. This makes comparing database fields easier.

Alltrim(master.surname) == "SMITH" selects all SMITHs

AT( cSearch, cString )

Return the position of a substring within a character string.

At("BEEK",master.surname) > 0 selects all names with "BEEK" in them.

LEFT( cString, nCharacters )

Extract substring beginning with first character in a string

Left(master.surname,4) == "VANB" Selects all records starting with VANB

LEN( cString )

Return the length of a string or number of array elements

Len(alltrim(master.surname)) < 4 Returns all whose surnames are shorter than 4 characters.

LOWER( cString )

Convert uppercase characters to lowercase. This makes comparisons easier as you can ignore case. See also UPPER()

Lower(master.surname) = "smith" selects all whose surname is smith, regardless of case.

LTRIM( cString )

Remove leading spaces from a character string

LTrim(master.addr1)=="112 Woodstock" Selects this address, even if it was entered as " 112 Woodstock"

Properize( cString )

Convert string to initial capital letters.

Properize(master.fname) = "Glenn" Selects GLENN or glenn or Glenn or gLENN...

RAT( cSearch, cString )

Return the position of the last occurrence of a substring. Like AT() but works from the end of the string.

Rat("er",master.surname) > 5 Selects all where er occurs a the 6th position or higher

RIGHT( cString, nCharacters )

Return a substring beginning with the rightmost character

Right(master.surname,4)=="BEEK" Selects all where BEEK are the last four letters.

RTRIM( cString )

Remove trailing spaces from a character string

Rtrim( master.surname) == "SMITH" Selects all the SMITHs, even though the database field has 15 additional spaces after the name.

SOUNDEX( cValue )

Convert a character string to "soundex" form

soundex(master.surname) == soundex("smith") Selects all the sound like SMITH.

SPACE( nSpaces )

Return a string of spaces. This is sometimes easier than typing a specific number of spaces.

register.degree == "BA"+space(2)

<code>str(register.advcred,6,2) > " 12.25" which would select everyone with 12.25 advance credits

SUBSTR( cString, nStart, nCharacters )

Extract a substring from a character string

substr(master.surname),2,3) == "AND" selects all names whose 2nd, 3rd and 4th letters are AND.

TRIM( cString )

Remove trailing spaces from a character string. See Rtrim.

See Rtrim example.

<code>Upper(master.surname) = "SMITH" Selects all SMITHs regardless of case.

AddressBlk( cName, cAddress1, cAddress2, cCity, cState, cZip )

 Returns a string of a formatted address block.

Asc( cCharacter )

Returns the integer ASCII value for the character.

AtNoCase( cSearchFor, cSearchIn )

Returns the integer position of the character in the string regardless of case.

AtLineNum( cSearchFor, cSearchIn )

Returns the integer line position of the character in the multiline string.

AtLineNoCase( cSearchFor, cSearchIn )

Returns the integer line position of the character in the multiline string regardless of case.

Chr( nNumber )

Returns the character corresponding to the ASCII value specified.

Descend( cString )

Returns a descending order key string for the passed string.

HardCR( cString )

Replaces soft returns with hard returns in the passed string.

IsAlpha( cString )

Returns true if the string begins with an alphabetic character.

IsDigit( cString )

Returns true if the string starts with a digit.

IsLower( cString )

Returns true if the string starts with a lower case character.

IsUpper( cString )

Returns true if the string starts with an upper case character.

MemoLine( cString , nLineLength , nLineNumber , nTabSize , lWrap )

Returns the specified line from the passed memo.

MemoTran( cString , cHardCR , cSoftCR )

Returns the memo with hard and soft carriage returns replaced as specified.

MLCount( cString , nLineLength , nTabSize , lWrap )

Returns an integer of the number of lines in the passed memo.

NumTrim( nNumber )

Returns a string of the passed number.

OccursIn( cSearchFor, cSearchIn )

Returns true if the sought value is found in the passed string.

Replicate( cString, nTimes )

Returns a string of the character(s) repeated the specified number of times.

StrTran( cSearchFor , cSearchIn , cReplaceWith , nStart , nCount )

Returns a string with the specified replacement made.

Stuff( cString , nStart , nDelete , cInsert )

Returns a string with the inserted and/or deleted characters.

Date Manipulation Functions

CDOW( dDate )

Convert a date value to a character day of the week

CDOW(master.birthday) == "Monday" selects students who were born on a Monday.

CMONTH( dDate )

Convert a date to a character month name

Cmonth(applics.admdate) == "July" selects students admitted in July of any year.

CTOD( cString )

Convert a date string to a date value

master.birthday <= CToD("01/09/1980") selects those born on or before 1 Sep 1980.

Today()

Return the system date as a date value

master.birthday = SISDate() selects students born today.

DAY( dDate )

Return the day of the month as a numeric value

day(master.birthday) = 10 selects students born on the 10th of any month.

DOW( dDate )

Convert a date value to a numeric day of the week. Sunday is 1, Saturday is 7.

dow(master.birthday) = 2 selects students born on a Monday

DTOC( dDate )

Convert a date value to a character string

dtoc(applics.appldate) = "01/02/1998" selects applicants who applied on 1 Feb 1998.

DTOS( dDate )

Convert a date value to a string formatted as yyyymmdd

dtos(applics.appldate) = "19980201" selects applicants who applied on 1 Feb 1998.

MONTH( dDate )

Convert a date value to the number of the month

month(applics.appldate) = 12 selects applicants who applied in December of any year.

SECONDS()

Return the number of seconds elapsed since midnight. This returns the number of seconds since midnight, to two decimals.

round(seconds(),0) % 2 = 0 selects students whose records are being looked at on even seconds.

YEAR( dDate )

Convert a date value to the year as a numeric value

year( applics.admdate) = 1998 selects students admitted in 1998

ElapsedTime( cTime1, cTime2 )

Returns a string of the difference between the two specified times.

HoursBetween( dDate1, cTime1, dDate2, cTime2, lWeekEnds )

Returns the number of hours between the specified dates and times.

MakeDate( nYear , nMonth , nDay )

Returns a date based on the passed integers.

MakeTime( nHours , nMinutes , nSeconds )

Returns a time string based on the passed values.

MinutesBetween( dDate1, cTime1, dDate2, cTime2, lWeekEnds )

Returns the minutes between the specified dates and times.

Num2CDOW( nDay )

Returns a string of the specified day number.

Num2CMonth( nMonth )

Returns a string of the specified month number.

Sec2Days( nSeconds )

Returns an integer of the number of days represented by the specified seconds.

Time()

Returns the current time as a string.

Academic Session Functions

AddOneSess( cYr, cSess )

Returns a new academic year string incremented by one session.

AddOneSess(applics.regnyr,applics.entsem) == "1998/994" Selects students who applied in 1998/993

ComplAYS( cYr )

Completes an academic year string given the initial year. So 1998 becomes 1998/99.

ComplAYs("1998") = applics.regnyr selects students applying in 1998/99

Before( cTest, cLimit )

Returns true if academic year cTest is before cLimit.

Before(applics.RegnYr+applics.entsem,"1996/971") selects students who applied for terms prior to 1996/971.

Student Information Functions

IsInCourses( cYear, cTerm cCourse, cMark, lIncludeWD )

Returns true if registered and not withdrawn in courses for cYear and cTerm (and optionally in the specific course with the specific grades in a ~ delimited list). The Year or Term may be a * to match any.

IsInCourses("1998/99","1") selects students registered for term 1 of 1998/99.

IsInRez( cYear , cTerm )

Returns true if the student is in residence in the academic year and (optionally) term specified.

IsInRez("2008/09","1") selects students in the residence file for 2008/09 Fall.

LkIsReg( cLockSource )

Returns true if student is on hold for Registry

LkIsReg( register.locksource)

<code>MealPlan( "2010/11" ) == "15" selects all students who are on the 15 meals/week plan.

Occupancy( cYear )

Returns the character string representing the occupancy (double or single).

Occupancy( "2010/11" ) == "D" selects students with double occupancy.

HasFinAid(nEntCode, cYear, nFinAidID)

Returns true if student was awarded nFinAidID in cYear. Omit nFinAidID to query if ANY aid was awarded.

HasFinAidID(master.entcode,"2000/01") selects students awarded any financial aid in 2000/01

HasFinApp( nFile#, cYear, nFinAidID )

Returns true if the student has applied for the specified item.

HasFinApp(master.entcode,"2010/11",141)

Returns true if the individual has a communication log entry matching the specified parameters.

Returns true if the individual has a loan for the specified year and term.

Returns the value of the true or false expression based on the condition.

Returns true if the student's course records for the specified year and term are missing tags.

Returns the global setting for the item in CrossRoad.

Returns the term in which a student entered residence in the specified year.

Returns true if the student has cable selected in the year's residence record.

Returns true if the student has network selected in the year's residence record.

Returns true if the student has previously confirmed registration.

Returns true if the student applied between the two dates.

Returns true if the student applied before the specified date.

Returns true if the student applied after the specified date.

Returns a floating point number of credits for the specified year and term. Withdrawn are counted only if the last parameter is true.

Returns the long distance code for the residence record for the year and term.

Returns the extension for the residence record for the year and term.

Returns the MAC address for the residence record for the year and term.

Returns the room number for the residence record for the year and term.

Returns the name of the region for the passed postal code.

Returns true if the student has applied for the specified year and term.

Returns true if the student has been admitted for the specified year and term.

<code>empty(master.gradyr) selects all the students who have NOT applied to graduate.

PADL( xValue, nLen, cPadChar )

Pad character, date, and numeric values with a fill character

padL( register.advcred,10,"X") == "XXXXX12.00" selects students with 12 credits of advance credit.

PADR( xValue, nLen, cPadChar )

Pad character, date, and numeric values with a fill character

padL( register.advcred,10,"X") == "XXX12.00XX" selects students with 12 credits of advance credit.

PADC( xValue, nLen, cPadChar )

Pad character, date, and numeric values with a fill character

padR( register.advcred,10,"X") == "12.00XXXXX" selects students with 12 credits of advance credit.

Numeric Manipulation Functions

ABS( nValue )

Return the absolute value of a numeric expression

<code>Integer( register.advcred ) = 12 selects students with 12.00 - 12.99 credits of advance credit.

ROUND( nValue, nDecimals )

Return a numeric value rounded to a specified number of digits

round( register.advcred) = 12 selects students with 11.50 - 12.49 credits of advance credit.

VAL( cString )

Convert a character number to numeric type

val(master.yrstdy) >= 2 selects students in year two or greater of their program.

Ceiling( nNumber )

Returns the next highest integer from the floating point number.

Cosine( nAngle )

Returns the floating point cosine of the angle.

Cotangent( nAngle )

Returns the cotangent of the angle.

Log( nNumber )

Returns the log of the passed number.

IsBetween( nTestNumber, nLowerLimit, nUpperLimit )

Returns true if the test is between the limits.

Max( dnValue1 , dnValue2 )

Returns the larger of the two passed values.

Min( dnValue1 , dnValue2 )

Returns the smaller of the two specified values.

Modulus( nDividend , nDivisor )

Returns the remainder of the division operation of the parameter.

Power( nNumber, nExponent )

Returns the floating point value of the number raised to the exponent.

Rand( nSeedNumber )

Returns a random value using the passed seed. Note that for a truly random value varying seeds must be used.

Sine( nAngle )

Returns the sine of the specified angle.

SqRt( nNumber )

Returns the square root of the number.

Tangent( nAngle )

Returns the tangent of the angle.

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