Hi Everyone. I have a challenge for those math-and-code oriented minds out there....I work in an R&D department that handles many projects (over 400 active projects at once, over 1200 total) and so I have created a database to keep track of the progress. One thing that is painfully apparent is the need for an easy to use priority list.
The way I have it setup now is quite simple. Each project has a unique ID# (autonumber) with is the primary key, and then the same table stores its status (Active, Cancelled, On Hold, Finished). The product manager also has a field in this table (Mike, for example)
What I want to do is the following: Add a column where the priority level of the project can be entered. Priority will be indicated as follows: 1 = highest, infinity = lowest. No number can be repeated, so the priority column will look like 1,2,3,4,5,6,7,8,...n. Priority numbers would be added for all Active projects when the column is added, however they should be updateable afterwards.
The key is to make it easy to update and to do that, I want to be able to select a project, enter a new priority number in the form for it (say I pick the project that had a priority of 168 and change it to 3) and then I need access to recompute all priorities for all projects from 3 down; for example, the old priority 3 turns into 4, old 4 turns into 5, and so on until it reaches 168.
Of course, when a project is finished or put on hold, its priority number should be deleted and all projects under it should have their priority "upped" by one.
Also, each product manager gets his own list, so in fact some numbers in the priority list will be repeated, but only because they belong to different product managers.
How is this for a challenge!
Does anyone have any idea how this can be done?
Thanks a million
mafhobb
The way I have it setup now is quite simple. Each project has a unique ID# (autonumber) with is the primary key, and then the same table stores its status (Active, Cancelled, On Hold, Finished). The product manager also has a field in this table (Mike, for example)
What I want to do is the following: Add a column where the priority level of the project can be entered. Priority will be indicated as follows: 1 = highest, infinity = lowest. No number can be repeated, so the priority column will look like 1,2,3,4,5,6,7,8,...n. Priority numbers would be added for all Active projects when the column is added, however they should be updateable afterwards.
The key is to make it easy to update and to do that, I want to be able to select a project, enter a new priority number in the form for it (say I pick the project that had a priority of 168 and change it to 3) and then I need access to recompute all priorities for all projects from 3 down; for example, the old priority 3 turns into 4, old 4 turns into 5, and so on until it reaches 168.
Of course, when a project is finished or put on hold, its priority number should be deleted and all projects under it should have their priority "upped" by one.
Also, each product manager gets his own list, so in fact some numbers in the priority list will be repeated, but only because they belong to different product managers.
How is this for a challenge!
Does anyone have any idea how this can be done?
Thanks a million
mafhobb
