In a previous post I talked about the solution I came up with to find the number of unique years a donor has given (or a customer has made a purchase, depending on your flavor of Salesforce).
You can read about that here: https://capozza.io/finding-donor-giving-streak-unique-years-given-in-salesforce/
I wanted to take a second and really outline each piece of the code I wrote somewhat to commit it to memory and partially to help anyone else trying to learn.
I’ll post the code snippet at the very bottom and then we’ll start at the top, working our way down.
Overall the code does this:
When a record is updated/inserted look at it/them. Use the ContactID from the record to query this object, then calculate a number, then write that number to a different object.
The first lines:
trigger CountUniqueYears20 on OpportunityContactRole (after insert,after update,after undelete)
In real words format this says :Hi, I’m a trigger, my name is CountUniqueYears20 and will be triggered by some event on the OpportunityContactRole (we’ll call it ocr from now on) object. What triggers me is when a record in the OCR is inserted, updated, or undeleted (restored).
Ok – so we’ve established where we live and when we take action. Alright! Moving on.
Set contactIds = new set();
I chose to use a set for this because sets don’t allow for duplicates – whereas Lists do. Here we’re saying: I know I’m going to want to save some pieces of data to a list (or set or some other collection), and we need to have a list to save in/to prior to the event. I think the fancy term in ‘initialization’. Whatevs, it’s letting the program know that it has a place to dump data. The () at the end says it’s empty. We could put a SOQL statement or another collection item in there but we don’t need to. These collection items become important when you’re talking about bulkifying code. Salesforce has a number of limits that you’ll run into if you try doing something like : for (Contact con : trigger.new){SELECT things FROM object and DO something}. This is what SF refers to as bulkifying. Basically thinking of this in batches, not just one record.
for(OpportunityContactRole ocr : Trigger.new){
contactIds.add(ocr.ContactId);
}
There is trigger.old or trigger.new. You would use trigger.old if you wanted to compare the existing data to the new data (mostly good for before update or before undelete). In this case, we’re only interested in the new data. So when the OpportunityContactRole object triggers this – we’re going to add the field ‘contactId’ from the OCR object to the set we ‘initialized’ earlier. Since its a set, we don’t need to worry about duplicates.
Now we have a list of ContactIds and we’re gonna wanna do something with those. What we WANT is that SOQL query that will count the number of unique years a donor or customer has purchased/donated over the last 20 years. So we’ve got a SOQL select statement in the form of an AggregateResult list. And so we end up with:
List<AggregateResult> arList = [SELECT ContactId, COUNT_DISTINCT(Fiscal_Year__c) cnt
FROM OpportunityContactRole
WHERE ContactId In :contactIds
AND ((CloseDate__c = LAST_N_Years:20) OR (CloseDate__c = THIS_YEAR))
AND ((Role = ‘Donor’) OR (Role = ‘Household Member’) OR (Role = ‘Soft Credit’))
GROUP BY ContactId];
You’ll notice that we’re only selecting the ContactId & the AggregateResult field (count distinct) because aggregate results don’t really let you do much more. You can’t do nested SOQL (cross-object) queries in those. Well, maybe YOU can, but I tried. I failed. This is essentially your criteria. We’re saying let’s only select the OCR records in which our ContactId lives (and a few other limiters).
Map resultMap = new Map();
Again, we’re going to need to hold data related to a contact (not an ocr) and a value that we want to write to the contact. We do this just like we did with the contactIds.
if(arList.isEmpty() == FALSE){
for(AggregateResult ar : arList){
resultMap.put((Id)ar.get(‘ContactId’),(Decimal)ar.get(‘cnt’));
}
So – now we want to do something with results of our SOQL select query and new # value for unique years given but only if its not empty. What we want to do is make a map where 1 ID (contactID) is linked to 1 number (the new # of years donated). Then we are going to put the results from our select query into that map. The map key will be the contactID and the map value will be the years given.
List<Contact> yearsToUpdate = new List<Contact>();
List<Contact> ecStatusUpdate = new List<Contact>();
List<Task> tasksToInsert = new List<Task>();Here we’re just creating three new lists that we will need in the future. We’re going to create ‘update’ lists to iterate over with an update or insert command. This is part of the code bulkification thing, rather than doing things one at a time with ‘for’ loops.
Map contactList = new Map([SELECT Id, YOURNAME_Circle_Lapsed_Member__c, YOURNAME_Circle_Lapsed_Year__c, npsp__Deceased__c, Years_Donated_Last_20__c, YOURNAME_Circle_Induction_Year__c
FROM Contact
WHERE Id in :resultMap.keySet()]);
Here we create a new map to link to the Contact sObject. Really, we just need the program to know about the fields on the Contact object that we’re going to want to update. You’ll see we call on our resultMap.keySet (which is just a fancy name for that map id-value pair earlier.
for (Id key : resultMap.keySet()){
Contact conG = new Contact(
Id = key,
Years_Donated_Last_20__c = (Integer)resultMap.get(key)); yearsToUpdate.add(conG);
So – now we start our iterating. For each key in the keyset that we created with the aggregate results, we’re going to do something. That something is to create a new list with that ConG (what I am calling the contact from the aggregate group) where the list will go Id, Value, Id, Value etc. This is the list I’ll use later to update the number of years.
After that it’s mostly programming logic of what the business process was for dealing with that donor circle.
trigger CountUniqueYears20 on OpportunityContactRole (after insert,after update,after undelete){
Set<String> contactIds = new set<String>();
//For each affected role, check the contact Id - add it to the set.
for(OpportunityContactRole ocr : Trigger.new){
contactIds.add(ocr.ContactId);
}
List<AggregateResult> arList = [SELECT ContactId, COUNT_DISTINCT(Fiscal_Year__c) cnt
FROM OpportunityContactRole
WHERE ContactId In :contactIds
AND ((CloseDate__c = LAST_N_Years:20) OR (CloseDate__c = THIS_YEAR))
AND ((Role = 'Donor') OR (Role = 'Household Member') OR (Role = 'Soft Credit'))
GROUP BY ContactId];
//Create a map with the key being the contact ID & The new value is the year cnt from above.
Map<Id, Decimal > resultMap = new Map<Id, Decimal>();
//populate the map with the aggregate results
if(arList.isEmpty() == FALSE){
for(AggregateResult ar : arList){
resultMap.put((Id)ar.get('ContactId'),(Decimal)ar.get('cnt'));
}
//create a list of contacts to update in bulk so we keep the queries low. Then update.
List<Contact> yearsToUpdate = new List<Contact>();
List<Contact> ecStatusUpdate = new List<Contact>();
List<Task> tasksToInsert = new List<Task>();
Map <Id, Contact> contactList = new Map<Id, Contact>([SELECT Id, YOURNAME_Circle_Lapsed_Member__c, YOURNAME_Circle_Lapsed_Year__c, npsp__Deceased__c, Years_Donated_Last_20__c, YOURNAME_Circle_Induction_Year__c
FROM Contact
WHERE Id in :resultMap.keySet()]);
for (Id key : resultMap.keySet()){
Contact conG = new Contact(
Id = key,
Years_Donated_Last_20__c = (Integer)resultMap.get(key));
yearsToUpdate.add(conG);
Contact conS = contactList.get(key);
//this is for building the task & ec status list.
if((conS.Years_Donated_Last_20__c != resultMap.get(key)) && (conS.npsp__Deceased__c == FALSE)){
if((conS.Years_Donated_Last_20__c == 14) && (resultMap.get(key) == 15) && (conS.YOURNAME_Circle_Lapsed_Member__c == FALSE) && (conS.YOURNAME_Circle_Lapsed_Year__c == null)){
//they're a new member! add them to the task list and update induction year.
tasksToInsert.add(
new Task (
WhoID = key,
Subject = 'New YOURNAME Circle Member Induction!',
Status = 'Not Started',
Type = 'Donor Services'));
ecStatusUpdate.add(
New Contact(
Id = key,
YOURNAME_Circle_Induction_Year__c = system.today()));
}
else if(resultMap.get(key) >= 15 && conS.YOURNAME_Circle_Lapsed_Member__c == TRUE){
//they were re-inducted so unlapse them. Should we update induction year again?
tasksToInsert.add(
new Task (
WhoID = key,
Subject = 'Lapsed YOURNAME Member has Qualified Again!',
Status = 'Not Started',
Type = 'Donor Services'));
ecStatusUpdate.add(
new Contact(
Id = key,
YOURNAME_Circle_Lapsed_Member__c = FALSE));
}
else if(resultmap.get(key)<= 14 && conS.YOURNAME_Circle_Lapsed_Member__c == FALSE && conS.Years_Donated_Last_20__c == 15){
//they're newly lapsed. check lapsed box, write lapsed date.
tasksToInsert.add(
new Task (
WhoID = key,
Subject = 'Lapsed YOURNAME Member!',
Status = 'Not Started',
Type = 'Donor Services'));
ecStatusUpdate.add(
new Contact(
Id = key,
YOURNAME_Circle_Lapsed_Member__c = TRUE,
YOURNAME_Circle_Lapsed_Year__c = system.today()));
}
else{
system.debug('No elses +++');
}
}
}
if(yearsToUpdate.size() > 0){
update yearsToUpdate;
}
if(tasksToInsert.size() > 0){
insert tasksToInsert;
}
if(ecStatusUpdate.size() >0){
update ecStatusUpdate;
}
}
}