Building a "dynamic" Priority Field

[Hoib]

Here's a simple to do list table with the usual items like "_pk_Task_ID" "Task Description" as a text field, due date, start date, Priority, etc.  I have 50 separate tasks in the table.  I wish to have 10 of them prioritized from 1 (most important to get done) to 10 least important.  That means there are 40 tasks without a priority assigned to them.  And I've struggled to figure out exactly how to do this - focusing primarily on this being a calculation result in the priority field on a layout.  (Hope I said that right...)  It could be that today task_ID 31 is denoted as priority 1, task_ID_22 is denoted as priority 2 etc.  I may decide later that task_ID_31 gets shifted down to priority 9 so in that case, task_ID_22 gets shifted to priority 1 and all the other priorities 3 through 10 get a similar shift up.  My idea was to then go to task_ID_31 and insert a "9" in it's priority which would then push the existing back up to 10.  And then of course, if I complete task_ID_22 and move it to the completed tasks, the priority 1 is removed/blanked and everything shifts up one priority.  I then would manually go into the un-prioritized tasks, grab one and assign it a priority.

 

This didn't look that complicated at the start.  Famous last words....  I've tried messing with a separate table for "Priorities" and attempted a really messy "Case" statement structure in the field calculation to attempt to keep this dynamic as possible.  Yuk!  Can you spell:  Total failure???

 

So I'm asking for some general guidance here namely, does this have the characteristics more of a calculated value for the priority field than say creating a separate table and trying to link it back to the Tasks?  What technique would you employ to re-prioritize these tasks as I shift them around and/or complete them?

 

Thanks - this is such a great place to learn...

[Fitch]

Don't over-engineer it. Make a triggered script that loops through the tasks and re-numbers them.

[Hoib]

So, I'm guilty again of over-engineering...  Mea Culpa...

 

One more quick question:  I'll need to set up an indicator field like "priority_OnOff" or "priority_YesNo" to help FM distinguish tasks which are to be prioritized vs those which are not to be prioritized (at the moment).  In other words, only prioritize with the triggered script those I have designated to be prioritized.  Right?

 

And this triggered script is set to run each time I "touch" the task_ID field.  Have I got that right?

[Fitch]

I don't know. Depends on your workflow, I suppose. Seems like you could just use one field, and set new tasks to 999 or whatever.

 

Each time you "touch" yes -- I generally use onObjectValidate.