Opp Record Type of "Renewal" (with different stages than a "New Business" record type, of course).
Each stage has a recommended time "window". Ex - "Opportunity Documentation" should be from 18 to 24 months before contract expiration/close date, "Account Evaluation" should be 12 to 18 months before, etc...
We need a way to identify (and report on) opportunities where the actual stage is behind the recommended stage "window". Ex - As of today, this Opportunity is still listed in the "Opportunity Documentation" stage, but it is now 17 months before the expiration date, so it ought to be in the "Account Evaluation" stage.
BUT if someone is ahead of schedule, I don't want them showing up as being not in compliance. For ex, the opp is in the "Negotiation" stage at 10 months before expiration, when the "Negotiation" stage recommended window doesn't start until 9 months before. If I use a filter based on the Recommended Stage <> Actual Stage, this one would show up as non-compliant, but they're actually ahead of schedule, which is a good thing.
Unless there's some other workaround, I have to translate those stages into numbers, so that I can compare whether one is higher or lower than the other. And once I do that, I keep running into the 5000 compiled character limit because there are 7 stages, and I'm having to write IF statements to calculate the recommended stage. By the time I've done that, I can't refer to that field in any other formula without dumping the formula over the edge, especially when you consider that I'm having to compare the value from the recommended stage to another value in another formula field that calculates the stage order of the actual stage. UGH.
I don't like the idea of using workflow field updates to set the value of a "stage number" field because it doesn't update existing records - only those that get edited. I know I could get around this with a manual update of all existing records immediately before turning the worklow field update on, but I'd really rather not go that route.
Can anyone think of another way to handle this that doesn't bump against that 5,000 compiled character ceiling, but still gets management what they need? (A report that lists all the Renewal opportunities that have passed their recommended stage window.)
Thanks for any advice!
4 respuestas
It might be a little bit too simple for what you're looking for, but what about creating a Formula(Number,0) field that uses a CASE function to evaluate the Opportunity Stage and return a number that represents that Max Recommended Days to Close (or something like that).
Then create a Formula field that evaluates the Close Date - TODAY() and compares that number to the "Max Recommended Days to Close" and returns a value: Lapsed/Overdue, OK, Ahead of Schedule, etc.
