1 Reply Latest reply on Oct 25, 2014 4:39 PM by FentonJones






      Good Morning...  I need to be able to "rank" records.  But I need these to be self adjusting.  SO if I have a series of records that are each ranked 1, 2, 3, 4, 5.  And then I create a new Record that needs to be ranked "3", then all then the Records formerly ranked 3, 4, 5, auto adjust to 4, 5, 6.  Similarly, if I decide that the Record ranked 1, now should be ranked 5, then everything adjusts, so that 2 becomes 1, etc.  Is that possible?  Any help?  

        • 1. Re: Rank?

          It's been a while, and no one is answering this, so I'll give it a try. I could not find that I had a database doing this (exactly), and it turned out to be a bit trickier that I thought.

          I did it in a script, running on the current records. It could also run on records (rows) within a portal; but you'd have to go to those records, in a layout there native table, before continuing, as it is going thru the records, via a Loop, in order to set them. It will only do so on ones that need changing.

          It runs on all records which need the change, which are "between" the "Min" and "Max" of change. It therefore minimizes the found set to only those. In order to save and return to the found set you had, it can open a new window, run the changes, then close it. OR, you can run a method I often use, which is to "save the found set", run the routine, then "restore the found set" (to what it was). 

          There are two files in this download. The first is a .fp7 file, using the "new window" method. The second uses the "save/restore the found set" method, and is .fmp12 (as I had a newer database using that). [ Let me know if you run .fp7, and want to use "save/restore found set", as I could add it to such.]

          The "rank change" script is rather longer than you'd guess. It has to know where to "start from" and where to "stop", as well as whether they are to use "-1" or "1" for the changes.

          Anyway, these are how I did it. There is quite likely a simpler way to do it; please show your's if you got one :-]


          P.S. This is what the relationship using a table to "save the round set". It is basically the SAME table, but is NOT connected on the graph. This allows you to use a special ability of FileMaker. If you tell it go "go to current table" in a Go To Related Record (in other words, go to itself), but tell it to "end up" on an unrelated layout, FileMaker will pass the found set to it. This was true for years. It may seem like a lot of trouble (not much really), but it is very fast, instant with thousands of records. This is what it looks like: