Jump to content

Displaying value from the last record in a related table


Asu
 Share

This topic is 2247 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Asu

Link to comment
Share on other sites

This topic is 2247 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.