Alert owner of account of sudden drop in numbers

Question

My setup:

Every month I have a .NET app that uploads data into a custom object (ChurchHist__c) that keeps track of each church's contributions for that month. There are little over 2,200 records inserted per month.

What I need to do:

Once these records are inserted into the object I need to check the new "ContribForMonth" data against last month's data. If there is a drop of a certain percentage, email a customer service rep so s/he can contact the church and try to help with any issues.

What I'm thinking:

Create a trigger (before insert) that gets last month's data and compare it to the new data coming in. So:

1.) Store last month data in a Map list. 2.) Loop through the new data in Trigger.new and find corresponding data in the map. 3.) Compare the two values. 4.) Email account owner

Does this seem like a good way to do this or is there a simpler way? I'm still learning my way around SF so any advice would be helpful.

Answer 1

How about using Analytic Snapshot functionality of Salesforce? This is what it is supposed to be used for - to identify patterns at regular intervals.

https://help.salesforce.com/HTViewHelpDoc?id=data_setting_up_analytic_snap.htm&language=en_US

Based on the snapshot data you could schedule reports/dashboards and mail it to your users.

Answer 2

The easiest way to aggregate data like this would be in a roll up summary field

You can do this in your account object as a new custom field, and roll up the totals of the ChurchHist__c "values" (whatever field the contribution goes into)

Use the filter fields to just grab the records you want. I was just typing up a cool way to do this using a formula field containing something along the lines of

MONTH(DATEVALUE(createdDate)) == MONTH(TODAY() - 1)

In your ChurchHist__c object, so it would be true when it was created in the last month, otherwise false. (you could then use a similar field to do this month). It seems you probably have a reasonable idea how you will catch the relevant records though? (if not, that should work).

With two roll up summaries on account for the last two months totals, you could then look to a bit of workflow to send the e-mail? You would want to find a way to update account when the loading had been completed (simple enough) and then on an Account Workflow rule, set up a condition like this:

And then have a Workflow immediate action for an Email alert, chuck in a template, and it should all work for you (DON'T forget, activate workflow rules when you have finished them... ultimate gotcha!)