FileFlex uses logical expressions throughout many of its search functions. When you clearly understand how to construct expressions, you'll be a good part of the way towards using FileFlex search tools.
The FileFlex logical expression can consist of constants, field names, and functions, joined together by operators. In the expression "SALES > 50000", there is a field name ("SALES"), a constant (50000) and an operator (">", meaning greater than). The key to using searches successfully is learning to construct expressions.
Expression Constants
Constants in FileFlex expressions can be numeric, character strings, or logical values:
Notice that these constants are surrounded by periods; these are essential for FileFlex to understand your constant as the logical value, rather than a string.
If a field in your database file contains a logical value and you want to test for it, you must be sure to provide the constant correctly. For example, the field called IN_STOCK in a video database file could be designed to contain a TRUE or FALSE value. To locate only videos that are in stock, you could set up the logical expression this way:
"IN_STOCK = .T."
but if you did this instead:
"IN_STOCK = 'T'"
you would not find any records because FileFlex would look for the expression string 'T' in the field IN_STOCK rather than the logical value ".T." (meaning, of course, "TRUE").
Field Names
You must be sure that field names in your logical expressions match the database table's field names exactly. As always, case doesn't matter but punctuation does. Thus if the database field is called IN_STOCK and you attempt to define a value for a field called INSTOCK, you will produce an error. Be sure to remember that field names are NOT enclosed in quotes.
Intrinsic Functions and Operators
FileFlex allows you to use certain intrinsic functions (functions built into the expression analyzer) that can aid your search. One of the most useful is UPPER. UPPER converts the string being compared to upper case. By placing UPPER in your query expression, you can be sure that you'll find a string, regardless of the case stored in the database. For example:
"UPPER(FIRSTNAME) = 'DAVID'"
Since we don't know whether the field FIRSTNAME contains "David" or "david" or "DAVID", the UPPER function allows us to find without regard to case.
All of the intrinsic functions are described below.
Logical Operators
Within the expressions, you can join functions, constants, and field names with logical operators. There are two types of such operators: comparative and connective.
The comparative operators recognized by FileFlex are shown below. Except for the "$" operator, they are the "standard" comparative operators recognized by many major database systems and programming languages. The "$" operator tests for the presence in one string of another (e.g., "ABC" $ "ABCDEF" returns .TRUE.).
SYMBOL INTERPRETATION = equal to <> not equal to < less than <= less than or equal to > greater than >= greater than or equal to $ is contained in
The connective operators recognized by FileFlex are the same as those understood by FoxPro: .NOT., .OR., and .AND. Note that, like the .TRUE. and .FALSE. constants mentioned earlier, these operators must be surrounded by periods to avoid their being confused with strings of characters.
These connective operators can be combined with the logical operators above to create some fairly complex search criteria. For example, if you wanted to find all your customers in California or Arizona with low account balances and who had received your catalog, you might end up with an expression like this:
DBQuery("(State='CA' .OR. State='AZ') .AND. Balance<=1000 .AND. Cat=.T.)")
String Constants in Expressions
FileFlex defines a special string constant operator used within expressions. The string operator is the single quote (') -- make sure you don't use "smart quotes". When you call a search function, you always need to pass it a string containing the search expression. This is string according to the rules of your host development environment. But when you're comparing the state to 'CA', as in the above example, you're passing a string constant to the function. In this case, the string you're asking DBQuery to compare within the expression must be surrounded by single quotes.
FileFlex Intrinsic Functions
FileFlex defines the following functions that you can use in your logical expressions:
ALLTRIM
ALLTRIM takes a string as a parameter and removes blanks from beginning and end [new in 3.0].
CHAR
CHAR takes a number as a parameter and returns the character corresponding to ASCII code [new in 3.0].
CTOD
CTOD (character to date) takes a character string as an argument and converts it into an internal database date format (e.g., CTOD(19901225), which converts the date Dec. 25, 1990, to internal database date format).
DATE
DATE takes no argumentS and returns the system date from the computer's internal clock.
DAY
DAY takes a date string as an argument and returns the day of the date function, as in DAY(DATE()) [new in 3.0].
DEL
DEL takes no argumentS and returns "*" if the current record is marked for deletion, or a blank otherwise.
DELETED
DELETED returns .TRUE. if the current record is marked for deletion, .FALSE. otherwise.
DTOC
DTOC (date to character) takes a database date format value as an argument and converts it into the equivalent string (e.g., CTOD(DATE()), which converts today's date into a character string so that if today were Dec. 25, 1990, the result would be "19901225")
IFF
IFF takes another logical expression as its first argument, followed by an indication of what should happen if that logical comparison is true, and another indication of what should happen if it is false.
The IFF function allows you to nest logical conditions with one expression. For example, you might design a credit collection and management system with the requirement that each customer file has not only a credit limit, but also a description of when you wish to take some collection action. The credit limit might be stored in a field called LIMIT and the action description in a field called COLLECT. This would enable you to establish different collection policies for each customer. You might use a line something like this:
put DBQuery("COLLECT = IFF(LIMIT>10000,'Casual',' '") into dbResult
This would confine your database review to records where the field COLLECT had a value of "Casual" and the LIMIT field is greater than $10,000. In other words, it would allow you to examine all casual customers with high credit limits.
LEFT
LEFT takes two arguments, a string and a count, and returns the left count characters of the string.
LTRIM
LTRIM takes a string as an argument and removes blanks from left of string.
RECCOUNT
RECCOUNT takes no argument and returns the total number of records in the database (not to be confused with the FileFlex DBCount API function, which permits you to retrieve the record count and do something with it other than use it in a logical expression).
RECNO
RECNO takes no argument and returns the current record number.
STR
STR takes a numeric value, a number indicating length and a number indicating number of decimal places as arguments and converts the numeric value into a character string (e.g., STR(5.7, 4,2) will return "5.70").
SUBSTR
SUBSTR extracts a substring of the string supplied as its first argument, beginning at the position specified in the second argument and continuing for the number of characters specified by the third argument (e.g., SUBSTR('ABCDEF',2,3) returns "BCD").
TRIM
TRIM takes a string as an argument and removes blanks from end (right side) of string.
UPPER
UPPER takes a character value as an argument and converts it to uppercase.
VAL
VAL takes a character string and returns its numeric value (e.g., VAL("233") returns 233).
Constructing Search Expressions
Search expressions are pretty easy to construct. Even so, we get many technical support calls from users who get confused about what goes into the various strings. It gets even more interesting when a user wants to construct a search expression containing the value of a host-environment variable. So let's break it down for those using Lingo. First, the typical search expression begins with the function call, followed by an open parenthesis. Everything between the open parenthesis and the closing parenthesis is the search expression (we use DBQuery in this example because it's sole parameter is the search expression):
search expression goes below
----------------------------
put DBQuery( ) into searchResult
To your host programming language, the search expression is simply a string. Therefore, you could put the string into a variable and call pass the variable to DBQuery, as in the following example:
put "UPPER(FIRSTNAME) = 'DAVID'" into queryString put DBQuery(queryString) into searchResult
Notice that the query string, like most normal strings, is bounded by double quotes and placed into the variable queryString. Again, the host environment only knows this as a string and does not parse the contents of the string.
But what if you wanted to replace the literal 'DAVID' with a string of your own choosing? Let's assume that you wanted to find the record matching the contents of the variable myFirstName. What many people do, and what WILL NOT WORK is the following:
-- the following won't work put "UPPER(FIRSTNAME) = 'myFirstName'" into queryString put DBQuery(queryString) into searchResult
All the above does is ask DBQuery to find a record where the contents of the field FIRSTNAME contains the literal string 'myFirstName'. What you need to do is construct a complex string. This is just pure Lingo, there's no FileFlex magic here. The easiest way to see this is to construct a string in the following way:
-- store the first half of the query expression string put "UPPER(FIRSTNAME) = '" into firstHalf -- build the string properly put firstHalf & myFirstName & "'" into queryString put DBQuery(queryString) into searchResult
Take extra care to notice that the single quote (') is contained in the double quotes and is passed to DBQuery. By making sure that the myFirstName variable is passed outside the double quotes, you're ensuring that it's evaluated by your host environment before it's passed to DBQuery.
Confusion evaluating strings has got to be one of the most common technical support calls. So read and study the descriptions above, learn about strings in your host language, and please make sure you understand how strings work in your host language before calling us up. We'll just tell you to read this section anyway!