Displaying value from the last record in a related table

[Asu]

Hello FM Mavens, 

I have an encounter database with a main table that is related to a "clients" table which is related to an "encounters" table.

The main table has a portal that displays the clients sorted by the date by which they were last seen. It displays their name, etc, from the "clients table", but in the same portal row I would also like to display the  "type of encounter" from the last encounter they had, which is a text field in the "encounters" table.

How can I accomplish this?

Thanks!

[comment]

I don't understand your description. Isn't the date field in the Encounters table?

[Asu]

Yes, the date field is in the encounters table. I can display the most recent encounter date by using a summary field for the date (Max date). I can not do this for a text field.

There are 3 tables, a "Main" table for the entire business, with one record. This has a portal to the "Clients" table. Each client has many encounters of course, contained in the third, "Encounters" table. Each individual encounter has a description (e.g. "short", "long", "correspondence" etc) held in a text field. I want to display the content of this "most recent text field" in the aforementioned portal.

Thanks.

[comment]

29 minutes ago, Asu said:

Yes, the date field is in the encounters table.

Well, then you cannot have a portal to Clients sorted by the date.

In any case, if your TOG looks like this:

Main x Clients -< Encounters

you could sort the related records from Encounters by Date, descending. Then any field from Encounters you place inside the portal to Clients on the layout of Main, will show data from the most recent encounter of each client.

Alternatively, you could leave the relationship unsorted (i.e. in creation order) and define a calculation field in the Clients table =

Last ( Encounters::Type )

and show that in the portal. Note that this assumes records are created in chronological order and that the Type field is never empty.

[Asu]

Thanks, the Last() fcn was the solution. I was not aware that it cold be used for other then repeating fields. 

Asu