1

Given the Lead Statuses I have, and in the order they appear, I need to be able to accomplish two things with a validation rule:

1 In general no lead status can change backwards with the exception of

2 The “Unqualified” and “Re-Market” statuses should be able to be change back to the “MQL – Follow Up” status and only that status

The below validation rule accomplishes #1 fine in that in general backwards movement of lead status gets trapped by the validation rule, however with #2 All Prior Values are allowed to be reset back to “MQL - Follow Up” instead of just the “Unqualified” and “Re-Market” values.

Just to clarify with an example: the “Customer” status should never be able to move backwards but with this validation in place it can erroneously be reset to “MQL – Follow Up”.

Can anyone spot what needs to change in the validation rule below to achieve the above described?


CASE( Status ,
"Open",1,
"New",2,
"MQL - Follow Up",3,
"In Progress",4,
"In Progress - Further Activity",5,
"Re-Market",6,
"Unqualified",7,
"Non-Prospect",8,
"Wrong Contact",9,
"No Longer w/Co",10,
"Remove",11,
"Qualified",12,
"Customer",13,
"Approved",14,0)
<
CASE(PRIORVALUE(Status) ,
"Open",1,
"New",2,
"MQL - Follow Up",3,
"In Progress",4,
"In Progress - Further Activity",5,
"Re-Market",6,
"Unqualified",7,
"Non-Prospect",8,
"Wrong Contact",9,
"No Longer w/Co",10,
"Remove",11,
"Qualified",12,
"Customer",13,
"Approved",14,0) && (TEXT(Status) != 'MQL - Follow Up' && (PRIORVALUE(Status) != 'Re-Market' || PRIORVALUE(Status) != 'Unqualified'))
0

give this a go and see if it helps ..

all it says is first check if the status is changed from Re-Market or Unqualified to MQL-Follow Up, if yes skip the validation, otherwise go thru your validation

IF( 
  AND(
    TEXT(Status) = 'MQL - Follow Up',  
    OR(TEXT(PRIORVALUE(Status)) = 'Re-Market',TEXT(PRIORVALUE(Status)) = 'Unqualified') 
  ),
  FALSE,
  CASE( Status ,
  "Open",1,
  "New",2,
  "MQL - Follow Up",3,
  "In Progress",4,
  "In Progress - Further Activity",5,
  "Re-Market",6,
  "Unqualified",7,
  "Non-Prospect",8,
  "Wrong Contact",9,
  "No Longer w/Co",10,
  "Remove",11,
  "Qualified",12,
  "Customer",13,
  "Approved",14,0)
  <
  CASE(PRIORVALUE(Status) ,
  "Open",1,
  "New",2,
  "MQL - Follow Up",3,
  "In Progress",4,
  "In Progress - Further Activity",5,
  "Re-Market",6,
  "Unqualified",7,
  "Non-Prospect",8,
  "Wrong Contact",9,
  "No Longer w/Co",10,
  "Remove",11,
  "Qualified",12,
  "Customer",13,
  "Approved",14,0)
)
  • Thanks, this did the trick but then our marketing folks decided not to put the validation rule in to play! – megachuck Mar 24 '15 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.