Jump to content

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


mikedr
 Share

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

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2884 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.