[= equation]

Advanced Evaluations using [= expression]

Schedule Wizard supports advanced expressions through the use of the [=] token. The expression entered within the [=] is evaluated as a Clarion language statement. Examples are:

FORMAT(TODAY(),@d12) returns 19991231

ROUND(45.2051,.01) returns 45.21

CHR(65) returns A

The following are useful valid functions that can be used:

Mathematical Functions

ABS (return absolute value) - ABS(expression)

INRANGE (check number within range) - INRANGE(expression,low,high) –

expression A numeric constant, variable, or expression.

low A numeric constant, variable, or expression of the lower boundary of the range.

high A numeric constant, variable, or expression of the upper boundary of the range.

The INRANGE function compares a numeric expression to an inclusive range of numbers. If the value of the expression is within the range, the function returns the value 1 for "true." If the expression is greater than the high parameter, or less than the low parameter, the function returns a zero for "false."

INT (truncate fraction) - INT(expression) - The INT function returns the integer portion of a numeric expression. No rounding is performed, and the sign remains unchanged.

LOGE (return natural logarithm) - LOGE(expression)

LOG10 (return base 10 logarithm) – LOG10(expression)

RANDOM (return random number) - RANDOM(low,high) - The RANDOM function returns a random integer between the low and high values, inclusively. The low and high parameters may be any numeric expression, but only their integer portion is used for the inclusive range.

ROUND (return rounded number) - ROUND(expression,order) –

expression A numeric constant, variable, or expression.

order A numeric expression with a value equal to a power of ten, such as 1, 10, 100, 0.1, 0.001, etc. If the value is not an even power of ten, the next lowest power is used; 0.55 will use 0.1 and 155 will use 100.

The ROUND function returns the value of an expression rounded to a power of ten.

SQRT (return square root) - SQRT(expression) - The SQRT function returns the square root of the expression. If X represents any positive real number, the square root of X is a number that, when multiplied by itself, produces a product equal to X.

Trigonometric Functions

SIN (return sine) - SIN(radians)

COS (return cosine) - COS(radians) –

radians A numeric constant, variable or expression for the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle.

The COS function returns the trigonometric cosine of an angle measured in radians. The cosine is the ratio of the length of the angle's adjacent side divided by the length of the hypotenuse.

TAN (return tangent) - TAN(radians)

ASIN (return arcsine) - ASIN(expression)

ACOS (return arccosine) - ACOS(expression)

ATAN (return arctangent) - ATAN(expression)

String Processing

ALL (return repeated characters) - ALL(string [,length])

CENTER (return centered string) - CENTER(string [,length])

CHR (return character from ASCII) - CHR(code) - The CHR function returns the ANSI character represented by the ASCII character code parameter.

CLIP (return string without trailing spaces) - CLIP(string) - The CLIP function removes trailing spaces from a string. The return string is a substring with no trailing spaces. CLIP is frequently used with the concatenation operator in string expressions using STRING data types. When used in conjunction with the LEFT function, you can remove both leading and trailing spaces (frequently called ALLTRIM in other languages).

DEFORMAT (return unformatted numbers from string) - DEFORMAT(string [,picture]) –

string A string expression containing a numeric string.

picture A picture token. If a picture token is not given, the return value will contain only characters that are valid for a numeric constant.

The DEFORMAT function removes formatting characters from a numeric string, returning only the numbers contained in the string. When used with a date or time picture (except those containing alphabetic characters), it returns a STRING containing the Clarion Standard Date or Time.

Example:

!DEFORMAT('$1,234.56') returns 1234.56

!DEFORMAT('309-53-9954') returns 309539954

!DEFORMAT('40A1-7',@P##A1-#P) returns 407

FORMAT (return formatted numbers into a picture) - FORMAT(value,picture) –

value A numeric expression for the value to be formatted.

picture A picture token.

The FORMAT function returns a numeric string formatted according to the picture parameter.

Return Data Type: STRING

Example:

Rpt:SocSecNbr = FORMAT(Emp:SSN,@P###-##-####P) !Format the soc-sec-nbr

Phone = FORMAT(DEFORMAT(Phone,@P###-###-####P),@P(###)###-####P)

!Change phone format from dashes to parens

DateString = FORMAT(DateLong,@D1) !Format a date as a string

GETINI (return entry from INI file) - GETINI( section ,entry [,default] [,file] ) –

section A string constant or variable containing the name of the portion of the INI file which contains the entry.

entry A string constant or variable containing the name of the specific setting for which to return the value.

default A string constant or variable containing the default value to return if the entry does not exist (up to 1023 characters). If omitted and the entry does not exist, GETINI returns an empty string.

file A string constant or variable containing the name of the INI file to search (looks for the file in the Windows directory unless a full path is specified). If the file does not exist, GETINI returns an empty string. If omitted, GETINI searches the WIN.INI file.

The GETINI function returns the value of an entry in a Windows-standard INI file (maximum file size is 64K). A Windows-standard INI file is an ASCII text file with the following format:

[some section name]

entry=value

next entry=another value

For example, WIN.INI contains entries such as:

[intl]

sLanguage=enu

sCountry=United States

iCountry=1

The GETINI function searches the specified file for the entry within the section you specify. It returns everything on the entry's line of text that appears to the right of the equal sign (=).

INLIST (return entry in list) - INLIST(searchstring,liststring,liststring [,liststring...]) –

searchstring A constant, variable, or expression that contains the value for which to search. If the value is numeric, it is converted to a string before comparisons are made.

liststring The label of a variable or constant value to compare against the searchstring. If the value is numeric, it is converted to a string before comparisons are made. There may be up to 25 liststring parameters, and there must be at least two.

The INLIST function compares the contents of the searchstring against the values contained in each liststring parameter. If a matching value is found, the function returns the number of the first liststring parameter containing the matching value (relative to the first liststring parameter). If the searchstring is not found in any liststring parameter, INLIST returns zero.

Return Data Type: LONG

Example:

!INLIST('D','A','B','C','D','E') returns 4

!INLIST('B','A','B','C','D','E') returns 2

INSTRING (return substring position) - INSTRING(substring,string [,step] [,start]) –

substring A string constant, variable, or expression that contains the string for which to search. You should CLIP a variable substring so INSTRING will not look for a match that contains the trailing spaces in the variable.

string A string constant, or the label of the STRING, CSTRING, or PSTRING variable to be searched.

step A numeric constant, variable, or expression which specifies the step length of the search. A step of 1 searches for the substring beginning at every character in the string, a step of 2 starts at every other character, and so on. If step is omitted, the step length defaults to the length of the substring.

start A numeric constant, variable, or expression which specifies where to begin the search of the string. If omitted, the search starts at the first character position.

The INSTRING function steps through a string, searching for the occurrence of a substring. If the substring is found, the function returns the step number on which the substring was found. If the substring is not found in the string, INSTRING returns zero.

LEFT (return left justified string) - LEFT(string [,length]) - The LEFT function returns a left justified string. Leading spaces are removed from the string.

string A string constant, variable, or expression.

length A numeric constant, variable, or expression for the length of the return string. If omitted, length defaults to the length of the string.

LEN (return length of string) - LEN(string) – Use with CLIP()

LOWER (return lower case) - LOWER(string)

MATCH (return matching strings) - MATCH( first, second [, mode ] ) - Returns true or false based on a comparison of the first two parameters passed.

first A string containing data to compare against the second parameter.

second A string containing data to compare against the first parameter.

mode An integer constant or equate which specifies the method of comparison. If omitted, a wild card comparison is the default.

The MATCH function returns true or false as to whether the first and second parameters match according to the comparison mode specified. The following mode value EQUATEs are listed in EQUATES.CLW:

Match:Simple
A straight-forward equivalence comparison (first = second), which is most useful when combined with Match:NoCase

Match:Wild (default)
A wild card match with the second parameter containing the pattern that can contain 'asterisk (*) to match 0 or more of any character, and question mark (?) to match any single character.

Match:Soundex
A standard soundex comparison of the two strings, returning true if they have the same soundex value.

Match:NoCase
Add to the mode for a case insensitive match (except Soundex).

NUMERIC (return numeric string) - NUMERIC(string) - The NUMERIC function returns the value 1 (true) if the string only contains a valid numeric value. It returns zero (false) if the string contains any non-numeric characters. Valid numeric characters are the digits 0 through 9, a leading minus sign, and a decimal point. DEFORMAT is used to return unformatted numbers from a formatted string.

RIGHT (return right justified string) - RIGHT(string [,length]) - The RIGHT function returns a right justified string. Trailing spaces are removed, then the string is right justified and returned with leading spaces.

SUB (return substring of string) - SUB(string,position,length) –

string A string constant, variable or expression.

position A integer constant, variable, or expression. If positive, it points to a character position relative to the beginning of the string. If negative, it points to the character position relative to the end of the string (i.e., a position value of -3 points to a position 3 characters from the end of the string).

length A numeric constant, variable, or expression of number of characters to return.

The SUB function parses out a sub-string from a string by returning length characters from the string, starting at position.

UPPER (return upper case) - UPPER(string)

VAL (return ASCII value) - VAL(character)

character A one-byte string containing an ANSI character.

The VAL function returns the ASCII code of a character.

Bit Manipulation

BAND (return bitwise AND) - BAND(value,mask) - BAND compares the value to the mask, performing a Boolean AND operation on each bit. The return value is a LONG integer with a one (1) in the bit positions where the value and the mask both contain one (1), and zeroes in all other bit positions. BAND is usually used to determine whether an individual bit, or multiple bits, are on (1) or off (0) within a variable.

BOR (return bitwise OR) - BOR(value,mask) - The BOR function compares the value to the mask, performing a Boolean OR operation on each bit. The return value is a LONG integer with a one (1) in the bit positions where the value, or the mask, or both, contain a one (1), and zeroes in all other bit positions. BOR is usually used to unconditionally turn on (set to one), an individual bit, or multiple bits, within a variable.

BXOR (return bitwise exclusive OR) - BXOR(value,mask) - The BXOR function compares the value to the mask, performing a Boolean XOR operation on each bit. The return value is a LONG integer with a one (1) in the bit positions where either the value or the mask contain a one (1), but not both. Zeroes are returned in all bit positions where the bits in the value and mask are alike.

BSHIFT (return shifted bits) - BSHIFT(value,count) - The BSHIFT function shifts a bit value by a bit count. The bit value may be shifted left (toward the high order), or right (toward the low order). Zero bits are supplied to fill vacated bit positions when shifting. If count is positive, value is shifted left. If count is negative, value is shifted right.

Date / Time Processing

TODAY (return system date) - TODAY( ) - The TODAY function returns the operating system date as a standard date. The range of possible dates is from January 1, 1801 (standard date 4) to December 31, 2099 (standard date 109,211).

CLOCK (return system time) - CLOCK( ) - The CLOCK function returns the time of day from the operating system time in standard time (expressed as hundredths of a second since midnight, plus one). Although the time is expressed to the nearest hundredth of a second, the system clock is only updated 18.2 times a second (approximately every 5.5 hundredths of a second). Use with the FORMAT() function and picture tokens. Example: FORMAT(CLOCK(),@t3) returns 2:00pm.

DATE (return standard date) - DATE(month,day,year) - The DATE function returns a standard date for a given month, day, and year. The month and day parameters do allow positive out-of-range values (zero or negative values are invalid). A month value of 13 is interpreted as January of the next year. A day value of 32 in January is interpreted as the first of February. Consequently, DATE(12,32,97), DATE(13,1,97), and DATE(1,1,98) all produce the same result. The century for a two-digit year parameter is resolved using the default "Intellidate" logic, which assumes the date falls in the range of the next 20 or previous 80 years from the current operating system date. For example, assuming the current year is 1998, if the year parameter is "15," the date returned is in the year 2015, and if the year parameter is "60," the date returned is in 1960.

DAY (return day of month) - DAY(date) –

date The date must be a standard date. A variable declared with a date picture token is automatically converted to a standard date intermediate value. The DAY function computes the day of the month (1 to 31) for a given standard date.

MONTH (return month of date) - MONTH(date) - The MONTH function returns the month of the year (1 to 12) for a given standard date.

YEAR (return year of date) - -YEAR (date) - Returns the 4-digit year for a given standard date.

AGE (return age from base date) - AGE(birthdate [,base date]).

birthdate A numeric expression for a standard date.

base date A numeric expression for a standard date. If this parameter is omitted, the operating system date is used for the computation.

The AGE function returns a string containing the time elapsed between two dates. The age return string is in the following format:

1 to 60 days - 'nn DAYS'

61 days to 24 months - 'nn MOS'

2 years to 999 years - 'nnn YRS'

Operating System Functions

COMMAND (return command line) - COMMAND( [ flag ] ) –

flag A string constant or variable containing the parameter for which to search, or the number of the command line parameter to return. If omitted or an empty string (''), all command parameters are returned as entered on the command line, appended to a leading space.

The COMMAND function returns the value of the flag parameter from the command line. If the flag is not found, COMMAND returns an empty string. If the flag is multiply defined, only the first occurrence encountered is returned.

COMMAND searches the command line for flag=value and returns value. There must be no blanks between flag, the equal sign, and value. The returned value terminates at the first comma or blank space. If a blank or comma is desired in a command line parameter, everything to the right of the equal sign must be enclosed in double quotes (flag="value").

COMMAND will also search the command line for a flag containing a leading slash (/). If found, COMMAND returns the value of flag without the slash. If the flag only contains a number, COMMAND returns the parameter at that numbered position on the command line. A flag of '0' returns the minimum path the operating system used to find the command. This minimum path always includes the command (without command line parameters) but may not include the path (if the operating system found it in the current directory). A flag containing '1' returns the first command line parameter.

LONGPATH (return long filename) - LONGPATH( [ shortfilename ] ) - The LONGPATH function returns the long filename for a given short filename. The file named in the shortfilename parameter must already exist on disk.

PATH (return current directory) - PATH( ) - Returns a string containing the current drive and directory.

SHORTPATH (return short filename) - SHORTPATH( [ longfilename ] ) - The SHORTPATH function returns the DOS standard short filename for a given longfilename. The file named as the parameter must exist on disk.

EXISTS (return file existence) - EXISTS( file ) - The EXISTS function returns true (1) if the file is available on disk. If the file is not available, EXISTS returns false (0).

Miscellaneous

CHOOSE (return chosen value) - CHOOSE(expression ,value, value [,value...]) OR CHOOSE(condition [,value, value ] ) - Returns the chosen value from a list of possible values.

expression An arithmetic expression which determines which value parameter to return. This expression must resolve to a positive integer.

value A variable, constant, or expression for the function to return.

condition A logical expression which determines which of the two value parameters to return.If no value parameters are present, one (1) is returned when the expression is true, and zero (0) is returned when the expression is false.

The CHOOSE function evaluates the expression or condition and returns the appropriate value parameter. If the expression resolves to a positive integer, that integer selects the corresponding value parameter for the CHOOSE function to return. If the expression evaluates to an out-of-range integer, then CHOOSE returns the last value parameter.

When the condition evaluates as true, then CHOOSE returns the first value parameter. When the condition evaluates to false, then CHOOSE returns the second value parameter. If no value parameters are present, CHOOSE returns one (1) for true, and zero (0) for false.

CLIPBOARD (return windows clipboard contents) - CLIPBOARD( [format] ) –

format An integer constant or variable that defines the format of the clipboard's contents. If omitted, the default is CF_TEXT .

The CLIPBOARD function returns the current contents of the windows clipboard. The format parameter defaults to CF_TEXT (as defined in the Windows API) but any of the other CF_ values can be specified (see a Windows API reference book for details). If the data in the clipboard is not in the specified format, CLIPBOARD returns a null string (''). The following clipboard formats are predefined in the Windows API:

CF_TEXT 1
CF_BITMAP 2
CF_METAFILEPICT 3
CF_SYLK 4
CF_DIF 5
CF_TIFF 6
CF_OEMTEXT 7
CF_DIB 8
CF_PALETTE 9
CF_PENDATA 10
CF_RIFF 11
CF_WAVE 12

Picture Tokens

Picture tokens provide a masking format for displaying and editing variables. There are seven types of picture tokens: numeric and currency, scientific notation, string, date, time, pattern, and key-in template.

Numeric and Currency Pictures

Scientific Notation Pictures

String Pictures

Date Pictures

Time Pictures

Pattern Pictures

Key-in Template Pictures

Numeric and Currency Pictures

@N [currency] [sign] [fill] size [grouping] [places] [sign] [currency] [B]

@N All numeric and currency pictures begin with @N.

currency Either a dollar sign ($) or any string constant enclosed in tildes (~). When it precedes the sign indicator and there is no fill indicator, the currency symbol "floats" to the left of the high order digit. If there is a fill indicator, the currency symbol remains fixed in the left-most position. If the currency indicator follows the size and grouping, it appears at the end of the number displayed.

sign Specifies the display format for negative numbers. If a hyphen precedes the fill and size indicators, negative numbers will display with a leading minus sign. If a hyphen follows the size, grouping, places, and currency indicators, negative numbers will display with a trailing minus sign. If parentheses are placed in both positions, negative numbers will be displayed enclosed in parentheses. To prevent ambiguity, a trailing minus sign should always have grouping specified.

fill Specifies leading zeros, spaces, or asterisks (*) in any leading zero positions, and suppressesdefault grouping. If the fill is omitted, leading zeros are suppressed.

0 (zero) Produces leading zeroes
_ (underscore) Produces leading spaces
* (asterisk) Produces leading asterisks

size The size is required to specify the total number of significant digits to display, including the number of digits in the places indicator and any formatting characters.

grouping A grouping symbol, other than a comma (the default), can appear right of the size indicator to specify a three digit group separator. To prevent ambiguity, a hyphen grouping indicator should also specify the sign.

. (period) Produces periods
- (hyphen) Produces hyphens
_ (underscore) Produces spaces

places Specifies the decimal separator symbol and the number of decimal digits. The number of decimal digits must be less than the size. The decimal separator may be a period (.), grave accent (' ) (produces periods grouping unless overridden), or the letter "v" (used only for STRING field storage declarations--not for display).

. (period) Produces a period
' (grave accent) Produces a comma
v Produces no decimal separator

B Specifies blank display whenever its value is zero.

The numeric and currency pictures format numeric values for screen display or in reports. If the value is greater than the maximum value the picture can display, a string of pound signs (#) is displayed.

Example:

Numeric Result Format

@N9 4,550,000 Nine digits, group with commas (default)

@N_9B 4550000 Nine digits, no grouping, leading blanks if zero

@N09 004550000 Nine digits, leading zero

@N*9 ***45,000 Nine digits, asterisk fill, group with commas

@N9_ 4 550 000 Nine digits, group with spaces

@N9. 4.550.000 Nine digits, group with periods

Decimal Result Format

@N9.2 4,550.75 Two decimal places, period decimal separator

@N_9.2B 4550.75 Two decimal places, period decimal separator, no
grouping, blank if zero

@N_9'2 4550,75 Two decimal places, comma decimal separator

@N9.'2 4.550,75 Comma decimal separator, group with periods

@N9_'2 4 550,75 Comma decimal separator, group with spaces,

Signed Result Format

@N-9.2B -2,347.25 Leading minus sign, blank if zero

@N9.2- 2,347.25- Trailing minus sign

@N(10.2) (2,347.25) Enclosed in parens when negative

Dollar Currency Result Format

@N$9.2B $2,347.25 Leading dollar sign, blank if zero

@N$10.2- $2,347.25- Leading dollar sign, trailing minus when negative

@N$(11.2) $(2,347.25) Leading dollar sign, in parens when negative

Int'l Currency Result Format

@N12_'2~ F~ 1 5430,50 F France

@N~L. ~12' L. 1.430.050 Italy

@N~£~12.2 £1,240.50 United Kingdom

@N~kr~12'2 kr1.430,50 Norway

@N~DM~12'2 DM1.430,50 Germany

@N12_'2~ mk~ 1 430,50 mk Finland

@N12'2~ kr~ 1.430,50 kr Sweden

Storage-Only Pictures:

Variable1 STRING(@N_6v2) !Declare as 6 bytes stored without decimal

CODE

Variable1 = 1234.56 !Assign value, stores '123456' in file

MESSAGE(FORMAT(Variable1,@N_7.2)) !Display with decimal point: '1234.56'

Scientific Notation Pictures

@Emsn[B]

@E All scientific notation pictures begin with @E.

m Determines the total number of characters in the format provided by the picture.

s Specifies the decimal separation character, and the grouping character when the n value is greater than 3.

. (period) period and comma
.. (period period) period and period
' (grave accent) comma and period
_.(underscore period) period and space

n Indicates the number of digits that appear to the left of the decimal point.

B Specifies that the format displays as blank when the value is zero.

The scientific notation picture formats very large or very small numbers. The format is a decimal number raised by a power of ten.

Example:

Picture Value Result

@E9.0 1,967,865 .20e+007

@E12.1 1,967,865 1.9679e+006

@E12.1B 0

@E12.1 -1,967,865 -1.9679e+006

@E12.1 .000000032 3.2000e-008

@E12_.4 1,967,865 1 967.865e+003

String Pictures

@Slength

@S All string pictures begin with @S.

length Determines the number of characters in the picture format.

A string picture describes an unformatted string of a specific length.

Example:

Name STRING(@S20) !A 20 character string field

Date Pictures

@Dn [s] [direction [range] ] [B]

@D All date pictures begin with @D.

n Determines the date picture format. Date picture formats range from 1 through 18. A leading zero (0) indicates a zero-filled day or month.

s A separation character between the month, day, and year components. If omitted, the slash ( / ) appears.

. (period) Produces periods
' (grave accent) Produces commas
- (hyphen) Produces hyphens
_ (underscore) Produces spaces

direction A right or left angle bracket (> or <) that specifies the "Intellidate" direction (> indicates future, < indicates past) for the range parameter. Valid only on date pictures with two-digit years.

range An integer constant in the range of zero (0) to ninety-nine (99) that specifies the "Intellidate" century for the direction parameter. Valid only on date pictures with two-digit years. If omitted, the default value is 80.

B Specifies that the format displays as blank when the value is zero.

Dates may be stored in numeric variables (usually LONG), a DATE field (for Btrieve compatibility), or in a STRING declared with a date picture. A date stored in a numeric variable is called a "Clarion Standard Date." The stored value is the number of days since December 28, 1800. The date picture token converts the value into one of the date formats.

The century for dates in any picture with a two-digit year is resolved using "Intellidate" logic. Date pictures that do not specify direction and range parameters assume the date falls in the range of the next 20 or previous 80 years. The direction and range parameters allow you to change this default. The direction parameter specifies whether the range specifies the future or past value. The opposite direction then receives the opposite value (100-range) so that any two-digit year results in the correct century.

For example, the picture @D1>60 specifies using the appropriate century for each year 60 years in the future and 40 years in the past. If the current year is 1996, when the user enters "5/01/40," the date is in the year 2040, and when the user enters "5/01/60," the date is in the year 1960.

For those date pictures which contain month names, the actual names are customizable in an Environment file (.ENV). See the Internationalization section for more information.

Example:

Picture Format Result

@D1 mm/dd/yy 10/31/59

@D1>40 mm/dd/yy 10/31/59

@D01 mm/dd/yy 01/01/95

@D2 mm/dd/yyyy 10/31/1959

@D3 mmm dd, yyyy OCT 31,1959

@D4 mmmmmmmmm dd, yyyy October 31, 1959

@D5 dd/mm/yy 31/10/59

@D6 dd/mm/yyyy 31/10/1959

@D7 dd mmm yy 31 OCT 59

@D8 dd mmm yyyy 31 OCT 1959

@D9 yy/mm/dd 59/10/31

@D10 yyyy/mm/dd 1959/10/31

@D11 yymmdd 591031

@D12 yyyymmdd 19591031

@D13 mm/yy 10/59

@D14 mm/yyyy 10/1959

@D15 yy/mm 59/10

@D16 yyyy/mm 1959/10

@D17 Windows Control Panel setting for Short Date

@D18 Windows Control Panel setting for Long Date

Alternate separators

@D1. mm.dd.yy Period separator

@D2- mm-dd-yyyy Dash separator

@D5_ dd mm yy Underscore produces space separator

@D6' dd,mm,yyyy Grave accent produces comma separator