13 Replies Latest reply on Jul 14, 2010 1:08 PM by MK

    RANK function



      RANK function


      Probably I am missing something obvious, but is there a simple function for ranking table records by a field parameter? e.g., table of Students, with RANK field defined by Exam Score?


      I suppose it can be done through a sorted portal and Get(PortalRowNumber), but I was hoping for a straightforward aggregate function like Min, Max, Average, etc.


      Thank you,



      FMP 10 Advanced, Mac OS X 10.6

        • 1. Re: RANK function

          You could sort your records in ascending order of rank and then use Replace Field Contents to put a serial number in the rank field.

          • 2. Re: RANK function

            Not really, because equal values are supposed to have equal rank.

            • 3. Re: RANK function

              Thanks, guys.


              The point comment makes is a major reason why I prefer not to use the results of a sorted portal. (There is no such thing as two equal portal rows, eh?)


              So I am concluding that there is no simple RANK function. Right? Strange, I think. Spreadsheets have it.


              Thank you.


              • 4. Re: RANK function

                There is no Rank() function in Filemaker.You could probably write a custom function for it, but I don't think that would be simple. If you want to rank child records, you can add a self-join of the child table with = parent ID and > value, then count  the related records.

                • 5. Re: RANK function

                  I have put @@ into a portal in the past to pull up the record number based on the sort order.  That worked for me in creating a rank of records.  Doesn't solve the issue of two numbers being the same though.

                  • 6. Re: RANK function

                    I sure using summary fields and functions you could derive what you need.  Google for the function defining rank and you'll probably be able to figure it out.

                    • 7. Re: RANK function


                      I assume you want a Rank field in the table with the Score field, yes?
                      If so, create a 2nd instance of this table in the Relationship Graph, let's call it TO2, then add a relationship as follows:
                      originalTO::score < TO2::score
                      Then add a calc field, Rank, evaluated in the context of the original TO, with the calc: Count: ( TO2::<PrimaryKeyField> ) + 1
                      and make sure that "Do not evaluate if all referneced fields are empty" is unchecked
                      That's it, you're good to go



                      • 8. Re: RANK function



                        Yes, your assumption is right. And thanks.

                        I had started to play with a similar approach but then haven't gotten back to it.


                        I think it should do nicely, once I get it set up properly.



                        • 9. Re: RANK function

                          tock, tock - is this on?

                          • 10. Re: RANK function



                            Thanks for the bit of dry humor. It cracked me up. Really. (It's been a long week already.)   :)


                            Sorry for not having acknowledged your answer. But you see, you used the phrase "self-join", which tends to cause immediate cerebral shutdown here. I never yet have been able to grasp that particular concept comfortably for some reason.


                            Your response said WHAT to do (very helpful, yes). Tom's said HOW to do it. So yours flew a couple inches above my flattop.


                            But, I will go back and flag your response as the solution. Sorry for being such a DS. And thank you, as always.


                            • 11. Re: RANK function



                              I totally apologise - I somehow managed to miss your post (onset of senility?)





                              • 12. Re: RANK function

                                It's OK - let's relax.

                                • 13. Re: RANK function


                                  Sorry my english isnt so good. I'm from switzerland.

                                  I need also a ranking function. I can sort the datas and numbering them, but my problem is

                                  to set for the equal points the equal rank.

                                  Has somebody an idea?

                                  Thanks a lot an sunny greats from switzerland!