Thank you, intrepid Panel Stumpers.
I actually lost sleep over this, and it's driving me insane. I apologize for the long post, but please help if you can!
Scenario:
My project list users want to see an easy, at-a-glance indicator (colored dot) on the project list when their project is getting close to the due date.
They call the "close to the due date" period the "critical" period, and its length depends on the length of the project period. If it's a standard project, the critical period is 3 days long; for rush projects it's all considered "critical."
The length of the project period is represented in a calculated column called "Target Days" that subtracts due date from start date to display the number of days allotted for the project. For the 2 project types- RUSH and STANDARD- the Project type is based off the Target Days value as follows:
If Target days is less than 4, Type = RUSH ; Else Type = STANDARD.
The Type value is achieved via calculated column, which looks like this:
=IF([Target days]>3,"Standard","Rush")
Other calculated columns have already produced other values based off the due date:
Critical1 = due date -1 day (the date of the day before the project is due)
Critical3 = due date -3 (the date of the day three days before the due date)
The goal of all this is:
A colored indicator shows green, yellow or red depending on whether the project is in the "critical period." The indicator is driven by a column called "Timing," a choice column whose values are:
(1) Late
(2) Critical
(3) On Time
My problems:
1) Allowing for changes to the due date.
Initally, I set up a workflow to run on creation that set the Target Days and related Project Type, and used a series of "Pause" actions that changed the Timing value accordingly to maintain the color of the Indicator in real time.
For example, on creation, if Project type = Standard, the Timing value = On time (Indicator clor = green). Due Date = July 14, therefore Critical3 = July 11th.
Action: Pause until Critical3; Action: Set Field in Current Item = change the "Timing"value to "Critical." The Indicator color will change accordingly to a yellow dot.
Action: Pause until Due date. If the status not equals Completed: Action: Set Field in Current Item to change Timing value to "Late." Indicator color changes to a red dot.
(The process is the same if the Project type = Rush, except the workflow first pauses til Critical1, then pauses til Due Date, then checks status.)
This works fine EXCEPT- if, at any time, a user changes the Due date- it all breaks.
I can't prohibit them from changing due dates, since that happens a lot if the project scope changes. The calculated columns will all reset off the new due date, BUT the running workflow will not update itself. It will continue pausing according to the original set of dates. So the users are not seeing updated Indicator colors.
I set up an "On Change" workflow to evaluate whether the due date had changed and to re-initiate the pausing action, but it simply ran parallel to the original workflow and resulted in 2 sets of actions and much confusion.
I though perhaps if the Timing column's value could somehow be calculated by comparing the current date to the Critical and/or due dates.... that might take care of it. But I could not make that work, despite my prayers to Dessie's altar.
If any of this has made sense to you- I am amazed. If you have a better way in mind for me to achieve this process, PLEASE let me know. I'm going nuts.
