In my post titled “Cleaning the data that matters – and not all data matters” I finished the post up with the following:

“PS – For bonus points, create a nice email alert telling the reps their data is bad, and make it so it sends them that notice every time they edit the account OR opportunity…just put on a timer so it only sends once per day!”

To which, JaneIsaac replied:

“nice detective work. Could you share What the timer formula look like?”

Well JaneIsaac, this post if for you!

The scenariois that we wanted to send out alerts if an account scored low data grade points and that account OR an opportunity related to that account was updated. After some quick research I saw that the updates were clustered, often receiving multiple updates in a short period of time. I didn’t want the alerts constantly kicking out.

So I built out a function so that prevent multiple alerts from being sent out in a given set of time. My functionality treats account and opportunity updates as two different actions, so I broke them out on this blog as such. Listing out the ingredients below and I will dissect the basic functionality after.

For the Account alerts:

1 – Date field on Account “Data Alert Sent Date”

1 – Workflow rule for the Account Object “Data Grade Alert”:

Evaluation Criteria = “Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria”

Rule Criteria =

AND(
AND(Account_Data_Grade__c <>”Acceptable”,Account_Data_Grade__c <>”Excellent”),
Open_Pipeline__c < 1,
AND(LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “, LastModifiedBy.ProfileId <>”xxxxxxxxxxxxxxx”),
(DATEVALUE(CreatedDate)<>Today()),
OR(ISBLANK(Data_Alert_Sent_Date__c),(Data_Alert_Sent_Date__c) <> today()))

2 – Immediate Workflow Actions

1 – Workflow email alert = Sendemail to “Last Modified By”

       1 – Field Update = “Data Alert Sent Date” with Today()

 For the Opportunity alerts:

1 – Date field on Opportunity “Data Alert Sent Date”

1 – Workflow rule for the Opportunity Object “Data Grade Alert”:

Evaluation Criteria = “Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria”

Rule Criteria =

AND(
AND(Account.Account_Data_Grade__c <>”Acceptable”,Account.Account_Data_Grade__c <>”Excellent”),
AND(LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “,LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “, LastModifiedBy.ProfileId <>” xxxxxxxxxxxxxxx “),
Record_Type__c =”Open Opportunity”,
(DATEVALUE( Account.CreatedDate )<>Today()),
or(ISBLANK( Data_Alert_Sent_Date__c ), (Data_Alert_Sent_Date__c)<>TODAY()))

2 – Immediate Workflow Actions

1 – Workflow email alert = Send email to “Last Modified By”

          1 – Field Update = “Data Alert Sent Date” with Today()

Taking a look at the mechanics:

The rule criteria’s are similar enough that we won’t have to dissect them both and since the interest is in the timer components, that is what I am going to focus on:

1)      (DATEVALUE( Account.CreatedDate )<>Today()) –  Ignore if the account is newly created

2)      But, the following situations are OK:

1. (ISBLANK( Data_Alert_Sent_Date__c ) – The data alert sent date is Null (Never triggered before)
2. (Data_Alert_Sent_Date__c)<>TODAY() – The data alert sent date does not equal Today()

This last line is what ensures that an alert will only send once per day. If the rule runs, (Data Quality = Poor and “Data Alert Sent Date” <>Today()), then the email alert gets sent out and the “Data Alert Sent Date” gets updated with the current day. If that record was updated ANY OTHER TIME during that day, the rule will not fire. I know I say this all the time, but what I really (Really) like about salesforce is that when it comes down to it, you can do some crazy cool stuff with zero code.

 

Looking at this functionality now, I think a couple neat additions would have been:

1)      A rollup summary on opportunity.Data_Alert_Sent_Date__c (MAX), this way, you could have the account rule also looking at the last time an alert was sent out on ANY opportunity.

2)      A counter field update on the opportunity rule with a corresponding rollup on accounts. This would allow for reporting on ignored updates and thresh holding of the alerts.

But, the fun with Salesforce is the ability to rapidly prototype and tinker, so if I wanted to add in some new stuff, it is easy – peasy – lemon squeezy.

Well, hope you enjoyed this. I certainly had fun taking a look at something that was built out quite some time ago but continues to keep ticking! If there are any special requests, just let me know!