The Recurring Donation object in the Non Profit Success Pack (NPSP) is a great way to keep track of regular pledges made by donors. There are a number of different options and features that can be configured to control the number of opportunities connected to each Recurring Donation record.

But what happens when it comes time to report on active and inactive Recurring Donations? Thousands of Recurring Donation records can make it quite difficult to figure out which donors are still pledging and which donors have finished (or cancelled) their commitments. Adding a Current Status field to the Recurring Donation object will help solve this challenge.

In order to define whether or not a Recurring Donation is active or inactive, the solution I came up with involves tracking the number of open opportunities connected to the Recurring Donation record. Unfortunately, because no master-detail relationship exists between these 2 objects, we can not use a Rollup Summary field to achieve this.

There are a number of ways to achieve this, including, Apex code and AppExchange packages (like Rollup Helper). For the purpose of this demonstration, I will be using a trigger created on the Opportunity object. This trigger will monitor the creation, update, deletion, and, undeletion of opportunities with the Recurring Donation lookup field populated (or removed). The Trigger will then update the Recurring Donation record with a count of the open opportunities connected to the Recurring Donation record. Once this is in place, it makes it easy to use Workflow rules and Formula fields for a current status field, and optionally, further field updates and email alerts.

Creating a trigger requires the use of a Development platform such as Eclipse or Sublime/Mavens Mate or similar. If you are not familiar with Salesforce development, I would recommend looking into using Rollup Helper as a way to achieve this.

To get started, create a Number field on the Recurring Donation object (Setup -> Create -> Objects). Call it Open Opportunities with a Field Name of Open_Opportunities Ensure the Decimal Places are 0 and the field is NOT a required field. I would also recommend making this field Read Only for everyone and potentially not even including this field on the Page Layout.

Either in a Developer instance or a Sandbox instance, create the following classes:

OpportunityRecurringRollupHelper.cls

public with sharing class OpportunityRecurringRollupHelper {

	/**
	* This will roll up open opportunities counts to the recurring donation.
	* This is used to determine whether or not the recurring donation is active or not.
	*/
	public static void rollupOpenOpportunityCounts(List recurringIds) {
		List opportunityResults = [Select npe03__Recurring_Donation__c, Count(Id) openOpps
													From Opportunity
													Where npe03__Recurring_Donation__c in :recurringIds
													And IsClosed = false
													Group By npe03__Recurring_Donation__c];
		List updates = new List();
		Set processedRecurringIds = new Set();
		for (AggregateResult result : opportunityResults) {
			updates.add(new npe03__Recurring_Donation__c(Id = (Id)result.get('npe03__Recurring_Donation__c'), Open_Opportunities__c = (Integer)result.get('openOpps')));
			processedRecurringIds.add((Id)result.get('npe03__Recurring_Donation__c'));
		}

		// for all the recurring donations that do not have any opportunities on the criteria as per what is above
		// we need to mark them as 0 open opportunities.
		// this would be common in the scenario of all open donations being deleted
		for (Id recurringId : recurringIds) {
			if (!processedRecurringIds.contains(recurringId)) {
				updates.add(new npe03__Recurring_Donation__c(Id = recurringId, Open_Opportunities__c = 0));
			}
		}

		if (updates.size() > 0) {
			update updates;
		}
	}

}

OpportunityRecurringRollupHelperTest.cls

@isTest
public class OpportunityRecurringRollupHelperTest {

	////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//// SCENARIO
	////	When a recurring donation is created it creates a series of opportunities.
	////	When those opportunities are updated it triggers an update to report back to the recurring donation the number of
	////		opportunities that are still open for a recurring donation.
	////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	public static testMethod void testOpenOpportunityCounts() {

		Contact con = new Contact(LastName = 'Franklin');
		insert con;

		Test.StartTest();

		npe03__Recurring_Donation__c recur = new npe03__Recurring_Donation__c();
		recur.npe03__Installment_Period__c = 'Monthly';
		recur.npe03__Open_Ended_Status__c = 'Open';
		recur.npe03__Schedule_Type__c = 'Multiply By';
		recur.npe03__Amount__c = 50;
		recur.npe03__Date_Established__c = System.Today();
		recur.npe03__Next_Payment_Date__c = System.Today();
		recur.npe03__Contact__c = con.Id;

		// this is inside the test to ensure the opportunities are generated after StopTest is called.
		insert recur;

		Test.StopTest();

		List recurrings = [Select Open_Opportunities__c From npe03__Recurring_Donation__c];
		System.assertNotEquals(0, recurrings[0].Open_Opportunities__c);

		Decimal openOpps = recurrings[0].Open_Opportunities__c;
		System.Debug(openOpps);

		List opps = [Select Id, Name, StageName From Opportunity];
		opps[0].StageName = 'Closed Won';
		update opps[0];

		recurrings = [Select Open_Opportunities__c From npe03__Recurring_Donation__c];
		System.assertEquals((openOpps - 1), recurrings[0].Open_Opportunities__c);

		// remove an opportunitiy
		delete opps[1];

		recurrings = [Select Open_Opportunities__c From npe03__Recurring_Donation__c];
		System.assertEquals((openOpps - 2), recurrings[0].Open_Opportunities__c);
	}
}

Also, create the following Trigger:

OpportunityTriggerRecurringDonationCount.trigger

trigger OpportunityTriggerRecurringDonationCount on Opportunity (after insert, after update, after delete, after undelete) {

	// whenever an opportunity is deleted, undeleted, updated or inserted, we need to add the update the open opportunity count on the recurring donation record.
	System.Debug('Opportunity Trigger isAfter: Rollup Open Opportunitiy Counts');

	Set recurringDonationIds = new Set();

	if (Trigger.isDelete) {
		for(Opportunity opp : Trigger.Old) {
			if(!String.isEmpty(opp.npe03__Recurring_Donation__c) && !recurringDonationIds.contains(opp.npe03__Recurring_Donation__c))
				recurringDonationIds.add(opp.npe03__Recurring_Donation__c);
		}
	} else if (Trigger.isInsert || Trigger.isUndelete) {
		for(Opportunity opp : Trigger.New) {
			if(!String.isEmpty(opp.npe03__Recurring_Donation__c) && !recurringDonationIds.contains(opp.npe03__Recurring_Donation__c))
				recurringDonationIds.add(opp.npe03__Recurring_Donation__c);
		}
	} else { // update
		for(Opportunity opp : Trigger.New) {
			Opportunity oppOld = Trigger.oldMap.get(opp.Id);
			if(opp.npe03__Recurring_Donation__c != oppOld.npe03__Recurring_Donation__c || opp.StageName != oppOld.StageName) {
				if(!String.isEmpty(opp.npe03__Recurring_Donation__c) && !recurringDonationIds.contains(opp.npe03__Recurring_Donation__c))
					recurringDonationIds.add(opp.npe03__Recurring_Donation__c);

				if(!String.isEmpty(oppOld.npe03__Recurring_Donation__c) && !recurringDonationIds.contains(oppOld.npe03__Recurring_Donation__c))
					recurringDonationIds.add(oppOld.npe03__Recurring_Donation__c);
			}
		}
	}

	if(recurringDonationIds.size() > 0) {
		// report back to the recurring donation any opportunities that are still open
		OpportunityRecurringRollupHelper.rollupOpenOpportunityCounts(new List(recurringDonationIds));
	}

}

Now, whenever an Opportunity connected to a Recurring Donation record is inserted, updated, or deleted, The Open Opportunities field on the Recurring Donation record will be updated to indicate how many opportunities are open.

The next step is to create a Formula field on the Recurring Donation object. This will be used to calculated whether or not the Recurring Donation record is Active or Inactive. Call the Formula field ‘Current Status’. The formula should be as follows:

IF(Open_Opportunities__c > 0, 'ACTIVE', 'INACTIVE')

That’s it! This field will now be reporting whether or not the Recurring Donation record is Active or Inactive!

Another application of this could be creating a Workflow rule to send an email alert when a Recurring Donation becomes Inactive, or a Workflow rule to update a Date field when the Recurring Donation record becomes Inactive.