First Max Value

[Pixbo]

I hope someone can help me with this. I have two tables, People and Complications. Each person could have a varying number of complications. Besides viewing each person’s complication information through a portal I will need to also show the FIRST time a person’s complication grade is at its highest level (range is 1 – 5 and Type is irrelevant). For example the portal data would look like this:

Grade..Type....Start.....End

2........4.......3/1/00....6/1/00

2........5a......1/1/02....2/1/02

1........3.......5/1/02....9/1/02

4........1.......2/1/04....3/1/04

2........1.......3/1/04....4/1/04

The returned result would be Grade: 4, Type: 1, Start: 2/1/04, End: 3/1/04

*If the grade 4 entry was not there then the returned result would be Grade: 2, Type: 4, Start: 3/1/00, End: 6/1/00.

I hope this makes sense and any help would be appreciated.

Thank you!

[comment]

There are several ways to do this, I think this one would be the easiest:

Place another portal to the same table on the layout. Make it show only one row. Sort the portal records by Grade descending, then by Start ascending. Place any fields you want to view from Complications in this portal.

[Raybaudi]

What about order the portal by Grade ( descending ) and by StartDate ( ascending ) ?

P.S. oops...too late !

[Pixbo]

Yeah that is about as far as I have been able to get. The part I am at now is how do I store that particular line of data for export?

[comment]

That's an entirely different matter. Which table are you going to export from, and in what format?

[Pixbo]

I would export from the People table most likely into an excel format.

[comment]

An easy to implement though tedious method would be to sort your relationship (not the portal) in the same way, and define a calculation field in People for every field in Complications you want to export. The calculations are simply =

Complications::SomeField

A smarter method would be to do the export from the Complications table, after sorting the records by Person/Grade/Start, and performing a Fast Summary by Person.

[Pixbo]

That is perfect!

Thank you so much for the direction to Fast Summaries. This is exactly what I needed. You're a life saver once again. :)