APPENDIX B: FUNCTIONS & FORMULAS Expressions, or mathematical formulas, are one of the more complex and technical tools provided by Pirouette. Unfortunately, there are many situations where there is just no other way to do the job. Expressions come in four data types, just like fields. The best way of illustrating this is by example: "George" is a Character expression 4 + 9 is a Numeric expression 7/20/69 is a Date expression PAYEE_NAME = "DeMars & Tilley" is a Logical expression The first three are pretty obvious, but the last one may be a bit difficult to grasp. A Logical expression is one which always has the value of true or false. The specific equation shown above may not be true for every record in your database, but it will be either true OR false; hence, it is a Logical expression. Expressions actually consist of four things: constants, fields, operators, and functions. Constants are illustrated by two of the examples above. "George" will always be "George" and 7/20/69 will always be July 20, 1969. Similary, 4 and 9 are numeric constants which have been strung together into a more complex numeric expression. There are also logical constants, for when the situation requires a value "always true" or "always false". These are represented as .T. or .Y. for true, and .F. or .N. for false. Fields are, of course, fields from a database, whose value will be different depending upon which record you're looking at. Operators are symbols which are used to combine simple expressions into more complex expressions. The + sign in 4+9 is a numeric operator. The = in PAYEE_NAME = "DeMars & Tilley" is a logical operator, which has been used to combine two character expressions to form a logical expression. A complete summary of all the operators for each data type is shown below: Character Operators ------------------- + concatenate (combine two strings) The expression "A" + "B", for example, is equivalent to the value "AB". -B1- Numeric Operators ----------------- + plus - minus * times / divided by % modulus (returns the remainder of a division) ^ or ** to the power of An example of the latter, 4^2 would be equal to four squared, or 16. Date Operators -------------- + plus (a certain number of days) - minus (a certain number of days) The expression DATE_PAID + 7 would be equal to 12/25/89 if DATE_PAID were equal to 12/18/89. Logical Operators ----------------- .AND. and the following is also true .OR. or the following is true .NOT. if the following is not true < is less than > is greater than = is equal to <> or # or != is not equal to <= is less than or equal to >= is greater than or equal to $ is contained in == is exactly equal to These last two pertain to strings and deserve further explanation. The $ operator is very useful for filtering, because it "forgives" differences in data input. For example, the expression "Jones" $ "Frank P. Jones" would evaluate as true, because the first string is contained within the second. The difference between the = operator and the == operator is that the first one compares the strings only up to the length of the second string. Thus, "Alvin" = "A" will be true. The == operator, on the other hand, compares the length of the strings as well, so "Alvin" == "A" would not be true. -B2- Functions work something like arithmetic operations, in that you provide certain parameters, and the function returns a certain value. Several functions have been introduced by example throughout the manual, so you probably have a general idea of how they work. UPPER("George") is equal to "GEORGE", SQRT(4) -- the square root function -- is equal to 2. The following is a list of all the functions available within Pirouette: ABS Syntax: ABS(n) Returns the absolute value of n. Example: ABS(-4) = 4 ASC Syntax: ASC(string) Returns the ASCII code value for the first character in string. (This function is the inverse of the CHR() function.) Example: ASC("T") = 84 AT Syntax: AT(string1,string2) Given two character strings, returns a number corresponding to the starting position of string1 within string2. If string2 does not contain string1, the function returns the value 0. Example: AT("plow","snowplow") = 5 CDOW Syntax: CDOW(date) Returns the name of the day of the week corresponding to the specified date expression. Example: CDOW(CTOD("1/31/89")) = "Tuesday" CHR Syntax: CHR(n) Returns ASCII character n. (This function is the inverse of the ASC() function). Example: CHR(84) = "T" -B3- CMONTH Syntax: CMONTH(date) Returns the name of the month corresponding to the specified date expression. Example: CMONTH(CTOD("1/31/89")) = "January" CTOD Syntax: CTOD(string) Returns a date value from a properly formatted string. This function is used frequently, since dates cannot be directly shown in filter conditions, etc. Pirouette (like dBASE III+) will not accept an expression like CMONTH("2/14/89"). The date string must first be converted to a string, using the CTOD() function. Then, as an actual date value, subtraction (to determine the days elasped between two dates) and other date-oriented operations can be performed. (This function is also the inverse of the DTOC() function.) Example: CTOD("1/31/89") = 01/31/89 Special note on CTOD(): If CTOD() is given a non- existent date, you may get some bizarre results. The expression CHECK_DATE > CTOD("6/31/89") will be accepted as a valid logical expression, but is NOT equivalent to CHECK_DATE >= CTOD("7/1/89"). DATE Syntax: DATE() Returns the date on your system clock (expressed as a date, not a string). Example: DATE() = 01/31/89 DAY Syntax: DAY(date) Returns the day of the month expressed as a number from 1 through 31. Example: DAY(CTOD("1/31/89")) = 31 DOW Syntax: DOW(date) Returns the day of the week expressed as a number from 1 through 7. Sunday has the value 1, etc. Example: DOW(CTOD("1/31/89")) = 3 -B4- DTOC Syntax: DTOC(date) Returns a date expressed as a character string. (This function is the inverse of the CTOD() function.) Example: DTOC(DATE()) = "01/31/89" DTOS Syntax: DTOS(date) Returns a date expressed as a character string in a modified format; one which is sometimes more suitable for sorting, since the year is listed first. Example: DTOS(DATE()) = "19890131" EMPTY Syntax: EMPTY(field) Returns .T. if a field is empty for a given record. "Empty", in this case" means that a character field consists only of spaces, a numeric field contains the value 0, a date field contains the value " / / ", and a logical field contains the value .F. Example: EMPTY(CHECK_NO) = .T. EXP Syntax: EXP(n) Returns the value of e (2.71828 approximately) raised to the power of n. (This function is the inverse of the LOG() functions, which computes the natural logarithm.) Example: EXP(2) = 7.39 IIF Syntax: IIF(condition,expression1,expression2) Returns expression1 if the condition evaluates as true; returns expression2 if the condition evaluates as false. Example: IIF(MONTH(DATE())=12, "Merry Christmas", "Bah, Humbug") = "Bah, Humbug" -B5- INT Syntax: INT(n) Returns the integer portion of a number. INT() does not round; where rounding is required the ROUND() function should be used. Example: INT(3.95) = 3 LEN Syntax: LEN(string) Returns the length of a character string. Example: LEN("turkey") = 6 LOG Syntax: LOG(n) Returns the natural logarithm of a number. To obtain the common logarithm of n, divide LOG(n) by 2.3. Example: LOG(7) = 1.95 LOWER Syntax: LOWER(string) Returns a string of all lower case letters. (This function is the converse of the UPPER() function.) Example: LOWER("Mayberry RFD") = "mayberry rfd" LTRIM Syntax: LTRIM(string) Returns a string stripped of all preceding blanks. This is particularly useful when converting numbers to strings using the STR() function. (This function is the converse of the RTRIM() function.) Example: LTRIM(" 33.95") = "33.95" MAX Syntax: MAX(n1,n2) Returns the greater of two numeric values. (This function is the converse of the MIN() function.) Example: MAX(77,391) = 391 -B6- MIN Syntax: MIN(n1,n2) Returns the lesser of two numeric values. (This function is the converse of the MAX() function.) Example: MIN(77,391) = 77 MONTH Syntax: MONTH(date) Returns the month as an integer from 1 to 12. Example: MONTH(CTOD("1/31/89")) = 1 REPLICATE Syntax: REPLICATE(string,n) Returns a character string consisting of the input string repeated n times. Example: REPLICATE("x",6) = "xxxxxx" ROUND Syntax: ROUND(n1,n2) Returns n1 rounded off to n2 decimal places. If n2 is 0, an integer is returned. If n2 is less than 0, the number is rounded off to the nearest ten, hundred, thousand, etc. Example: ROUND(4.9,0) = 5 Example: ROUND(763,-2) = 800 RTRIM Syntax: RTRIM(string) Returns a string minus any trailing blanks. (This function is the converse of the LTRIM() function.) Example: RTRIM("Berkeley ") = "Berkeley" SPACE Syntax: SPACE(n) Returns a string of n blank spaces. This is equivalent to REPLICATE(" ",n). Example: SPACE(6) = " " -B7- SQRT Syntax: SQRT(n) Returns the square root of n. Example: SQRT(25) = 5 STR Syntax: STR(n1,n2,n3) Returns a string representation of n1 which is n2 characters long, rounded off to n3 decimal places. (The STR() function is the inverse of the VAL() function.) Example: STR(10.333333,5,2) = "10.33" STUFF Syntax: STUFF(string1,n1,n2,string2) Returns a string which is created as follows: characters in string1 are replaced with n2 characters from string2, beginning at position n1. If n2 is 0, string2 is inserted without replacing any characters in string1. Example: STUFF("byte",2,1,"i") = "bite" Example: STUFF("IBM computer",5,0,"compatible ") = "IBM compatible computer" SUBSTR Syntax: SUBSTR(string,n1,n2) Returns n2 characters from the string, beginning at position n1. Example: SUBSTR("Jones, Bob",1,5) = "Jones" TIME Syntax: TIME() Returns the system time (the time on your computer's internal clock), expressed as a character string in the format hh:mm:ss. A 24-hour clock is used. Example: TIME() = "15:04:32" UPPER Syntax: UPPER(string) Returns a string in all upper case letters. (This function is the converse of the LOWER() function.) Example: UPPER("byte") = "BYTE" -B8- VAL Syntax: VAL(string) Returns a number from a string resembling a number; in effect, allowing conversion of strings to numbers. (This function is the inverse of the STR() function.) Example: VAL("44") = 44 YEAR Syntax: YEAR(date) Returns the year as a four-digit integer. Example: YEAR(DATE()) = 1989 -B9-