SET (a little special) RELATION

[Toman]

I'd like to link two tables with common date type fields. The child table has only a few records (stores a history of prices, date field indicates date when the price was changed).

If the date from parent table is not included in a child table, record pointer in the child goes to EOF position. This is a standard behavior but not what I need. In that case I'd need pointer into child to show record with the date which is first less then the date of parent's record, so showing the valid price for given period.

Can this task be solved with SET RELATION command?

Thank you for any recommendations.
Tom.

 

[StewartUK]

Tom,

I don't think so.

You could try turning the relation off entirely and then when the record pointer moves in the parent table, use either SEEK() or INDEXSEEK() to move the pointer in the child table.

Doing it that way, you can then use SET NEAR and it might achieve what you want.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[brigmar]

The key is to index the child table by descending dates.

SELET child
INDEX ON date TAG datedown DESCENDING
SET NEAR ON

IF SEEK( parent.date, 'childtable', 'datedown' ) OR !EOF('childtable')
* closest date BEFORE the parent date
ENDIF

 

[Toman]

Hi Brigmar,

I understood your post like precise and elegant solution how to find record in one table under conditions we discuss here. Namely SET NEAR is important tip to me - mentioned also by StewartUk.

But not sure about your using words parent, child. Are you using it just for keeping continuity and clarity in this thread (and think that setting relation is impossible like StewartUK), or you indicate that setting relation would be possible???

Thank you Tom

 

[AndyKr]

>>or you indicate that setting relation would be possible

I don't think you can use a relation here irrespective of whether SET NEAR works with relations (it doesn't!).

The reason is that it makes no sense at all! In order to use a relation, the date would have to be part of the relational expression and that would be meaningless in this context - because if there isn't a date, then by definition there can't be a record related to one that has a date.

I think that you will have to do this with an index and seek. By the way, you don't actually need to use SET NEAR - you can use this code instead:

IF NOT SEEK( field, table, key )
  GOTO RECNO(0)
ENDIF

That will also position the record pointer on the closest match - just as SET NEAR does - but doesn't require SET NEAR to be ON.


----
Andy Kramek
Visual FoxPro MVP

 

[brigmar]

My post was a follow-on from StewartUK's; I should have made that clearer. It cannot be done using SET RELATION.

AndyKr : Nice to now about the SET NEAR ON replacement.

 

[Toman]

Stewart, Brigmar, Andy, SET RELATION
bye-bye and thank you

Tom

 

[NasibKalsi]

Tom,

The the following might entertain your question.

Create a temporary table with a common field(Grand Parent).
Append from both tables(Parent and Child. Use this table as the primary table. Then set the relation to both tables(Parent/Child). Check the speed(if table size is large)

Nasib Kalsi