You could create an unstored calc field, type text, along these lines …
ExecuteSQL ( "
FROM Activities A1
id_parentForeignKey = ?
theTimestamp = (
SELECT MAX ( theTimestamp ) from Activities A2 where A1.id_activityType = A2.id_activityType AND A1.id_parentForeignKey = A2.id_parentForeignKey
" ; "" ; "" ; id // parent primary key
… and use the field as a match field into your Activities table.
EDIT: Amended to take into account a parent ID.
If you are going to use standard FileMaker relationship techniques (as opposed to SQL) then note that for an unsorted relatioship, the LAST function gets the one you want. That is, it gets the last record, in creation order.
So if there is a relationship to Activities, and say a global field zType that acts as a selector by type for this relationship, then if you set zType to "Call", then Last( filteredActivities::Description) gets you the contents of the Description field for the last (most recent) call.
This assumes record creation order always correctly matches the actual sequence.
If, for instance, you created a Call record today, and then realized oh wait - we had a call that we have not entered yet, that call was last Friday, and you enter THAT call descripton and date, then even though its date is earlier and you enter the correct date in the Activity DATE field, it is the "LAST" call by creation order.
If you are going to use standard FileMaker relationship techniques (as opposed to SQL)
… then here is one way to do it; SQL approach thrown in for free.
If you sort the realtionship itself, you can sort it by date/descending. That way the First related record will be the latest created. You can then put that related record's date field from the related record itself right on the parent record's layout, and it will display the latest date.
If you have several different tables from which you need the latest of several last dates, that will require a calc (probably as a field in the parent table) such as:
- Max (tableA::fieldX ; tableB::fieldY ; etc)