2 Replies Latest reply on Nov 1, 2014 9:34 AM by KellyFisher

    Reorder a priority list



      Reorder a priority list


      I have a number of tasks that are being assigned to our creative team. Everything is grouped as High, Medium, Low.  I want High = A##, Medium = B##, and Low = C##.

      Currently I have formula set to change High to A, etc on one field [PRIORITYLEVEL].  And I have a second field [PRIORITY#] where I am manually importing the number 1, 2, 3, etc. And then a third field [PRIORITY] that is combining both fields to create A1, A2, B1, C1, etc.

      What ideally I would like to do is either

      (a) have the ability that if I change the 1 in PRIORITY# all the other numbers in this column automatically change to the next highest number.  For example if I want 4 to become 1, then 1 will become 2, 2 will become 3, 3 will become 4, 5 will remain 5, and so forth. I don't know how to do this script?  And the only issue I see is that I would want these to be unique (no duplicate A1...) and by the way I have it set up this way the number is not unique because there can be 3 1's (A,B,C). If I can figure out this script, I can work around that.


      (b) would be to have one field that is a pull down (or something) that has a predetermined list A1...A25, B1....B25, C1...C25.  And if I change one to something that has already been selected (ie A4, everything moves to the next spot in the order). If I do this I will just have to manually put in High, Medium, Low and then this priority selection.


      Can anyone help with the most efficient solution? (a) is probably more more realistic.

        • 1. Re: Reorder a priority list

          Is the actual value of the field important or just the order once you sort by priority?

          To get the order, your script need only compute a value that puts the current record in the correct place when the records are sorted. Getting the numbers to look perfect has no effect on that purpose and requires that your script renumber the entire set of numbers. That can be done fairly easily, but the script can take some time to do it.

          Say your records are numbered 1, 2, 3, 4... and you want the record currently numbered 3 to be placed between records 1 and 2. Your script can assign the value (1 + 2 ) / 2 or 1.5 to the record in the 3 position and then sorting on these new values will pop it up between items 1 and 2 in the original order. Once you have sorted your records by the new values, a looping script or Replace field contents could renumber the records using only whole numbers.

          b) isn't all that different. After selecting a value, a script can renumber all the records in that category if needed.

          • 2. Re: Reorder a priority list

            What is seen is probably more important.  We will be showing this report to different groups of people and they will see here.
            After discussing with my boss, I made some variations.

            I have now 3 levels (Complex, Medium, and Simple). Within complex all numbers are sorted (A1...A99), in medium (B1...B99), simple (C1...C99).  I have this (A1...C99) as a field called priorder. Within the field there value list call priorderlist.  These are the numbers A1...A99.  Currently I have manually create this order of projects (1 as high prioritiy, 99 as lowest - within each level).

            What I would like to do if I change (by selecting from the drop down menu) something from (as an example) from A15 to A2, then A2 will become A3 and so forth up to A15. Then A16...C99 won't change. Manually what is happening is that the layout is sorted by levels and then by priorder.  If I change a priority of of a record, it moves up or down sequentially. Then I click on the matching field (in the next order and have to change this to the next sport on the list, and so forth until they are all reordered and are all unique. Is there a better way to do this with a script automatically?

            I created a screen grab of an an example of how this is set up. Maybe it will help? This show 14 items, but we really have 64 open projects right now.

            (BTW - I would like to have the open projects be unique, but if they are closed I am deleting the PriOrder so it's blank (and when there are entered into the system they are blank).  If I do that, I don't think I can validate by unique value, correct?