Trigger and Workflow on the same field facing Issues

Question

Hi I have a requirement. I have a custom Checkbox field inside case object say "res__c". When a Email is received then this res__c should be TRUE. And later if the case record is edited, then res__c should be false. So, i have written a after insert trigger on EmailMessage. Here i have mapped EmailMessage.ParentID and Boolean value.. Once an email is received, that is,

for(EmailMessage Email : Trigger.New) {
IF(Email.Incoming == TRUE)
 res__c = true;
// added email.parentID and res__c to the map
} 

Since the trigger is running on EmailMessage object, i cannot write a before update trigger on Case object. So i wrote a Workflow.. IN this workflow, action is ISCHANGED(LastModifiedDate)and field update that res__c = false. Now, what am facing is that when a new email arrives, both Trigger and Workflow performs at the same time and the final value on the res__c is becoming false. I checked the trigger and it gives 100% code coverage and Update happens on the res__c when a mail is received and it returns true. But at the same ISCHANGED() function is also running on the INSERT of record itself. I think that ISCHANGED() function runs on insert because the Lastmodified Date value for a record is null and its being changed to a value of NOW() on the creation of record.

So please provide me an idea of how to resolve this,

• should i try something like Datetimevalue(LastmodifiedDate)-Datetimevalue(CreatedDate)<>0 for workflow action (or)
• Should i write a trigger on case object to uncheck the res__c value on any update for the record (or)
• will i be able to check for change in case record value on the after insert trigger of Emailmessage itself.

Answer 1

Have you tried using the IsNew() function in your workflow criteria? Try this, I've added a check to ensure that CreatedDate and LastModifiedDate are different, and a check for the IsNew flag.

And (
    CreatedDate <> LastModifiedDate,
    Not(IsNew()),
    IsChanged(Res__c),
    Res__c = true
)

You could also update your Email trigger so that it only update Case records when Res__c is equal to false. It won't solve your problem but there's no need to update Case records when the Res__c field is already set to true.

You could write a Case trigger to gain a finer level of control over what's happening, but I'd do that as a last resort.

Edit: there's no reason we can't check for a true value in the formula too.

Edit 2:

Ok since you're having trouble with this approach, let's try something different.

1. Res__c - change this to a Formula(Boolean) field.
2. LastEmailDate__c - this will be a DateTime field.

Now, in your trigger simply update Case.LastEmailDate__c whenever a new Email is received for that Case.

For Res__c, have a formula something like:

If (
    LastEmailDate__c < LastModifiedDate,
    FALSE,
    TRUE
)

So assuming that Case.LastEmailDate__c is always being correctly populated by your trigger, Case.Res__c will always be false if the Case has a LastModifiedDate greater than the LastEmailDate__c i.e. if the record has been edited after the last Email was received.

If you want you can change the return type of Res__c to make it show text value, RAG images etc.

An added bonus is that no workflow is required!