>Hi
>
>I am developing an work order system. Each product/Service has a price based on the effective date (i.e. the price becomes effective on a certain date)
>
>I wrote the following function in the 'Stored Procedures' section for the database. WOTS.dbc
>
>PROCEDURE relCurrPrice
>LPARAMETERS cPrSched, cPcode
>*
>* find the current price in price schedule = cPrSched
>* for product/service = cPcode
>*
>LOCAL cSeekKey
>cSeekKey = cPrSched+cPcode+STR({^3000/01/01}-DATE())
>cSaveAlias=ALIAS()
>
>SELECT SPECPRICE
>SET ORDER TO 1 && PRSCHED+CODE+STR({^3000/01/01}-EFFDATE)
>cSaveFilter = FILTER()
>SET FILTER TO prsched=cPrsched AND code=cPcode
>
>lFound=INDEXSEEK(cSeekkey,.T.)
>IF !lFound
> SKIP -1
> IF EOF()
> lFound = .F. && No record for this Pcode
> ELSE
> lFound = .T. && We are on the current price
> ENDIF
>ENDIF
>* Restore entry state
>SET FILTER TO &cSaveFilter
>SELECT (cSaveAlias)
>
>RETURN lFound
>ENDPROC
>
>**********************
>I added the parent 'PRODSERV' and child 'SPECPRICE' tables and
>set the RelationExpression to 'relCurrPrice(prsched,code)'
>
>I then added some test fields from each table to a test form.
>
>The relationship expression gets fired and finds the correct record
>but I then get an 'error 67' 'expression evaluator failed' in 'DataEnvironment.OpenTables' method.
>
>Obviously I am doing something wrong. can someone please point me in the correct direction.
>
>Ta very muchly
>Geoff Scott
Geoff,
There are a number of workarounds. If your only concern is to get last effective date then you don't need relations. However for now I think you need it. One quick way I can think of :
index on prsched+code+dtos(effdate) tag descending && No need for a big date
set relation to prsched+code into child
When pointer moves in parent :
select child
lcCode = parent.prsched+parent.code
set key to range lcCode,lcCode+dtos(date())
Cetin