Field Rule Help -- having a mental block on lookup()

[joannristau]

Hi,
I'm having a mental block with writing a field rule. I have a field in a table called pfl-detail that needs to show the period name, using a lookup to the gl_periods dbf file. I used my expression that works to find the 'Current' period as a guide to get the period name based on the doc_date field.

For example, the gl_periods dbf contains rows with the following fields pertaining to the period record:

period_name, period_year, start_date_dt, end_date_dt

I need to populate the pdnm field in table pfl-detail based on the date contained the doc_date.

Here is what I have tried that doesn't work:

LOOKUP("gl_periods.dbf","dtos(Start_Date_Dt)>=dtos(doc_date).and.dtos(End_Date_Dt)<=dtos(doc_date)","Period_Year")

I used this code as a guide :
LOOKUP("gl_periods.dbf","dtos(Start_Date_Dt)<=dtos(date()).and.dtos(End_Date_Dt)>=dtos(date())","Period_Year") /*returns the current period year*/

I'm sure this will be an easy one for somebody, but I can't seem to figure it out.

Hope someone can help me! Thanks!

[Stan Mathews]

Re: Field Rule Help -- having a mental block on lookup()

Your guide expression works because Alpha knows to evaluate date(). You modified expression doesn't work because Alpha doesn't know how to handle doc_date.

Looks like I misread the data type on doc_date.

doc_date = {11/15/2010}

? "dtos(Start_Date_Dt)>=dtos({"+dtoc(doc_date)+"}).and.dtos(End_Date_Dt)<=dtos({"+dtoc(doc_date)+"})"
= "dtos(Start_Date_Dt)>=dtos({11/15/2010}).and.dtos(End_Date_Dt)<=dtos({11/15/2010})"

Whole expression...

LOOKUP("gl_periods.dbf","dtos(Start_Date_Dt)>=dtos({"+dtoc(doc_date)+"}).and.dtos(End_Date_Dt)<=dtos({"+dtoc(doc_date)+"})"
,"Period_Year")

[joannristau]

Re: Field Rule Help -- having a mental block on lookup()

Thanks Stan. I tried your code, and still didn't work. I get 'Argument is incorrect data type' error.

[Stan Mathews]

Re: Field Rule Help -- having a mental block on lookup()

Edited my earlier reply. I misread the data type on doc_date.

[joannristau]

Re: Field Rule Help -- having a mental block on lookup()

Tried converting everything to character. I added two fields in gl_periods; s_start_dt and s_end_dt. They are based on field rule in that table of dtos(Start_date_dt) and dtos(End_Date_dt).

Created another field in pfl-detail called crmemodate that is dtos(doc_date).

Edited the code as follows:

LOOKUP("gl_periods","S_start_Dt>=crmemodate.and.S_end_dt<=crmemodate","Period_Name")

Still doesn't work.

[Stan Mathews]

Re: Field Rule Help -- having a mental block on lookup()

Inside of quotation marks Alpha treats variables as the text of their names, not their contents.

"S_start_Dt>=crmemodate.and.S_end_dt<=crmemodate"

is evaluated like

S_start_Dt>=crmemodate

not

S_start_Dt>=value_of_the_variable_crmemodate


See the edit of my earlier post.

[joannristau]

Re: Field Rule Help -- having a mental block on lookup()

Stan,
You are the GENIUS. Thank you, that worked! I changed the code per your example, recalculated field rules, and now have the period names!