Specific record in child table wanted 1

[Toman]

Hi all,
A child table in relation has date type field and has typically more records corresponding to one record in parent table.
I need to see only the "youngest" record in relation when I move the record pointer in the parent table.
Any suggestion? SQL will do also.
Thanks in advance. Toman

 

[JRB-Bldr]

For a non-SQL approach, one way is to build an index on your Child table that includes, not only your Relation Field, but also the Date field in an expression such that it sorts with the "Youngest" record to the top.

Make this the active Index on the Child table.

Then SET EXACT OFF before setting up the Relation from the Parent table so that it will ignore the extra Date part of the Child table index expression.

Not knowing if you mean "Youngest" to be "most recent" or "oldest" record you might have to play with it to get it right. But an example might be:
USE Child IN 0 EXCLUSIVE
SELECT Child
* --- If Cust_ID was a Char Field ---
* ---- Display "Most Recent" Date First ----
INDEX ON Cust_ID + PADL(ALLTRIM(STR((CTOD("12/31/2010") - ChildDate))),6,"0") TAG key

SET EXACT OFF
SELECT Parent
SET RELATION TO Cust_ID INTO Child
< and so on >

For an SQL approach to create a single resultant table, it might be even easier, but you would need to specify fields which will appear in both tables.

Something like..

SELECT Parent.Cust_ID,;
Child.ChildDate,;
Child.whatever,;
Parent.whatelse;
FROM Child, Parent;
WHERE child.cust_id = parent.id;
ORDER BY Parent.Cust_ID, Child.ChildDate DESC;
INTO CURSOR Temp

If you only wanted the SINGLE "Youngest" Child record, then you could use MAX(Child.ChildDate) or MIN(Child.ChildDate) depending on which was the "youngest". And then add a GROUP BY... line

Good Luck,
JRB-Bldr

 

[Toman]

The answer fits perfect.
I was afraid even thing about
index on Cust_ID + SomethingWithDate
because it could broke the relation (in my poor opinion)
Probably SET exact off is the most important here
Thank you very much again.
Toman