FM 5.5 'Rank' solution. [Finished-Dev 8]

[CoZiMan]

Description: In answer to a question on another forum:

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.

Here is the sample file.

Working Under: Dev 7

Solution Status: Finished

Pre-requisites: fm 5-8

Author(s) CoZiMan

Date: 03/05/06

Credits: CoZiMan

Instructions / Other Info: 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 when i originally built this 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.

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

TestRank.fp5.zip

Edited March 5, 2006 by Guest

[Ender]

A couple things:

You should change the versions info above to reflect that this is an FM5/6 file. ".fmp" is not a valid FileMaker extension. It should be ".fp5", but when changing this your valuelistitems() calc will need to be updated as well.

In FM7/8, one could use the same idea optimized a bit, as in cjaeger's example here:

http://fmforums.com/forum/showtopic.php?tid/123618

[CoZiMan]

Thanks, Ender. Corrections to the post, and file, completed!