Need help creating complex lookup expression

[joannristau]

How would I go about creating a table lookup that will return a character value in a field based upon a date value in the current table that is between two date values in the lookup table? For example, the lookup table is quarters and the two date fields are qtrbegdate and qtrenddate. The other table contains transaction records with the date value field called invdate. The field to be filled is QtrID.

Here's an example. Qtr3 record in quarters table has qtrbegdate of 01-06-03 and qtrenddate of 03-30-03. I need the transactions table to compare the value of the invdate field in the record to the qtrbegdate and qtrenddate fields in the quarters table and return the correct quarter ID value from the quarters table in the QtrID field in the transactions table.

Any help you all could provide would be GREATLY appreciated. Thanks!!

[Tim Stephens]

RE: Need help creating complex lookup expression

Look in the expression function reference book at the lookup function. it should do what you want, here is an example that might help you out.

invdate = dtoc(transactions->invdate)
filter = invdate + " >= qtrbegdate .and. " + invdate + "

[joannristau]

RE: Need help creating complex lookup expression

I tried the dtoc function in my expression but it didn't work. Here's what I came up with that works at the set level field rules but not at the table level:

IF(INVHEADER->INVDATE>=QUARTERS->Quarterbegdate.AND.INVHEADER->INVDATEQuarterenddate,QUARTERS->Qtryearid,"")

When I look at the field rule at the table level I see:

IF(INVHEADER->INVDATE>=LOOKUP("QUARTERS","QTRYEARID="+CHR(34)+QTRYRID+CHR(34),"Quarterbegdate").AND.INVHEADER->INVDATE

[Tom Cone Jr]

RE: Need help creating complex lookup expression

JoAnn,

It sounds to me like you're describing a calculated field rule in the field rules for the transactions table. Once the user enters an InvDate value you want the calc expression to lookup (retrieve) a single value from the Quarters table.

If this is the right scenario, the approach Raymond suggests is the way to go, but instead of defining variables which are passed to the lookup() function, just pass the raw expressions themselves.

This would be relatively easy for us to demonstrate for you if you posted a sample data set for us.

-- tom

[Tom Cone Jr]

RE: Need help creating complex lookup expression

Tim, sorry about getting your name wrong. It's funny, you look a bit like Raymond to me.

-- tom

[joannristau]

RE: Need help creating complex lookup expression

Yes that is what I'm trying to do. I will zip it up and post it for you to look at. Thanks so much for the help!

[JohnZaleski]

RE: Need help creating complex lookup expression

Here's a different approach:
Create an index tag called "FINDQTR" based on the expression
dtos(qtr_end_date)

Then the following expression should get what you want:
lookupc("C",dtos(inv_date),"Qtryearid","Quarters","FINDQTR")
It's very easy to test this in the interactive window with a variety of different dates.
As long as you have all the potential quarters filled in properly with the start day of one quarter being one day higher than the end day of the previous quarter, the above should work.

[Tom Cone Jr]

RE: Need help creating complex lookup expression

John,

That looks very promising, and should run faster since it uses the index to do the search. the downside is it requires another indextag. If the Quarters table doesn't have many indextags already it probably won't be noticed since the indextag specification is so simple. -- tom

[JohnZaleski]

RE: Need help creating complex lookup expression

Tom,
I don't think an extra index tag on a table basically used for lookups will matter at all. I usually try to be stingy on indexes on tables that will have a lot of users simultaneously entering and changing records.
However, to make queries even faster, they could have a field on the transaction table QTRYEARID that was a calculated field based on the expression:lookupc("C",dtos(inv_date),"Qtryearid","Quarters","FINDQTR")
This would work as long as the quarter start dates and end dates weren't changable.
John

[joannristau]

RE: Need help creating complex lookup expression

Thank you all for the suggestions. I will be testing them today. I am attaching the db for your review and help. Thanks again so much!

[joannristau]

RE: Need help creating complex lookup expression

John,

Your solution finds the correct qtryearid value and puts it in the field, but I get the following error when saving:

error: lookup(QUARTERS,QTRYEARID="Q103 ",Quarterbegdate) The system cannot find the path specified.
...\databases\pharminv\quarters.dbf

Do I need to put the full path somewhere? Thanks.

[JohnZaleski]

RE: Need help creating complex lookup expression

This error is not related to anything I suggested. It must be left over from something else you tried.

[Tom Cone Jr]

RE: Need help creating complex lookup expression

JoAnn,

After a bit of struggling I was able to implement a lookup() calc field rule in the InvHeader table.

I found two things that were making this very difficult for
you (and me!):

1) the dictionaries for your InvHeader table had become
corrupted. There's an empty Validation field rule for the
QtrId field that is causing trouble. I dropped the table
from the database, deleted the dictionary files (those ending with DDD, DDM, and DDX extensions) and added the table back to the database. This cleared the glitch in table's dictionary files.

2) the Lookup() function requires that the filter expression evaluate to a logical true or false value, but that it be written and passed to the function entirely as a character string. The function does not recognize filter expressions which use numeric or date type field values directly. They must be converted to their string equivalents. Doing this requires that you delve into the single area in Alpha Five that remains unfriendly to mortals.

Here's the filter expression I developed for the calc field
rule for the QuarterId field in the InvHeader table:

LOOKUP("Quarters.dbf","dtos(Quarterbegdate) INVDATE)+" ' .and. dtos(QuarterendDate)>= ' " + \
DTOS(INVHEADER->INVDATE) + " ')","QtrId")

It returns the value of the QtrId field from the Quarters
table, if the value of the InvDate field in the InvHeader
table falls within the range of QuarterBegDate to
QuarterEndDate in the Quarters table.

The DTOS() function converts date values into
char strings fomratted as YYYYMMDD, which was needed
for the comparisons.

The apostrophes and quote marks are mysteries to almost all
of us. If you want to begin to develop a feel for them study an article at www.learn alpha.com called "Understanding Mysteries of Query Syntax" or some such. The glossary accompanying that piece is worth a second glance, too.

To see all this in action, unzip the attachment to an empty
folder. Open the default browse or form for the InvHeader
table, and change the InvDate in a record, or enter a new
record.

-- tom