Revision as of 02:14, 8 June 2011 (view source) WikiSysop (talk | contribs) (→‎ProperCase( cString )) ← Older edit		Revision as of 02:21, 8 June 2011 (view source) WikiSysop (talk | contribs) (→‎Student Information Functions) Newer edit →
Line 281:		Line 281:
	<code>LkIsBook(&nbsp;register.locksource)		<code>LkIsBook(&nbsp;register.locksource)
		+
		+	=== LkIsStu(&nbsp;cLockSources&nbsp;) ===
		+
		+	Returns true if the student is on hold for Student Services
		+
		+	<code>LkIsStu(&nbsp;register.locksource)
	=== MealPlan() ===		=== MealPlan() ===

---

Revision as of 02:21, 8 June 2011

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.

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)

STR( nValue, nLength, nDecimals )

Convert a numeric expression to a character string

<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.

UPPER( cString )

Convert lowercase characters to uppercase

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

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

= 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)

LkIsBus( cLockSource )

Returns true if student is on hold for Business Office

<code>LkIsBus( register.locksource)

LkIsLib( cLockSource )

Returns true if student is on hold for Library

<code>LkIsLib( register.locksource)

LkIsBook( cLockSource )

Returns true if student is on hold for Other

<code>LkIsBook( register.locksource)

LkIsStu( cLockSources )

Returns true if the student is on hold for Student Services

<code>LkIsStu( register.locksource)

MealPlan()

Returns the character string representing the meal plan the student is on.

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

Occupancy()

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

Occupancy() == "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

= Functions working with multiple data types =

EMPTY( xValue )

Determine if the result of an expression is empty

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

INT( nValue )

Convert a numeric value to an integer. Note that this does NOT round, but truncates the number

<code>int( 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.

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