The LA Fox Developer Newsletter

February 1996

LOOKUP() (Con't from page 1)

Now, just as Lookup() would do, we must figure out where the information that we want to have returned to us is stored. Usually, this will be another alias. We will use the Substring and AT functions to parse out the alias name from the fully qualified return fields name. Then we will change to that alias and save its current order/tag name.

jcNewAlias SUBSTR( pcRetFld,

1, AT( “.“, pcRetFld))

SELECT (jcNewAlias)

jcCurrentOrder ORDER()

The fourth parameter is the tag name. Foxpro’s help file describes the fourth parameter as optional. If you want the benefits listed in this article, the fourth parameter is MANDATORY. Why? If you don’t include the fourth parameter, Lookup() does a Locate, which can be slow. However, if you do include it, Lookup() will perform a Seek which is very fast. For our UDF, we are going to assume the fourth parameter is always passed. Since we have the tag name, we must set the order of the current alias to the passed order.

SET ORDER TO (pcTagName)

Next, we search for the key value that was passed to the UDF as parameter #2. This key can be a simple key like a customer # or a complex key like customer # + invoice # + DTOS( invoice date). Either way, the search key should match the key value of the order that was passed as parameter four.

IF SEEK( pcKey)

Now, the UDF must take action based upon the result of the seek. If it is found, the UDF stores the contents of the return field’s name in a variable. Otherwise, it must find out the data type of the return field and fill the return variable with an empty value of the same data type and length.

jRetumvaiue = EVAL( pcRetFld)

ELSE

jcType TYPE( EVAL( ‘pcRetFld’))

DO CASE

cASE jType ‘C’

jReturnVaiue = 5PACE(;

LEN( EVAL( ‘pcRetFld’)))

CASEJType ‘D’

jR.tumvalue = CTOD(”)

CASE jType ‘L’

jRetumvalue .F.

CASE jType ‘N’

jRetumvalue = 0

ENDCASE

Either way, whether the search value was found or not, the UDF must set the order of the table back its original state. Then, the UDF should switch back to the original work area that the system was in prior to the UDF being called.

SET ORDER TO (JcCurrentOrder)

SELECT (jcCurrentAlia)

Finally, the UDF will return whatever value is in the return variable. This value could be character, numeric, logical or even a date. This UDF and the Lookup() command are very flexible.

RETURN jRetumValue

The entire UDF takes 26 lines of code. Below is a program that calls both the UDF and the lookup command. The tables used are customer.dbf and invoices.dbf, both available as part of the sample data that accompanies the installation of Foxpro.

Lookup Equiv. UDF &

Lookup()

USE \fpw26\sample\catalog\Customer;

ORDER company IN 1

USE \fpw26\sample\catalog\Invoices IN 2

SELECT Invoices

CLEAR

? SECONDSØ

? MYUDF( 'Customer.company',;

Invoic.s.cno, ‘Customer.cno’, ‘cno’)

? SECONDSO

‘P SECONDSO

‘P LOOKUP( Customer.company,;

Invoices.cn Customer.cno, ‘cno’)

‘P SECONDSO

Here are the screen results:

38689.434

Carsonville W.B. Associates

38689.494

38689.496

Carsonville W.B. Associates

38689.498

As you can see, the UDF and the Lookup Command

returned the same values, however, the UDF took .060

(Con't, page 8)

Page 7