+ Start a Discussion
econ242econ242 

Workflow rule help with ISCHANGED

Hello,

Looking for some assistance with a workflow rule...

We don't utilize Leads in the traditional sense within Salesforce...all "Leads" are entered as Accounts and given a "Status" of "Lead". Once a Lead is qualified, the Status changes to "Qualified Prospect", then once an order is placed, the Account Status is changed to Active. What we want (need) to do is capture the date the Account Status is changed from Lead to Qualified Prospect, then again when the Account Status is changed from Qualified Prospect to Active. We also want (need) to capture the number of days an Account remains at said Status.

So...I've created 2 custom date fields and 2 custom age fields to capture the changes from both Lead to Qualified Prospect, and then again when changed from Qualified Prospect to Active seperately...now I want to utilize these fields with a workflow rule that will basically capture the date the Status was changed from Lead to Qualified Prospect, which would then in turn kick off the age field and display the number of days at that status.

I'm stuck on the formula piece for the Status change...i utilized parts of the info contained in the link below:

https://success.salesforce.com/answers?id=90630000000gxreAAA

Is it possible through a workflow rule to capture those dates and time at each Status individually? Or would a trigger be better suited?

Any help would be appreciated...thanks everyone!!!
Best Answer chosen by econ242
kaustav goswamikaustav goswami
You can try something like this:

Create a workflow rule and set the evaluation criteria to everytime it is created and edited.

Then entry criteria will be a formule - something like this

AND(
    NOT(ISNEW()),
    ISCHANGED(Status__c),
    ISPICKVAL(PRIORVALUE(Status__c),"Lead"),
    ISPICKVAL(Status__c, "Qualified Prospect")
)

Now add an immediate workflow action - a field update
and update the first date with today's date - that is TODAY().

As for the duration - A formula field can be used to calculate the difference in the number of days between the created date and the date populated by the above field update.

Iplement something similar for the next set of value changes.

Let us know if this works.
Thanks,
Kaustav

All Answers

CodenameDMCodenameDM
Yes, this should be totally doable via workflows (to set the date fields) and formula fields (to calculate the age between statuses).  For the workflows, you don't necessarily need to use ISCHANGED.  Instead, you could look at the value for the date field in question and only update the value if it's not null and the status is applicable.  This will have the net result of only updating the value when the status is changed.  Note that this does not support changing that status again at a later date (e.g. from Qualified Prospect back to Lead back to Qualified Prospect), I don't know if this is a requirement.  With this in mind, your workflow trigger condition formula could be:
Qualified_Date__c == NULL && Status == ”Qualified Prospect”

For the formula fields, consider all conditions.  For example, if you're measuring the time in the Qualified Prospect status for a record still in the Qualified Prospect status, you'd need to measure the amount of time between today and the day it was moved to Qualified.  But if you're measuring the time in Qualified Prospect for a record that's already been moved to Active, you'd need to calculate the time between your two date fields.  To do this in a single formula, you can use nested IF statements (always fun), like so:
If(Qualified_Date__c != Null && Age_in_Active_Status__c == Null,
IF(Qualified_Date__c != Null && Age_in_Active_Status__c != Null,
Active_Date__c – Qualified_Date__c,
Today() – Qualified_Date__c),
Null)

Note that this is pseudocode and may not work as a formula out of the box.  Hope this helps!
kaustav goswamikaustav goswami
You can try something like this:

Create a workflow rule and set the evaluation criteria to everytime it is created and edited.

Then entry criteria will be a formule - something like this

AND(
    NOT(ISNEW()),
    ISCHANGED(Status__c),
    ISPICKVAL(PRIORVALUE(Status__c),"Lead"),
    ISPICKVAL(Status__c, "Qualified Prospect")
)

Now add an immediate workflow action - a field update
and update the first date with today's date - that is TODAY().

As for the duration - A formula field can be used to calculate the difference in the number of days between the created date and the date populated by the above field update.

Iplement something similar for the next set of value changes.

Let us know if this works.
Thanks,
Kaustav
This was selected as the best answer
SteveMoSteveMo
Why don't you just use a Criteria Based WFR like this:

Rule Criteria	
Account: Status  EQUALS  Qualified Prospect
Evaluation Criteria	Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria


kaustav goswamikaustav goswami
Yes that can be done too.Just one point that we will have to ensure is the status field of the account is always unidirectional. That is values always get set in one direction. If not then say the Account is 'Active' and then someone downgrades the account to 'Qualified Prospect' - the workflow will fire and will over write the value of the date field. In order to avoid that from happening I had suggested the prior value checking. However, if that is not the scenario then this will work too.

Thanks,
Kaustav
SteveMoSteveMo
You could prevent that with a Validation Rule that evalues the current value and compare it to the previous value selected.
econ242econ242
You guys rock!!! thanks so much for the responses!!!

I actually went with Kaustav's method....seems to be the best fit for the requirement since yes, we do want them to flow unidirectional.

Testing so far is minimal, but working as needed...I'll see in a few days how it's going...thanks again everyone!!!

:)

E