Best/different way to show one related record in a one-to-many relationship?

[mikedr]

We have a matter table and a correspondence table.  Each matter record can be associated with multiple correspondence records, related by matter number.  Each correspondence record has a correspondence name and a date.

 

On the main layout for the matter table, I would like to show the most recent correspondence record for the currently viewed matter, in uneditable fashion.

 

What I'm doing now is putting a portal on the layout, with a single row and without scroll arrows.  The portal is defined to show related records from the correspondence table, sorted in descending order by date (such that the most recent related record is displayed).

 

This does indeed work great, but I'm just wondering if there is a different or more optimal way to achieve this?

[Kris M]

if you are looking to show ONLY the first related record, based on sort order of the relationship, then you don't even need the portal. if you don't want the data editable then use merge fields or leverage the security engine to restrict edit priveleges.

[mikedr]

OK, I simplified my question a bit too much.  Each correspondence record has a type field, as one of "sent" or "received."  I actually am showing the most recently sent correspondence and the most recently received correspondence.

 

So, in the portal for each, I filter the records so that just "sent" (or "received") records are considered.

 

It seems, then, that I still need to use a portal, to achieve this filtering?  Or ?

[Wim Decorte]

A portal can get expensive because it will load all related data, the client does the filtering.  If you just need to get the most recent and you have a scripted navigation to and from that layout, you can just pick up the most recent through ExecuteSQL() and show the result in layout variables.  Over time; as the # of correspondence records grow this will be a lot more performant.

[mikedr]

A portal can get expensive because it will load all related data, the client does the filtering.  If you just need to get the most recent and you have a scripted navigation to and from that layout, you can just pick up the most recent through ExecuteSQL() and show the result in layout variables.  Over time; as the # of correspondence records grow this will be a lot more performant.

 

Looks like I need to learn the ExecuteSQL command! 

 

However, perhaps another way?  I can set up two dummy fields in the matter table, one always set to "sent" and the other always set to "received."  Then, in the database relationship graph, set up two instances of the correspondence table, one for sent and one for received.  I could relate the sent instance of the corresp table to the matter table by matter number (as before) and also by corresp type of the corresp table to the dummy field in the matter database, and do the same for received.

 

So the idea would be that I could use Kris M's solution above -- the sent instance of the corresp table will only return sent correspondence, and likewise with the received table.

[bruceR]

Hm. ExecuteSQL is quite useful but it seems to be far from established that it delivers performance benefits.

[mikedr]

Hm. ExecuteSQL is quite useful but it seems to be far from established that it delivers performance benefits.

 

I just whipped up the approach I was thinking about afterwards (see below), and this works perfectly, too! 

 

Looks like I need to learn the ExecuteSQL command! 

 

However, perhaps another way?  I can set up two dummy fields in the matter table, one always set to "sent" and the other always set to "received."  Then, in the database relationship graph, set up two instances of the correspondence table, one for sent and one for received.  I could relate the sent instance of the corresp table to the matter table by matter number (as before) and also by corresp type of the corresp table to the dummy field in the matter database, and do the same for received.

 

So the idea would be that I could use Kris M's solution above -- the sent instance of the corresp table will only return sent correspondence, and likewise with the received table.