Admin Tools vs Coding: Salesforce Automation and the Mystery of the Unread Emails

07/24/2019 by Mario Di Genio

Sometimes there are issues that defy the rules. You think the system should do one thing, and yet either nothing happens or something unexpected happens. In this post we’re going to analyze a use case with the Salesforce email feature and how to control the emails received, where a lot of unexpected things happened.

Our client uses Salesforce for support and communicates with their clients via emails from Cases, using Email-to-Case. When one of their clients responds to an email, there is no indicator in the service console that there is an unread email.

An initial working solution was built using two Workflow Rules.

The first Workflow Rule sets “Unread Email” checkbox field to true in the Case when an email is received. If the Status of the email is New, then it updates the Unread Email field.

The second sets the “Unread Email” checkbox field to false in the Case when an email is read.

This solution seemed to be working, but then it stopped, and Salesforce Support says it should have never worked.

The Analysis and Research

Why are the Workflow Rules not working?

The two things we need to have happen in our implementation are:

If an email is received then mark it in the Case.
If an email is read then mark it in the Case, unless there’s another unread email.

What is Email-to-Case?

Email-to-Case automatically creates cases and auto-populates case fields when customers send messages to the email addresses you specify. Agents can quickly track and resolve their issues more efficiently by having all their email interactions in one place.

When an email is received in a predefined email address, Salesforce automatically turns it into a case and stores the email in the Activities related list. If the email is a response to a previous notification, it is stored in the related list of the case that it is in reference to.

A user can go into the case and read the emails, which Salesforce automatically proceeds to mark as read. Salesforce allows us to build Workflow Rules on email messages as on any other object, defining conditions on the status of the email (received, sent, read). So building a roll-up of the number of unread emails to the case should be pretty straightforward.

Why is it not implemented already?

Since emails can be read from multiple sources within and connected to Salesforce (Outlook, Gmail, etc.), maybe this is what makes the tracking difficult to roll up to other objects. It could also create an overload for the system to process all the readings for large organizations with a constant flow of emails. But all of these assumptions are based on what we get to see from the outside.

Option One: The Admin Tools Way

So why are the Workflow Rules not working?

The answer is here. Reading an email updates its status to Read but does not count as an update, therefore it does not trigger any Workflow Rule, Process Builder, or Trigger. Lesson learned

That’s why the Workflow Rule that sets “Unread Email” field to true in the Case (when an email is received) works 100% because there’s an insert. But the Workflow Rule that sets “Unread Email” field to false in the Case (when an email is read) works 0% because there’s no update.

Even if both Workflow Rules worked, it would work when there’s only one unread email and that one email is read. If there are more unread emails, then it would not show the correct status, which means for example:

Receive email 1:

Unread status is updated to true per Workflow Rule. There’s 1 unread email.

Receive email 2:

Unread status is updated to true per Workflow Rule. There are 2 unread emails.

User opens email 1:

Unread status is updated to false per Workflow Rule (if it worked), but that’s not accurate since email 2 is still unread. To do this properly it would require a combination of Process Builder and Flow, with flow checking if there are any other Email Messages unread in the same Case before updating the field in the Case (and only if the Process Builder fired with the reading of the message, which as discussed above it does not behave as such).

It may be better to have an “Unread Email Count” field in the Case and a Workflow Rule that adds 1 to it every time an email is received. The checkbox to show if there are unread emails can be defined as a formula from the “Unread Email Count”. Lesson learned

Can it be done with Process Builder or Flow?

Once again, Process Builder automations operate just like Workflow Rules; they only fire if there’s an action on the record, and the reading of an email does not count as such (works 0%). It would only work 100% for the new email, which already works with the Workflow Rule.

Can it be done with DLRS?

DLRS (Declarative Lookup Rollup Summary) is a powerful package that allows you to create roll-up summary fields from Lookups (out-of-the-box summary fields can only be created for Master-Detail relationships). But in the backend DLRS works with Triggers which, just as Workflow Rules, are fired by an actual update, so it would only work when there’s a new email, which is the same way the Workflow Rule works, but not an email read.

DLRS also allows you to do scheduled calculations that run through a Scheduled Job instead of a Trigger. Since this is going to be running on a schedule instead of an action on the record, we gave it a chance:

In order to work, the rollup should count the emails that are unread (Status=”New”), so we added the Relationship Criteria for it. We tried first with the Relationship Field RelatedTo because we thought that was the lookup field to connect the email to the Case.

It didn’t work, and we couldn’t find a cause for it, other than the EmailMessage object has a RelatedTo lookup field that is generic, that can lookup to any other object in the system (same as Activities have):

So DLRS does not seem to be able to transform the related record into the correct type of record we want (Case) and access the Status field properly to update it. It’s able to successfully detect how many emails each Case has, but not to filter by any criteria. We’ll go back to this later, as we make new findings in our quest.

Option Two: The Coding Way

Can it be done with Apex Triggers?

Apex Triggers only execute when there’s an action on the record, which is why the Workflow Rules and DLRS did not work, so we don’t even need to give this a try.

Can it be done with Batch Apex?

Since no action can be detected, we can have a process that will run periodically and check all cases and update their current count of unread emails.

To build the batch apex:

From the Cases, see all their Emails:
- Put the Unread Email Count in the Case to 0.
- Get all the emails that are unread:

Get all the Cases in the system that have unread messages (those are the ones that the Workflow Rule marked as having unread when the email was received). We get only the ones with unread messages to limit the number of records to process.
For each Case:

Because there’s a query to emails for each case, batches cannot be of more than 50 records to be within Salesforce platform limits.

The batch was not working initially, so we retrieved the emails from a Case we knew had unread emails, and it turns out that the actual field the emails use to look up to the Case is ParentId, not RelatedToId, so maybe this is why the DLRS was not working! We fixed this in the batch and will get back to the DLRS to try again later. Lesson learned

The batch was still not working, and it turns out that in the database the Status of an email is saved as a number, not as a String (“0” for New, “1” for Replied, etc.). See the EmailMessage object definition. Lesson learned

This is a major change since for the DLRS we had to specify this condition and we did it as Status=”New”. Since DLRS builds the query directly as such, maybe that’s why DLRS wasn’t working. This is another discovery to retry with DLRS later.

So for queries, the actual condition is Status=”0” for the unread emails. Now we can finally have the batch working, retrieving the emails through the proper field that looks up to the Case (ParentId), and the correct value for the status (0 instead of New).

We tested the following scenarios:

Test Case Scenario 1:

Sent a new email
1 email unread
Emails unread count in the Case = 0
Run batch job
Email unread count is updated to 1 = SUCCESS

Test Case Scenario 2:

2 emails unread
Emails unread count in the Case = 1
Run batch job
Email unread count is updated to 2 = SUCCESS

Test Case Scenario 3:

2 emails unread
Emails unread count in the Case = 2
Updated Case just to have a different LastModifiedDate
Run batch job
Email unread count is not updated = SUCCESS

Test Case Scenario 4:

Sent a new email
3 emails unread (the previous 2 unread and the new one)
Emails unread count in the Case = 2
Run batch job
Email unread count updated to 3 = SUCCESS

We now have the count of unread emails in the Case updated by the batch job in all the scenarios we’ll encounter.

Can it be done with DLRS?

Back to try again with DLRS; let’s give it another chance:

From the coding we learned that the field that relates the emails to the Case is ParentId, not RelatedToId, so we made that change in the DLRS definition. With Relationship Criteria Status=’New’ it does not find any emails unread.

And since in coding the Status=’New’ should be Status=’0’, then we tried again changing the DLRS setting with that.

We tested the following scenarios:

Test Case Scenario 1:

Sent a new email
1 email unread
Emails unread count in the Case = 0
Run rollup scheduled job
Email unread count is updated to 1 = SUCCESS

Test Case Scenario 2:

2 emails unread
Emails unread count in the Case = 4
Run rollup scheduled job
Email unread count is not updated = FAIL

Test Case Scenario 3:

2 emails unread
Emails unread count in the Case = 4
Updated Case just to have a different LastModifiedDate
Run rollup scheduled job
Email unread count is not updated = FAIL

Test Case Scenario 4:

Sent a new email
3 emails unread (the previous 2 unread and the new one)
Emails unread count in the Case = 4
Run rollup scheduled job
Email unread count updated to 3 = SUCCESS

It looks like for the DLRS to update, there has to be a change in the LastModifiedDate of the child records or a new child record must be created, otherwise, nothing is triggered. Since reading an email does not modify the record, the DLRS does not roll up correctly when the emails are read unless a new email comes in and then it does recalculate.

So the solution via a batch apex is the only one that would work for this request.

Takeaways and How to Make it Happen

There are many situations in which discovery is constant. In this case, we learned about scenarios where an apparent update is not registered as such and a picklist value is not stored as it is seen on screen. As we traveled through different scenarios, we also discovered that our original solution (having a checkbox in the Case for the unread emails) is not enough to cover it all and ensure correct functionality.

Even though we tried many different paths, failure is not a complete loss. It is a chance to learn not just about Salesforce, but also to teach your brain to find workarounds. This is when it’s not only what you know, but your ability to think outside the box and come up with alternate solutions that may not be direct, but may work. There are many ways to tackle a problem and there will always be at least one that meets most of your needs, it just may take some time to get to it.