We needed to know how many unique years out of the last 20 a donor had given. This seems pretty simple because SQL select/aggregate queries are pretty straight forward. Being someone who has never written ANY apex code, I looked and looked and looked for a way to do this without apex. This needed to include soft credits as well as hard credits for a specific number of roles.
This.Was.Hard. I mean, really hard. Especially for someone new to Salesforce and programming in Apex. I googled the crap out of this issue and the closest thing I came up with was the DLRS for ‘streak’ here: https://biggerboatconsulting.com/calculating-donor-streak-npsp/
This wasn’t exactly what I was trying to achieve. I broke down and decided to learn enough Apex to teach me what I needed. In this instance, I was looking for the number of years, out of the last 20 years, that a donor had given. I don’t know much about Apex or making the code super ‘efficient’ but this is the best way I could come up with to achieve the results.
I even opened a case with AdminAssist to see if I was missing something. I wasn’t. There is no way to achieve a count of unique years donated by contact or account.
As far as retroactively updating these, depending on the size of your database, you’ll need to do it in batches. It’s not the solution I envisioned but I needed something like.right.now and didn’t have a ton of time. What I did (after bulkifying the code) was to make a temp update checkbox and batch update it with Apsona.
I decided the solution would work best and most efficiently on the Opportunity Contact Role object because our requirements included making sure that soft credits were included. If that weren’t the case, this would have been a bunch simpler although we already need some refactoring done on the Opportunity triggers….but that’s for another day.
I went through a few iterations of the logic on this one and it sucked. Every time I thought I had it, I ran into a ‘but you can’t because….’. Below is the trigger I created. I’m still in the early stages of testing, but so far it looks pretty good for our needs. It’s good to go. It also needs a delete trigger that is yet to be created – but I will add it and any other updates to git.
You can check the git repo here: https://github.com/ellpc/nonprofitcloudsalesforce/blob/master/uniqueyearstrigger
Fields Impacted:
YOURNAME_Circle_Induction_Year__c Date
YOURNAME_Circle_Lapsed_Year__c Date
YOURNAME_Circle_Lapsed_Member__c Bool
Years_Donated_Last_20__c Decimal
Account: GivingSocietyNameHere_Circle__c Bool
Years_donated_last_20__c drives the check box with a formula field and
YOURNAME Circle Status is a check box the primary contact's YOURNAME status flows up to the account level with a formula field.
**/
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;
}
}
}Since this is literally my ‘hello world’ code I’m going to do a separate post and go over all the parts – dissecting each piece so maybe someone else can get unstuck on the things I got stuck on.
