Apollo VCL Components > xBase Expressions/Functions

The following xBase functions are supported in index and query expressions passed to TApolloTable methods:

ALIAS()

Returns the Alias name of the current workarea as a string.

ALLTRIM( String )

Trims both leading and trailing spaces from a string. The string may be derived from any valid xBase expression.

ALLTRIM(" Vista Software ") returns 'Vista Software'.

AT( SearchString, TargetString )

Determine whether a search string is contained within a target. If found, the function returns the position of the search string within the target string (relative to 1). If not found, the function returns 0 (zero).

AT("gh", "defghij") returns 4.

CHR( Val )

Converts a decimal value to its ASCII equivalent.

CHR(83) returns 'S'

CTOD( String )

Converts a character string into an xBase date. The string must be formatted according to the settings of SetDateFormat and SetCentury.

CTOD("12/31/94")

DATE()

Returns the system date (today). Use DTOC(DATE()) to retrieve today's date formatted according to the settings of SetCentury and SetDateFormat . If the date format is American, DTOC(DATE()) returns '03/21/95' if today is March 21, 1995.

DAY( DateField )

Returns the day portion of an xBase date as an integer.

DELETED()

Returns True if the record is deleted and False if not deleted.

DESCEND( String )

An xBase function that inverts a key value using 2's complement arithmetic. The result of the operation is the arith metic inverse of the key value. When inverted keys are sorted in ascending sequence, the result is in descending order. To create a search key for an index built with this function, you must use the Descend method. An index expression could be

'DESCEND(DTOS(billdate)) + CUSTNO'

DTOC( DateField [, ReturnType] )

Converts an xBase date into a character string formatted according to the settings of TApolloTable's SetDateFormat and SetCentury. For example, if the date format was American and SetCentury was True, and the date field contained March 21, 1995, DTOC(datefield) would return '03/21/1995'.

The optional ReturnType parameter has been added in Apollo for FoxPro compatibility purposes. If not passed, a default ReturnType of 0 is used and returns an xBase date formatted according to the settings of TApolloTable's SetDateFormat and SetCentury. A ReturnType of 1 returns the date as a Clipper style DTOS() date ("CCYYMMDD", i.e., November 8th, 1996 would be "19961108").

DTOS( DateField )

Converts an xBase date into a string formatted according to standard xBase storage conventions (CCYYMMDD). For example, December 21, 1993 would be returned as '19931221'. Indexes that contain date elements should use the DTOS() function, which naturally collates into oldest date first.

EMPTY( Field )

Reports the empty status of any xBase field. Character and date fields are empty if they consist entirely of spaces. Numeric fields are empty if they evaluate to zero. Logical fields are empty if they evaluate to False.

Memo fields that contain no reference to a memo block in the associated memo file are empty.

IF( Logical, True Result, False Result )

This is the immediate if function. If the Logical expression is true, return the True result, otherwise return the False result. The types of the True Result and the False Result must be the same (i.e., both numeric, or both strings, etc.) The logical expression must of course evaluate as True or False.

IF(DATE() - CTOD("12/31/99") > 0, "This Year", "Last Year")

IIF( Logical, True Result, False Result )

Supported exactly like IF() as noted above.

INDEXKEY()

Returns the current index key as a string. (Same as ORDKEY()).

LEFT( String, Length )

Returns the leftmost characters of the expression for the defined length.

LEFT("xyzabc", 3) returns 'xyz'.

LEN( Expression )

Returns the length of the expression result as an integer.

LOWER( String )

Converts the string expression into lower case.

Both dBase and Clipper UPPER() and LOWER() case conversion functions limit the characters eligible for case conversion.

With UPPER(), only characters a-z are converted to upper case. With LOWER(), only characters A-Z are converted.

The SysProp SDESP_SETLIMITCASECONV option can affect this default behavior. Characters with diacritical marks are not converted when this switch is True if OEMTranslate is also set to True. To limit case conversion using this switch, set OEMTranslate to True and set the SysProp value on as well.

For example:

ApTbl.OEMTranslate := True;

ApTbl.SysProp( SDE_SP_SETLIMITCASECONV, Pointer(1));

MONTH( DateField )

Returns the month portion of an xBase date as an integer.

ORDER()

Returns the current index order as an integer.

ORDKEY()

Returns the current index key as a string. (Same as INDEXKEY())

PADC( String, Length, Character )

Centers the passed string between a number of the passed character to make the string the specified length.

'[' + PADC("Scott", 9 ,"-") + ']' returns '[--Scott--]'.

PADL( String, Length, Character )

Pads the passed string to the specified length with the specified characters. If the string is longer than the value specified by Length, the string is truncated to this length.

'[' + PADL("Scott", 8, "*" ) + ']' returns '[***Scott]'.

'[' + PADL("Loren Scott", 8, " " ) + ']' returns '[Loren Sc]'.

PADR( String, Length, Character )

Pads the passed string to the specified length using the specified character. If the string is longer than the value specified by Length, the string is truncated to this length.

'[' + PADR("Scott", 8, " " ) + ']' returns '[Scott ]'.

'[' + PADR("Loren Scott", 8, " " ) + ']' returns '[Loren Sc]'.

QKEYVAL( IndexTagName )

Returns the current key value for the passed IndexTagName as a string. See TApolloTable.Query for more information.

RAT( SearchString, TargetString )

Determine whether a search string is contained within a target, starting from the right side of the target string. If found, the function returns the position of the search string within the target string (relative to 1). If not found, the function returns 0 (zero).

RAT( "ab", "abzaba" ) returns 4.

RECCOUNT()

Returns the number of records in the table as a long integer.

RECNO()

Returns the current physical record number as a long integer.

RIGHT( String, Length )

Returns the rightmost characters of the expression for the defined length.

RIGHT("xyzabc", 3) returns 'abc'.

SELECT()

Returns the workarea number for the current workarea as a long integer.

SOUNDEX( String )

Returns a 4-byte string consisting of the first letter of the passed string and three numbers representing a 'sound' value for the specified string.

SPACE( Length )

Returns a string consisting entirely of spaces for the defined length.

STOD( String )

The inverse of DTOS(). STOD() converts a string formatted according to standard xBase storage conventions (CCYYMMDD) to an xBase Date formatted according to the settings of SetDateFormat and SetCentury.

STR( Number, Length, Decimals )

Converts a number into a right justified string with decimals digits follow ing the decimal point. The total length of the string is defined by the length parameter. STR(RECNO(), 5, 0) is a common indexing element that ensures creation of unique keys if appended to another field element.

An index key using this expression could be built with:

NAME + STR(RECNO(),5,0)

If the decimals parameter is omitted, the function defaults to zero decimals. If the length parameter is omitted as well, the length of the result is 10 characters under SDEFOX. Under SDENSX and SDENTX the default is the length of the field.

STRZERO( Number, Length, Decimals )

Converts a number into a, zero-padded right justified string with decimals digits follow ing the decimal point. The total length of the string is defined by the length parameter.

STRZERO( 1234, 10, 2 ) returns '0001234.00'

SUBSTR( String, Start, Length )

Returns a portion of the string expression starting at the defined start location for the defined length..

SUBSTR('xyzabcd', 3, 4) returns 'zabc'.

sxChar( Length )

sxChar( Length ) is a special indexing function that allows the creation of Roll-Your-Own (RYO) indexes with each key being a character string of the defined sxChar length. An sxChar index does not rely on any given field value for a key. The maintenance of the index and the contents of each key is entirely in the hands of the applications programmer. See RYOKeyAdd and RYOKeyDrop .

When an sxChar index is created it is initially empty. Reindexing an sxChar index will also leave it in an empty state.

sxChar may only be used as an index expression in Index or IndexTag and it may only be used with IDX/CDX or NSX index types. It is recommended that single tag indexes be used for sxChar RYO indexes. To create an sxChar index, pass the option parameter in Index or IndexTag as IDX_EMPTY.

Reindexing an sxChar RYO index empties the index.

ApTbl.IndexTag( '', 'CODE', 'sxChar( 8 )', IDX_EMPTY, False, '' );

SX_VFGET( VFieldName, Length )

This function is only used for creating an index on a weakly-typed 'V' field, and returns a fixed length string value. When processing each record for index creation, if the current value of the specified 'V' field is shorter than Length, it is padded with spaces to the right. If the value in VFieldName is longer than Length, it is truncated.

ApTbl.IndexTag( '', 'CITY', 'SX_VFGET( "CITY", 30 )', IDX_NONE, False, '' );

TIME()

Returns the system time as a string in the form HH:MM:SS.

TRANSFORM( Expression, Picture )

Transform converts strings and numeric values into formatted character strings. The function transforms the result of the first expression in accordance with the second picture string.

The picture string is made up of two parts. The first part is the Function string and it is optional for both strings and numeric values (as long as the second Template string is present).

A character string transformation picture may consist of only a Function string or only a Template or both.

A numeric picture must contain a Template string; the Function string is optional.

A logical value must contain only a Template string with Template characters L or Y.

The Function string consists of a leading @ character followed by one or more formatting characters. If the Function string is present, the @ character must be the first character in the picture string with its formatting characters immediately following and it may not contain spaces.

If a Template string exists as well, it follows the Function string. A single space separates the Function string and the Template string.

Function string characters allowed for numeric values are:

B left justify;

C display CR after positive numbers;

X display DR after negative numbers;

Z blank a zero value;

( encloses negative numbers in parentheses.

Function string characters allowed for strings are:

R inserts unassigned template characters;

! converts all alpha characters to upper case.

The @R Function requires a Template; the ! Function does not.

The Template string describes the format on a character by character basis. The Template string is made up of special characters which have specific results and optional unassigned characters which either replace characters or are inserted in the formatted string depending upon the absence or presence of the @R Function string.

Template assigned characters are as follows:

A,N,X,(,# are place holders and are interchangeable;

L displays logical values as T or F;

Y displays logical values as Y or N;

! converts the corresponding character to upper case;

, (comma) or a space (in Europe) in a numeric template separate

the elements of a number;

. (period) or , (comma - in Europe) in a numeric template specify

the decimal position;

* fills leading spaces with asterisks in a numeric template;

$ as the leading character in a numeric template results in a

floating dollar sign being placed in front of the

formatted number.

Delphi Example: Where "phone" is a character field holding a phone number with no formatting characters.

ApTbl.EvalString( 'transform(phone, "@R (###) ###-####")');

returns '(909) 699-6776'. If the formatting characters were actually present in the field, the "@R" function would be omitted.

For numeric fields, EvalString('transform(123456.78, "$9,999,999.99")') returns ' $123,456.78'.

TRIM( String )

Removes trailing spaces from the string expression.

UPPER( String )

Converts the string expression into upper case. Character fields used in index expressions should always be converted to upper case to insure correct collating sequence.

Both dBase and Clipper UPPER() and LOWER() case conversion functions limit the characters eligible for case conversion.

With UPPER(), only characters a-z are converted to upper case. With LOWER(), only characters A-Z are converted.

The SysProp SDESP_SETLIMITCASECONV option can affect this default behavior. Characters with diacritical marks ARE NOT CONVERTED when this switch is True if OEMTranslate is also set to True. To limit case conversion using this switch, set OEMTranslate to True and set the SysProp value on as well.

For example:

ApTbl.OEMTranslate := True;

ApTbl.SysProp( SDE_SP_SETLIMITCASECONV, Pointer(1));

VAL( String )

Converts a string of numeric characters into its equivalent numeric value. The conversion stops at the first non-numeric character encountered (or the end of the string).

VAL("123ABC") returns a value of 123.

YEAR( DateField )

Returns the year portion of an xBase date as an integer.

xBase Functions Supported

xBase Functions Supported