I would like to have a calculation field that returns the rank (e.g. 1st, 2nd, 3rd or even just 1 2 3 etc...) of another field's contents with respect to all records (and for a group of records). It would seem that something simple as this would be built in, but apprently it's not.

Using the Status(CurrentRecordNumber) won't really work as the records are sorted in a variety of other ways and not with repsect the field in which I would like the rank. I'd prefer to do it using a math formula.

I've tried searching everywhere in FM documention and this and other forums without success.

Any help would be appreciated

FMP v. 6 mac OSX

It's not possible to do this in a calc field in FM6.

You might try a script that loops through the records and sets a Rank field for you. Roughly, you'd sort by the field you're ranking, then keep a global gCounter and a global gLast Value. At each iteration of the loop, if the current Value is not the same as the gLast Value, increment the gCounter, set the Rank as that gCounter, and update the gLast Value. This assumes that equal values are ranked the same.

Thanks for the reply. I was afraid it wasn't going to be easy. I'll give your suggestion a try.

Sorry Ender,

Here's an example in FM 5.5 with EXACTLY what this user wanted.

What do you think?

Clever technique!

I've read lots of your posts on other forums and you have great examples.

Thanks for the nice comment. I had to do this once for a project and it sticks in the memory when you bash your head against something for awhile.

I couldn't find the original but this one was a quick build.

Takes 1 relationship, 1 value list, 4 or 5 variables, and 1 'clever' calculation to create a ranking for ANY type of data in each grouping.

At first I was discouraged about value lists not updating immediately and almost gave up, but even though the displayed value list doesn't update on data entry, the 'rank' calculation DOES! Like Edison said, 99% perspiration.

What I used this for was to 'hold' sub-sorts before we could save them, but it works for lots of things.