Finding most recent related records

[Bee Square]

I have a one-to-many relationship from Table A to Table B. Each record in Table B contains a date field. How do I create a found set consisting of the most recent related record in Table B for each primary key in Table A?

[Maarten Witberg]

could be something like this demo

[Bee Square]

Thanks, kjoe. That demo definitely works. It's surprisingly complicated, with 3 TOs of what I called Table B, and a product join relationship. I'll have to spend some time deciphering exactly how it works.

 

Meanwhile, (perhaps motivated by the complexity of the demo) I figured out a way to solve my immediate problem knowing only the dates of the most recent records in Table B (using the Max function), rather than the entire most recent records.

 

Thanks, though -- I appreciate the demo and I'm sure it will come in handy sooner or later!

 

B^2

[Maarten Witberg]

Let me try to explain, maybe you'll agree that it's not so complicated. I am not aware of other, simpler methods, but there might be.

 

There are three steps:

1. parent-child -> that's just data generation

2. work out the max values for each group of child records with identical parent IDs

3. create reports and found sets

 

1. Parent-child is just plain line items work.

2. The relationship child::sjchild ( a self join from ParentID to ParentID ) makes it possible to identify the max ID value (or indeed the most recent date) for the groups of child records having the same ParentID. This is done using the calc MaxIs = Max ( sjchild::childID ). You could do this with the date as well but logically, the most recent date will allways be coupled to the highest serial number.

3. Report is actually a separate table. Using the X join report::child it is possible for the script to generate a list of the current values of MaxIs using List(child::MaxIs). You will notice there are many duplicates in the ListOf field for each report. But that does not really matter practically *). ListOf is then used in the relationship report::child2report to view the sets of max values for each parent. It's making use of the principle of a multikey relation.

 

So the first script creates the on-screen reports in the Report table, enabling you to retain a history of reports as the ListOf field is holding a fixed set of record IDs. For printing purposes, the second script (actually just a button action using Go To Related Records) creates the found set of all child records holding the current max values as per spec.

 

*) if the sets of child items per parent become really, really large it would be wise to work out a way of removing the duplicate values from this list. Doable but requires a bit of work.

[Molson]

I have a one-to-many relationship from Table A to Table B. Each record in Table B contains a date field. How do I create a found set consisting of the most recent related record in Table B for each primary key in Table A?

 

By found set, does this mean you are displaying the related records through a portal? If so, sort the portal by the date field in descending order.

[Maarten Witberg]

Hey michele, using that way couldn't you end up with a list like

 

parent 1 - june 21

parent 2 - june 20

parent 1 - june 19

parent 3 - june 18

etc...

[Molson]

Hey michele, using that way couldn't you end up with a list like

 

parent 1 - june 21

parent 2 - june 20

parent 1 - june 19

parent 3 - june 18

etc...

 

If a list, perhaps. But as I was reading his original post, I thought perhaps he was looking at the list through a portal. If he was on parent 1 record, he would only see the parent 1 child records in reverse chronological order.

 

I may be reading what he wants to do wrong.