Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: help with math calculation/function (FMP 6)

    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

  2. #2
    Join Date
    Aug 2005
    Location
    Minneapolis, MN
    Posts
    56
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Thanks for the reply. I was afraid it wasn't going to be easy. I'll give your suggestion a try.

  4. #4
    Join Date
    Apr 2004
    Posts
    61
    Sorry Ender,

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

    What do you think?
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2005
    Location
    Minneapolis, MN
    Posts
    56
    Clever technique!

  6. #6
    Join Date
    Apr 2004
    Posts
    61

    You rock, Ender!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •