top of page
Writer's pictureMichael Kolodner

Band-Aid for New Cloud Reporting


A cartoon image of an adhesive bandage.

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.

Screenshot of the setup page for a custom field that is a lookup to DonorGiftSummary.

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.

Freebie as Aunt Dolores, the rollup specialist.

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! 

Screenshot from the custom report type builder adding fields via lookup that spans Gift Commitments>Donor>DonorGiftSummary and then selects fields.

(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. 🏆

602 views

Recent Posts

See All

Comments


Don't wait for the next post! Get them in your In Box.

bottom of page