Writing Expressions

When you pass an expression to a function or method, you are passing a line of Xbasic code as a character string. As result, whether the expression is used to filter or order records, whether it evaluates to a character or a logical value, the argument is type C (character).

There are several principles that you should understand when writing an expression argument.

Character Filter Expressions

Many functions have character filter expressions as arguments. While this type of expression has a character value, it is different from a normal character variable. It is a line of Xbasic code that will be interpreted. A character expression has the following rules for formatting its components:

Element	Rule
Xbasic Language	Quoted
Relationship Operators	Quoted
Table Field Names	Quoted
Constant values	Quoted
Character variables	Presented inside the Xbasic functions such as DTOC(), FORMAT_DATA(), QUOTE(), and S_QUOTE() that convert the variables to character values
Date variables
Logical variables
Numeric variables

Consider the following example in the Interactive window of the Code Editor.

dim nam as C = "Bob"

query.filter = "customer->firstname > nam"

? query.filter

= "customer->firstname > nam"

Examine the value of query.filter. It is a character string. Alpha Five has not identified that you are referencing the nam variable, which is incorrectly treated as the character value "nam". The following expression is correct.

dim nam as C = "Bob"

query.filter = "customer->firstname > " + s_quote(nam)

? query.filter

= customer->firstname > "Bob"

In this case Alpha Five knows that you are referencing the customer->firstname field and the nam variable has the value "Bob".

Character Arguments

You should place an extra pair of quotes around character arguments inside the expression or use a quoting function.

... + "lastname = 'Smith'" + ... or

... + "lastname = \"Smith\"" + ...

Examples using variables and constants:

dim nam as C = "James"

' Version 6

"FieldName > " + S_QUOTE("James")

"FieldName > " + S_QUOTE(nam)

' Version 5

"FieldName > " + QUOTE("James")

"FieldName > " + QUOTE(nam)

Numeric Arguments

Examples using variables and constants:

dim dsc as N = 2

' Version 6

"FieldName > " + S_QUOTE(dsc)

"FieldName > 2"

' Version 5

"FieldName > " + dsc

"FieldName > 2"

Date Arguments

Examples using variables and constants:

dim dt as D = {1/2/2001}

' Version 6

"FieldName > " + S_QUOTE({1/2/2001})

"FieldName > " + S_QUOTE(dt)

' Version 5

"FieldName > {" + DTOC(dt) + "}"

"FieldName > {" + DTOC({1/2/2001}) + "}"

Logical Arguments

Examples using variables and constants:

dim TF as L = .F.

' Version 6

"FieldName = " + S_QUOTE(.F.)

"FieldName = " + S_QUOTE(TF)

"FieldName = .F."

Version 5

"FieldName = " + IIF(TF, ".T.", ".F.")

"FieldName = .F."

Putting it all together

dim TF as L = .F.

dim dsc as N = 2

dim dt as D = {1/2/2001}

dim nam as C = "James"

' Version 6

query.filter = "FieldName1 = " + S_QUOTE(TF) + " .and. FieldName2 > " + S_QUOTE(dt) + " .and. FieldName3 > " + S_QUOTE(dsc) + " .and. FieldName4 > " + S_QUOTE(nam)

Version 5

query.filter = "FieldName1 = " + IIF(TF, ".T.", ".F.") + " .and. FieldName2 > {" + DTOC(dt) + "}" + " .and. FieldName3 > " + dsc + " .and. FieldName4 > " + QUOTE(nam)

Character Order Expressions

Record sorting is specified using a character order expression. While this type of expression has a character value, it is different from a normal character variable. It is a line of Xbasic code that will be interpreted.

Note: This expression must be less than 240 characters long.

When Alpha Five sorts, it retrieves records and evaluates the order expression using the records' field values. As the order expression is evaluated for each record, the result, or key, is entered into an internal list. Alpha Five then sorts the keys in this list alphabetically to determine the record order.

For example, you have the following table of customers:

State	City	Lastname
MA	Boston	Marshall
IL	Springfield	Adams
VT	Burlington	McConnell
VT	Burlington	Flanders
MA	Wellesley	Gordon

If you want to sort these records by the STATE, CITY, and LASTNAME fields, you can use the order expression STATE+CITY+LASTNAME. Alpha Five evaluates this order expression for each record and lists the keys as shown:

Key

MABOSTON MARSHALL

ILSPRINGFIELD ADAMS

VTBURLINGTON MCCONNELL

VTBURLINGTON FLANDERS

MAWELLESLEY GORDON

The keys are sorted alphabetically, and then Alpha Five displays the records in the correct order.

Key

ILSPRINGFIELD ADAMS

MABOSTON MARSHALL

MAWELLESLEY GORDON

VTBURLINGTON FLANDERS

VTBURLINGTON MCCONNELL

State	City	Lastname
IL	Springfield	Adams
MA	Boston	Marshall
MA	Wellesley	Gordon
VT	Burlington	Flanders
VT	Burlington	McConnell

You do not need to use whole fields in order expressions, although you will have to consider whether the sort results will be adequate for your needs. The following expressions are all valid.

query.order = "state + city + lastname"

query.order = "left(state,3) + left(city,4) + lastname"

In the Interactive window of the Code Editor, you can see the results of an order expression.

dim tbl as P

dim qry as P

tbl = table.open("customer")

query.order = "left(lastname,1)+left(firstname,1)"

query.filter = ".T."

qry = tbl.query_create()

tbl.fetch_first()

while .not. tbl.fetch_eof()

#? tbl.firstname + tbl.lastname

#tbl.fetch_next()

#wend

Randi Adams

Winston Abrams

George Boschetti

Jeffrey Bernstein

John Baker

Leonard Burtonski

Tom Barber

Herb Copen

...

Note: Using less than the entire field value results in a list that is not completely alphabetically ordered.

Alpha Five creates and sorts keys automatically, so you do not need to know much about keys, except that they are created using an order expression and are sorted alphabetically.

When creating order expressions, there are tools available to you: the Order Builder and the Expression Builder. The Order Builder is the easiest way to create an order expression, and is appropriate for most situations.

The Expression Builder is more involved, but it lets you test the order expression by displaying the keys that are generated.

Adding Quote Characters

If you need to include a quote in an expression, use a backslash to escape the quote.

my_var = "He said \"Hello, how are you?\""

? my_var

= He said "Hello, how are you?"

or you can use chr(34), such as:

my_var = "He said " + chr(34) + "Hello, how are you?" + chr(34)

and finally, you can use the QUOTE() function:

my_var = "He said " + quote("Hello, how are you?")

All of these give you the same end result, so it's a matter of personal preference.

Adding Backslash Characters

If you need to include a backslash in an expression, use a second backslash to escape it.

myvar = "\\"

? myvar

= "\"