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

    RANK function

    DLW-BPEX

      Title

      RANK function

      Post

      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,

      David

       

      FMP 10 Advanced, Mac OS X 10.6

        • 1. Re: RANK function
          philmodjunk

          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
            comment_1

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

            • 3. Re: RANK function
              DLW-BPEX

              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.

              David

              • 4. Re: RANK function
                comment_1

                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
                  user14360

                  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
                    braxton

                    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
                      TomElliott

                       

                      David
                      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
                      cheers
                      Tom



                       

                       

                      • 8. Re: RANK function
                        DLW-BPEX

                        Tom,

                         

                        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.

                         

                        David

                        • 9. Re: RANK function
                          comment_1

                          tock, tock - is this on?

                          • 10. Re: RANK function
                            DLW-BPEX

                            comment,

                             

                            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.

                            David

                            • 11. Re: RANK function
                              TomElliott

                              comment

                               

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

                               

                              cheers

                               

                              Tom

                              • 12. Re: RANK function
                                comment_1

                                It's OK - let's relax.

                                • 13. Re: RANK function
                                  MK

                                  Hi

                                  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!

                                  Michael