Difference between revisions of "Export Merge Data"
(→ProperCase( cString )) |
(→Student Information Functions) |
||
Line 281: | Line 281: | ||
<code>LkIsBook( register.locksource) | <code>LkIsBook( register.locksource) | ||
+ | |||
+ | === LkIsStu( cLockSources ) === | ||
+ | |||
+ | Returns true if the student is on hold for Student Services | ||
+ | |||
+ | <code>LkIsStu( register.locksource) | ||
=== MealPlan() === | === MealPlan() === |
Revision as of 02:21, 8 June 2011
Contents
- 1 Introduction
- 2 Criteria
- 3 Criteria Editing Screen
- 4 Functions Useful In Export Operations.
- 4.1 Text Manipulation Functions
- 4.1.1 ALLTRIM( cString )
- 4.1.2 AT( cSearch, cString )
- 4.1.3 LEFT( cString, nCharacters )
- 4.1.4 LEN( cString )
- 4.1.5 LOWER( cString )
- 4.1.6 LTRIM( cString )
- 4.1.7 Properize( cString )
- 4.1.8 RAT( cSearch, cString )
- 4.1.9 RIGHT( cString, nCharacters )
- 4.1.10 RTRIM( cString )
- 4.1.11 SOUNDEX( cValue )
- 4.1.12 SPACE( nSpaces )
- 4.1.13 STR( nValue, nLength, nDecimals )
- 4.1.14 SUBSTR( cString, nStart, nCharacters )
- 4.1.15 TRIM( cString )
- 4.1.16 UPPER( cString )
- 4.2 Date Manipulation Functions
- 4.1 Text Manipulation Functions
- 5 = Academic Session Functions =
- 5.1 AddOneSess( cYr, cSess )
- 5.2 ComplAYS( cYr )
- 5.3 Before( cTest, cLimit )
- 5.4 Student Information Functions
- 5.4.1 IsInCourses( cYear, cTerm cCourse, cMark, lIncludeWD )
- 5.4.2 IsInRez( cYear , cTerm )
- 5.4.3 LkIsReg( cLockSource )
- 5.4.4 LkIsBus( cLockSource )
- 5.4.5 LkIsLib( cLockSource )
- 5.4.6 LkIsBook( cLockSource )
- 5.4.7 LkIsStu( cLockSources )
- 5.4.8 MealPlan()
- 5.4.9 Occupancy()
- 5.4.10 HasFinAid(nEntCode, cYear, nFinAidID)
- 6 = Functions working with multiple data types =
- 7 Comparison Operators
- 8 Logical Operators
- 9 Database Fields
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.