Hey there,

If I wanted to find the highest value within a relationship I'd use

max(relationship::field)

How do I find the 2nd highest value?

Thanks,

Dan

Omit the highest valued records and use the Max() function again? Just guessing here...

Or you could do a find in the child file, using the relationship key as the "find" field, sort them in decreasing order and then go to record number 2.

It's easily scripted, it's more cumbersome if you want to have a calc field

You might do it sorting the relationship, grabbing the related values using a relational value list and the ValueListItems function and finally using the text parsing function to get the second item in the list

See this tip file on the matter:

http://www.buliga.it/TipFiles/Ultimi%20due%20RR.sit

http://www.buliga.it/TipFiles/Ultimi%20due%20RR.zip

Hi,

Category field

Price

MaxPrice by category = Max(selfjoinOnCategory::Price)

Intermediate Calc = Case(Price < MaxPrice by category,Price, 0)

MaxSecondHighest Price = Max(SelfjoinOnCategory::Intermediate Calc)

Intermediate2 = Case(Price<MaxSecondHighest Price, Price,0)

MaxThirdHighest Price = Max(SelfjoinOnCategory::Intermediate2)

...

Thanks everyone,

These are great ideas. Ugo's solution does the trick and avoids using a script, so I'm declaring it the winner. ;-)

I hope I can get to the point where I'm answering half as many questions as I'm asking.

Cheers,

Dan

Hi Dan,

Actually, after re-reading all the answers, I think Vaughan had it the same way .

Omit the highest valued records and use the Max() function again? Just guessing here...

Just guessing here ?

I want to have Vaughan kids!!!

Oh yeah, I guess so. Thanks, Vaughn. It makes more sense now that I've walked through it.

Unfortunately, after I got it up and running, I realized that that wasn't quite what I was after. It's more subtle than that, and I'm not sure I can even really describe the problem. I'm still working on wrapping my head around it.

But thanks for the help. Always good to learn a new technique.

Dan

Ugo wrote: "I want to have Vaughan kids!!!"

ROTFLMAO!

I'm still clueless with this "children"/ "kid" problem. And I'm now clueless with ROTFLMAO....

Too many secrets

Rolling on the floor laughing my a.. off

Hi Lee,

One secret is solved now...

Hey Guys,

Now that everyone has ROTF and LTAs off, I figured out what my real question was. (Must be all that laughing relieved some stress and helped me to clarify my thought process.) :-)

I didn't just want to find the second highest value in a relationship, but the highest value in a relationship that is less than the current value.

For example...

A 1

B 2

C 3

D 4

E 5

So for record B, the highest value that is less than B's value (i.e., 2) is 1. For record C it is 2. For record D it is 3, and so forth.

Any thoughts on how to find this one? I'm thinking that I'll have to rely on a script for this. Yet in the back of my head I still think I can do it with a calculation. But my logic centers must be out of whack, 'cause I can't get this.

Thanks,

Dan

Hi Dan,

Scripted method I think.

Set fied g_record_ID (record_ID)

Go to related record (show all)

Sort on your number field descending

Loop

Go to next record

End Loop when Record_ID = g_record_ID

Go to previous record

Omit

Show omitted.

Thanks Ugo,

I've gone with the script method. The actual situation was quite a bit more complex than I described, so the script got pretty convoluted. But your's was a good starting point. It seems cumbersome -- at least it was to write -- but it works and it seems pretty quick. We'll see once I get the db full of data.

Cheers,

Dan

Hi Dan,

This scripting method would lead you to another layout, isolating (omitting...) one record...Not really dynamic....

Depending on your file structure and interface needs, you should take a look at "fixed portals" at http://www.fmfiles.com or even DJ's renumbering portal in the sample section.

Scripting the find using these solutions, would offer you a cleaner way to view at the fly within the current layout (you surely have some room left) the record you are looking for, even highlightting it.