I'm learning FM Pro 7 after years doing Access and SQL Server. Making
the adjustment pretty well, but one simple thing still eludes me.

How can I accomplish the equivalent of what in SQL would be:

SELECT Max([Date]) AS MAXDATE, tblPeople.Person
FROM tblDates INNER JOIN tblPeople ON tblDates.IdNum = tblPeople.IdNum
WHERE (((tblPeople.Person)="John"));

That is to quite simply get the maximum value in a field but
restricting to records in the table related to a value within another
table. I need to get this type of value for display and autoentry
purposes

I suspect this is really simple and I just need to learn a new way to
think about it. It seems like combining a Find command with a
function, but I'm not sure how to do it.

Thanks!

Paul Norris
UCSF

In article <1128036415.396490.107750 (AT) g49g2000cwa (DOT) googlegroups.com>,
pnorris (AT) itsa (DOT) ucsf.edu says...
There are actually a few ways of doing it.

Most of the time, its simply a matter of defining an unstored
calculation field in the appropriate table

maxdate = Max(relatedrecord::date)

Im guessing your table structure resembles:

tblDates = {date, idnum}
tblPeople = {person, idnum}

And that you have a relationship from tblPeople to tblDates on idNum

.... then...

If the context is that you are on a layout looking at a tblperson
record where person="John", then defining maxdate as described above in
the tblPeople table will get you the value you need.

Hope that at least gets you pointed in the write direction...

-cheers,
dave

Thank you! This is EXACTLY what I needed. Part of the key was to
create the calculation field in tblPeople rather than in tblDates.

Paul