Monday, July 19, 2004

Solution for Normalizing Filters()

I recently posted an article about how the FILTER() command doesn't always return back what you want it to.

Well, Sergey Berezniker set me straight - that's why there's a function called NORMALIZE.

I had never heard of this function before although it's been in FoxPro for quite a while (even back in FPW 2.6).

From the help file: NORMALIZE( ) returns a character string from the character expression cExpression with the following changes:
The character expression is converted to uppercase. However, embedded strings are not changed. An example of an embedded string is "Hello" in the character expression "LEFT('Hello',1)".
Any abbreviated Visual FoxPro keywords in the character expression are expanded to their full length.
Any -> operators separating aliases from field names are converted to periods.
The syntax of any Visual FoxPro commands or functions within the character expression is checked, although the expression is not evaluated. If the syntax is incorrect, Visual FoxPro generates a syntax error. NORMALIZE( ) does not check for the existence of any fields, tables, memory variables, user-defined functions, or other references in the character expression.

WHat this means is that if you put in something like:
SET FILTER TO (company_name!="Alfred" OR region<>"NC") AND country = "United States"

You can do
a ? NORMALIZE([(company_name!="Alfred" OR region<>"NC") AND country = "United States"])

and it will return
(COMPANY_NAME#"Alfred".OR.REGION#"NC").AND.COUNTRY="United States"

What a life saver! Thanks Sergey for bringing it to my attention.

Andrew MacNeill - AKSEL Solutions: Beware of how the FILTER() works

No comments: