Ask Search:
Hillary BarterHillary Barter 

Workflow Rule - Formula based on Picklist Item AND Date

I am hoping to create a workflow rule ( contact record ) that runs based on two fields: picklist and date/time field. I want it to be an AND function, in the sense that both fields need to fit in order for the workflow (task creation) to run. How it should work (ideally) is that if a contact is defined as an A(picklist), and they're last activity date is more than 14 days in the past (date/time field) a task should be created. 

I am having trouble with the syntax and keep getting various errors: 

Cultivation_Code__c = A
AND
TODAY()- (Last_Activity_Date__c) > 14
Best Answer chosen by Hillary Barter
Steve MolisSteve Molis
Basically what this will do
AND (
TEXT(Cultivation_Code__c) = "A", 
NOT(ISBLANK(Last_Activity_Date__c))
)
is "start the clock" when the User creates or edits a record and saves it that meets the Criteria

if and when the clock strikes 14 days after  Last_Activity_Date__c the Scheduled Action will happen. 

If anytime before that the user edits the record and changes to Cultivation_Code__c to a value othe rthan  "A" the Scheduled Action is cancelled. 

Is a new Activity is added and the Last_Activity_Date__c is updated then Scheduled Action clock is reset and starting counting from 0 again
 

,
 

All Answers

Anel MalabananAnel Malabanan
You can try the formula below:
 
AND(ISPICKVAL(Cultivation_Code__c,"A",
((TODAY() - (Last_Activity_Date__c)) > 14)
)

If it's not working let me know the error message.
Hillary BarterHillary Barter
Hi Anel,

Tried that, I get the following error: Error: Incorrect parameter type for operator '-'. Expected Number, Date, received DateTime
Anel MalabananAnel Malabanan
AND
(ISPICKVAL(Cultivation_Code__c,"A"), 
TODAY() - DATEVALUE(Last_Activity_Date__c) > 14)

Let me know if it's working now
Hillary BarterHillary Barter
That was it! Thank you
Steve MolisSteve Molis
I don't think that will work the way you want it to.  

I would use a Trigger Criteria like 
 
AND (
TEXT(Cultivation_Code__c) = "A", 
NOT(ISBLANK(Last_Activity_Date__c))
)
and then add a Time-Dependant Action and schedule it 14 Days after the Last Activity Date
 
Hillary BarterHillary Barter
Hi Steve, can you explain why you think that wont work and yours will? Just trying to understand the difference. 
Steve MolisSteve Molis
Hi Hillary

TODAY() is what's called a Relative Date value, today the TODAY() function will return 08/14/2019, tomorrow the TODAY() function will return 08/15/2019, the day after that the TODAY() function will return 08/16/2019... 

Workflows and Processes only exvaluate the record when it is created/or edited and saved to the database, they will not automagically compare Last_Activity_Date__c to the current date and trigger on their own when the current date is 14 days after the Last_Activity_Date__c
Hillary BarterHillary Barter
Hi Steve,

Ok, so with your proposal, it will run and check everyday, and when the rules conclude to yes, it will create the task I am hoping for? 
Steve MolisSteve Molis
Basically what this will do
AND (
TEXT(Cultivation_Code__c) = "A", 
NOT(ISBLANK(Last_Activity_Date__c))
)
is "start the clock" when the User creates or edits a record and saves it that meets the Criteria

if and when the clock strikes 14 days after  Last_Activity_Date__c the Scheduled Action will happen. 

If anytime before that the user edits the record and changes to Cultivation_Code__c to a value othe rthan  "A" the Scheduled Action is cancelled. 

Is a new Activity is added and the Last_Activity_Date__c is updated then Scheduled Action clock is reset and starting counting from 0 again
 

,
 
This was selected as the best answer