Previously, I explained how hard it is to do some simple kinds of reporting in the new Nonprofit Cloud and Education Cloud. In particular, I was looking at the impossibility of making a report that would show people (Person Accounts) and their donations (Gift Transactions) while also being able to show or filter based on donation rollups, like Total Giving (over their lifetime), Gifts This Year, etc. The problem stems from the fact that giving rollups, in the new clouds, do not reside on the account record, nor even on the contact record. They're on an extension object called Donor Gift Summary.
I wasn't going to leave my client without the reports she requested, particularly since I think her requests are entirely reasonable. So I found a workaround.
But I don't love it. I’m a little afraid it could introduce problems in large data volume environments. Still, it works and I'm proud of having thought of it. So I'm sharing it with you. But caveat emptor and YMMV (your mileage may vary).
A Lookdown Relationship
Step 1 is to create yourself a new custom field on account. I called mine DonorGiftSummary. This is a lookup field, to…wait for it…Donor Gift Summary.
This is a lookup field. But it’s really a "lookdown" because Donor Gift Summary is already a child object to Account. (It has a lookup field of its own, called DonorId, that looks up to Account.) Yes, I'm intentionally creating a redundant field here.
Dolores Does the Work
Next I built a DLRS rollup to fill the DonorGiftSummary field.
Parent: Account
Child: DonorGiftSummary
Field to Aggregate: Id
Aggregate Operation: First (or last, since there is only ever one DGS)
Mode: Process Builder
In plain English, what that rollup does is take the Id of the Donor Gift Summary that goes with each account and put it into the DonorGiftSummary field. Because it's set to the mode "Process Builder," this rollup will only fire when called by other automation. I then used DLRS's Schedule Full Calculate button to set this rollup to run once per night around 4am.
(If you want to hate on DLRS, you’re welcome to fill the field using a scheduled flow instead. But it took me less than three minutes to build, test, and schedule that DLRS rollup.)
Schedule Your Work
As noted above, we run this rollup nightly, an hour or two after the Data Processing Engine (DPE) for donor rollups runs.
If this is the first time you’re hearing the phrase "data processing engine" or seeing the acronym "DPE," consider yourself lucky. I'll have to write about those sometime soon. The Short Version: a DPE definition is like a Flow that can collect, filter, and calculate, but it does the bulk of the work off-platform. Once you go through the new cloud setup steps, you'll have created a scheduled flow that runs the donation rollups DPE definition that Salesforce provided. This definition takes all your donation data, calculates the rollups for accounts, and then inserts donor gift summary records. Unfortunately, the DPE definition that Salesforce has provided so far has all kinds of problems, including getting some rollups wrong. (Oops.) And DPEs aren't Flows, particularly in the sense that nobody understands how to build or modify them.
But what matters for the timing of this DLRS is that the DPE deletes all donor gift summaries and then inserts new ones, rather than doing an upsert (updating those that exist and inserting the new ones that are needed). So we have to make sure the DLRS runs daily and after the DPE is finished or else our new field will be blanked out for all records each night. (I assume that setting this DLRS to realtime mode would be a disaster when the DPE goes to insert all those donor gift summaries at one time.)
The "Lookup Lookdown" is the Magic
Now that we have a lookup field on Account that points us to a single DPS record, we have the option of adding any of the DGS fields to custom report types via lookup!
(Great that we can do this. But it's still a pain in the butt to have to edit all of our custom report types.)
We also now have the option of formula fields on Account that would span the lookup relationship. Those could allow us to put rollup fields on the page layout without using the R2D2 component [Not my favorite component.] or to calculate other things about an account's rolled-up/summarized donation information.
Bam!
Now a report that was simply impossible is unlocked. 🏆