Dictionary - Standard Functions

QMSCAPA Report Builder Features

The language used to create this Report Builder has some extremely useful "functions" that programmers use to generate applications. Some of these functions are made available to you to use with your reports.

A function could be described as a means of converting something to something else. Data is manipulated using a function to generate a desired result. Kind of like a car going through a carwash - it looks one way going in, and another way coming out.

Functions are broken into four "groups":

Here is a more detailed breakdown of the categorized function types:

String Functions

•ALL() - Returns a text string that replicates itself a defined number of times. For example, ALL('A',5) would return the text string 'AAAAA'.

•CAPITALIZE() - Returns a text string with the first letter in uppercase and the remainder in lowercase.

•CHR() - Returns the ANSI character from an ASCII code. For example, CHR(122) would return 'z'

•CLIP() - Returns a string with the trailing spaces removed. Commonly used to properly format names and addresses into a more aesthetic appearing text string; multiple database fields are combined together using the concatenation symbol (&) to form a formatted string. For example, the database field ADR:City that contains the city name 'Tucson' could be combined with the ADR:State field that contains 'Az'. Because the ADR:City database field has 30 spaces allocated to it, and only 6 positions were used for 'Tucson', you would normally have 24 blank spaces:

                                        Tucson                          Az

    not exactly appealing! Using the CLIP() function and the concatenation symbol you

    can convert this to:  Tucson, Az

         EX: CLIP( ADR:City ) & ', ' & CLIP( ADR:State )

    Note that there is a blank space after the comma to "push" the state over one

    position to avoid crowding the comma character.

•DEFORMAT() - Returns a string that contains the unformatted characters of a numeric string. For example DEFORMAT('999-53-9954') returns 999539954

•FORMAT() - Returns a formatted string; knowledge of the PICTURE command is mandatory to make best use of this function. For example SSN = FORMAT( Emp:SSN,@P###-##-####P), which would properly format the database field EMP:SSN to a standard social security number.

•INLIST() - Returns the position of an item in a comma delimited list. For example, INLIST('D','A','B','C','D','E') returns a value of 4

•LEN() - Returns the length of a string. For example, LEN('Scott Daughtry') would return a value of 14.

•LOWER() - Returns the lowercase version of a string. For example LOWER('SCOTT Daughtry') would be returned as 'scott daughtry'.

•NUMERIC() - Returns a true or false. If a string contains only numbers a true is returned. For example NUMERIC('123') would be true, and NUMERIC('1,23') would be false.

SUB() - Returns a portion of a string. You define where the starting and stopping positions are in the string. For example, SUB( 'Scott Daughtry', 1, 5 ) would extract positions 1-5 of the string, returning 'Scott'. SUB( 'Scott Daughtry', 7, 3 ) extracts 'Dau' - the starting position is 7, and the next 3 positions are extracted from that starting point. You can also work backwards on a string by assigning a negative starting position. For example, SUB( 'Scott Daughtry', -1, 3 ) would return 'try'.

•UPPER() - Returns an uppercase version of a text string. For example, UPPER( 'scoTT') is returned as 'SCOTT'.

•VAL() -  Returns the ASCII character numeric equivalent for an ANSI entry. For example, VAL('A') would return 65.

Mathematical Functions

•ABS() - Returns an absolute value from an expression. The absolute value of a number is always a zero. For example, C = ABS( A-B ). C would contain the absolute value of the difference of B subtracted from A

•INRANGE() - Compares a numeric expression for an inclusive range of numbers. Returns a value of true if the scope is met, and false otherwise. For example, INRANGE( variablename, 1, 5 ) would check if the variablename is with the numbers 1-5, and return true if so. The starting number is 1, and ending number is 5.

•INT() - Returns an integer value from a numeric expression. For example, INT( 8.5 ) would return a value of 8.

•LOGE() - Returns the natural algorithym. The natural logarithm of a value is the power to which e must be raised to equal that value. The value of e used internally for these calculations is 2.71828182846. For example LOGE(2.71828182846) returns 1; LOGE(1) returns 0

•LOG10() -Returns base 10 logarithm. If the value of the expression is zero or less, the return value will be zero. The base 10 logarithm is undefined for values less than or equal to zero. The LOG10 procedure returns the base 10 logarithm of a numeric expression. The base 10 logarithm of a value is the power to which 10 must be raised to equal that value. For example, LOG10(10) returns 1; LOG10(1) returns 0

•RANDOM() - Returns random integer.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. For example, RANDOM( 22, 80 ) would return a random number between the numbers 22 and 80.

•ROUND() - Returns a rounded number. For example, ROUND( 5163, 100 ) would return the rounded number of 5163+100 (which is 5200); ROUND(657.50,1) returns 658; ROUND(51.63594,.01) returns 51.64

•SQRT() - Returns the square root of a number.

Trigonometric Functions

•SIN() - Returns the trigonometric sine of an angle measured in radians. The sine is the ratio of the length of the angle's opposite side divided by the length of the hypotenuse. For example, eturns 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. For example, sineAngle = SIN( 45 * 3.142498712

•COS() - 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. For example, cosineAngle = COS( 45 * 3.142498712)

•TAN() - Returns the trigonometric tangent of an angle measured in radians. The tangent is the ratio of the angle's opposite side divided by its adjacent side. For example, TanAngle = TAN( 45 * 3.142498712)

ACOS() - Returns the inverse cosine. The inverse of a cosine is the angle that produces the cosine. The return value is 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. For example, acos = ACOS( 45 * 3.142498712)

ASIN() - Returns the inverse sine. The inverse of a sine is the angle that produces the sine. The return value is 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. For example, asine = ASIN( 45 * 3.142498712)

ATAN() - Returns the inverse tangent. The inverse of a tangent is the angle that produces the tangent. The return value is 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. For example, TanAngle = ATAN( 45 * 3.142498712)

Date/Time Functions

•TODAY() - 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). You can use a picture command to change the appearance of the return value. For example, cToday = format( TODAY(), @d01B )

•CLOCK() - Returns the current time based upon your pc bios time.

•DATE() - 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. For example, HireDate = DATE(Hir:Month,Hir:Day,Hir:Year); FirstOfMonth = DATE(MONTH(TODAY()),1,YEAR(TODAY())) would compute the first  day of month

•DAY() - Returns day of month. Computes the day of the month (1 to 31) for a given standard date. For example, OutDay = DAY(TODAY()) to get the day from today's date; DueDay = DAY(TODAY()+2)  to calculate the return day

•MONTH() - Returns the month of the year (1 to 12) for a given standard date. For example, PayMonth = MONTH(DueDate) to get the month from the date

•YEAR() - Returns a four digit number for the year of a standard date (1801 to 9999). For example, YEAR( today() )

•AGE() - 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'. For example, nAge = AGE( EMP:Birthday, TODAY()) would compare the database field EMP:Birthday against the computer date to return the persons age.

+1 800 644 2056

abci-consultants.com