| Navigation: ISAM File Drivers > FoxPro / FoxBase Database Driver >====== FoxPro:Other ====== |    |
Boolean Evaluation
FoxPro and FoxBase allow a logical field to accept one of 11 possible values (0,1,y,Y,n,N,t,T,f,F or a space character). The space character is neither true nor false. When using a logical field from a preexisting database in a logical expression, account for all these possibilities. Remember that when a STRING field is used as an expression, it is true if it contains any data and false if it is equal to zero or blank. Therefore, to evaluate a Logical field's truth, the expression should be true if the field contains any of the “true” characters (1,T,t,Y, or y). For example, if a Logical field were used to specify a product as taxable or nontaxable, the expression to evaluate its truth would be:
(If Condition):
Taxable='1' OR Taxable='T' OR Taxable='t' OR Taxable='Y' OR Taxable='y'
Large MEMOs
Clarion supports MEMO fields up to a maximum of 64K. If you have an existing file which includes a memo greater than 64K, you can use the file but not modify the large MEMOs.
You can determine when your application encounters a large MEMO by detecting when the memo pointer variable is non-blank, but the memo appears to be blank. Error 47 (Bad Record Declaration) is posted, and any modification to the MEMO field is ignored.
Long Field Names
FoxPro and FoxBase support a maximum of 10 characters in a field name. If you require more, use an External Name with 10 characters or less.
Key Definition
FoxPro and FoxBase support the use of expressions to define keys. Within the Dictionary Editor, you can place the expression in the external name field in the Key Properties dialog. The general format of the external name is :
'FileName=T[Expression]'
Where FileName represents the name of the index file (which can contain a path and file extension), and T represents the type of the index. Valid types are: C = character, D = date, and N = numeric. If the type is D or N then Expression can name only one field.
Multiple-index (.CDX) files require the NAME() attribute on a KEY or INDEX to specify the storage type of the key and any expression used to generate the key values. The general format of the NAME() attribute on a KEY or INDEX is:
NAME('TagName|FileName=T[Expression],COMPRESSED')
The following are the parameters for the NAME() attribute:
| TagName | Names an index tag within a multiple index file. If the TagName is omitted the driver creates an .IDX file with the name specified in FileName. |
| FileName | Names the index file, and optionally contains a path and extension. |
| T | Specifies the type of the index; legal types are C = character, D = date, N = numeric. If the type is D or N then Expression may name only one field. |
| Expression | Specifies the expression used to generate the index. The expression may refer to multiple fields, and invoke multiple of xBase functions. The functions currently supported are listed below. Square brackets must enclose the expression. |
| COMPRESSED | When specified, the FoxPro Driver creates a FoxPro 2 compatible compressed .IDX file. |
Elements of the NAME() attribute may be omitted from the right. When specifying an Expression, the type and name must also be specified. If the Expression is omitted, the driver determines the Expression from the key fields when the file is created, or from the index file when opened.
If the type is omitted, the driver determines the index type from the first key component when the file is created, or from the index file when opened.
If the NAME() attribute is omitted altogether, the index file name is determined from the key label. The path defaults to the same location as the .DBF.
Tag names are limited to 10 characters in length; if the supplied name is too long it is automatically truncated.
All field names in the NAME() attribute must be specified without a prefix.
FoxPro additionally supports the use of the Xbase FOR statement in expressions to define keys. The expressions supported in the FOR condition must be a simple condition of the form:
expression comparison_op expression
comparison_op may be one of the following: <;, <;=, =<;, <;>, =, ⇒, >= or >.
The expression may refer to multiple fields in the record, and contain xBase functions. Square brackets must enclose the expression. The currently supported functions appear below. If the driver encounters an unsupported Xbase function in a preexisting file, it posts error 76 'Invalid Index String' when the file is opened for keys and static indexes.
String expressions may use the '+' operator to concatenate multiple string arguments. Numeric expressions use the '+' or '-' operators with their conventional meanings. The maximum length of a FoxPro or FoxBase expression is 250 characters.
Supported xBase Key Definition Functions
| ALLTRIM(string) | Removes leading and trailing spaces. |
| CTOD(string) | Converts a string key to a date. The string must be in the format mm/dd/yy; the result takes the form 'yyyymmdd'. The yyyy element of the date defaults to the twentieth century. An invalid date results in a key containing blanks. |
| DELETED() | Returns TRUE if the record is deleted. |
| DTOC(date) | Converts a date key to string format 'mm/dd/yy.' |
| DTOS(date) | Converts a date key to string format 'yyyymmdd.' |
| FIXED(float) | Converts a float key to a numeric. |
| FLOAT(numeric) | Converts a numeric key to a float. |
| IIF(bool,val1,val2) | Returns val1 if the first parameter is TRUE, otherwise returns val2. |
| LEFT(string, n) | Returns the leftmost n characters of the string key as a string of length n. |
| LOWER(string) | Converts a string key to lower case. |
| LTRIM(string) | Removes spaces from the left of a string. |
| RECNO() | Returns the current record number. |
| RIGHT(string, n) | Returns the rightmost n characters of the string key as a string of length n. |
| RTRIM(string) | Removes spaces from the right of a string. |
| STR(numeric[,length[,decimal places]]) | Converts a numeric to a string. The length of the string and the number of decimal places are optional. The default string length is 10, and the number of decimal places is 0. |
| SUBSTR(string,offset,n) | Returns a substring of the string key starting at offset and of n characters in length. |
| TRIM(string) | Removes spaces from the right of a string (identical to RTRIM). |
| UPPER(string) | Converts a string key to upper case. |
| VAL(string) | Converts a string key to a numeric. |