Highest of related records from 3 tables

[Stickybeak]

I have a parent table which has 3 child tables all of which have a date field.

I would like to have a field in the parent table which tells me what is the latest date in the 3 child tables so I know the last time there was activity relating to the parent record.

I can figure it out.

 

[comment]

You could use a calculation =

Max ( 
Max ( ChildTableA::Datefield ) ; 
Max ( ChildTableB::Datefield ) ; 
Max ( ChildTableC::Datefield ) 
)

However, if you have 3 child tables that are alike in that you need to know the latest date in all three, then maybe they should be consolidated into one.

 

[Stickybeak]

superb - thanks

[Rich]

Follow-up question, please:

Say, in the child table, there's a Description (text) field. While Max (ChildTableA::Datefield) will grab the latest date (from Table A), how would you grab the contents from the latest date's accompanying Description field as well?

Edited December 24, 2019 by Rich
grammar police

[comment]

If it's for display only, you could use a one-row portal sorted by date, descending (or filtered to show only the most recent record), with any combination of fields.

If you need it at the data level (e.g. for further calculations), you may be able to use the Last() function; this will get the data from the last record (in the sort order of the relationship) where the description field is not empty. For another option see:
https://www.briandunning.com/cf/908

 

Edited December 25, 2019 by comment