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 pro-

gram 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.

**

Time Test for

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

seconds versus the

Lookup Command that took .002

(Con't, page 8)

Page 7