8 Replies Latest reply on Dec 5, 2014 10:56 AM by philmodjunk

    Self-Adjusting Rank?



      Self-Adjusting Rank?


      Is there anyway to create "self-adjusting ranking" for records?  For instance:








      uniqueID = 100  "Orange"             Rank = 2

      uniqueID = 101  "Apple"                Rank = 1

      uniqueID = 102  "Pear"                  Rank = 3

      So if one were to change:  uniqueID = 100  "Orange"             Rank = 1

      Then, automatically

      uniqueID = 100  "Orange"             Rank = 1

      uniqueID = 101  "Apple"                Rank = 2

      uniqueID = 102  "Pear"                  Rank = 3


      If one were then to add a new record:  uniqueID = 103  "Pineapple"             Rank = 2


      uniqueID = 100  "Orange"             Rank = 1

      uniqueID = 101  "Apple"                Rank = 3

      uniqueID = 102  "Pear"                 Rank = 4

      unique=ID = 103 "Pineapple"       Rank = 2

      Also, if one were to delete a record, such as "Pineapple" from above, then:

      uniqueID = 100  "Orange"             Rank = 1

      uniqueID = 101  "Apple"                Rank = 2

      uniqueID = 102  "Pear"                 Rank = 3

      If possible, I would prefer this to accomplished with a calculation field rather than a script or a sorting function.

        • 1. Re: Self-Adjusting Rank?

          "Rank" implies sorting your records in ascending or descending order on a particular field. When I examine your examples, I cannot deduce what value in a field is being used for that sort to determine the Rank value. I thought you might be sorting on the value of Rank itself but then your examples contradict this in your last two examples:

          So what field is determining the order that you then want to show as a "Rank" in the Rank field?


          • 2. Re: Self-Adjusting Rank?

            The "Rank" field is actually the field that would be sorting.  BUT what I'm getting at is that if I go in and CHANGE the value in any of the "Rank" fields, then Filemaker automatically adjusts all of the other values in the "Rank" fields to maintain the proper order.

            • 3. Re: Self-Adjusting Rank?

              What else would impact a ranking?  Adding or deleting records (and thereby altering the ranking in both directions...)  In those cases as well, FILEMAKER would automatically recalibrate the rank by actually changing the numerical numbers in each of the "Rank" fields.

              • 4. Re: Self-Adjusting Rank?

                I repeat my question. What value in your example determines the rank?


                Name     Score        Rank
                Fred        300             1
                George    295             2
                Harry       295             2
                Andrew    200             3

                Note that George and Harry have the same Rank due to having tied for the same score.

                What I do not see is that "score" field in your example.

                I considered the possibility that you are just manually assigning the rank as a value entered by the user, but your examples do not seem consistent with that possible interpretation of your original post.

                • 5. Re: Self-Adjusting Rank?

                  Yes.  I would be manually changing the rank.  So in your example above, forget any "Score" field.  


                  Name     Rank

                  Fred        1

                  George   2

                  Harry      3  

                  Now if I were to add a new record, Andrew, and manually enter his Rank as "2"

                  Then I want the other Ranks to "auto adjust" so that   

                  Fred        1

                  Andrew   2

                  George     3  

                  Harry 4

                  • 6. Re: Self-Adjusting Rank?

                    But why does Andrew come before George and not after?

                    Do you want the newly added record to always come before an existing record?

                    The simple solution (which ignores such details) is to sort your records on a number field such as your current "rank" field, but then display the record number for each record instead of the value of this field. The Insert Menu can insert a layout symbol for the record number. This can also work for records displayed in a portal.

                    And there is also a way to set up a running total summary field (Set to "count" a non empty field in your table) to provide the same values if you need an actual field with this value.

                    The more complex and slower approach is to sort your records after any such change (editing a record, deleting a record or adding a record) and then use Replace Field Contents with the  serial number option to renumber the field after making such a change.

                    • 7. Re: Self-Adjusting Rank?

                      Think of it this way.


                      If these are chapters in a book, and I have three chapters....  1, 2, & 3.

                      If I want to add a new chapter, and I want it to be "Chapter 2" then how do I get chapter formerly labeled "2" and all the ensuing ones, so  have their chapter numbers change by +1 each.  

                      Maybe I'm missing something here!  Similarly if I have those 4 chapters, and I decide to delete a chapter, how do the numbers recalibrate for the correct chapter numbers?


                      • 8. Re: Self-Adjusting Rank?

                        I have listed three ways to do that. I just needed to know what the "rule" was when you specify a "rank" value that exactly matches another record in your table. That still is not clear. If I were to specify that I have a new "chapter 2", I'd give it a rank value of 1.5 and then sort and use the replace field contents method to renumber everything as integers. That will make my "1.5" record a 2 and the original 2 will become 3...