Using NPSP Accounting Subledger With Legacy Data

Foglight recently implemented NPSP and Accounting Subledger (ASL) for a customer who migrated from Tessitura. One of the challenges we faced with the ASL portion of the project is getting ASL to generate Ledger Entries for pledges that were migrated from Tessitura, without generating Ledger Entries for the other gifts that are complete. This particular migration had 400,000+ historical gifts with a very small portion of them still active/unpaid. It’s this smaller portion that needed Ledger Entries.

So, how do you make ASL generate Ledger Entries for a portion of the records?

One of the coolest features of Salesforce is the “Insertable System Audit Fields” setting. After enabling this setting, Salesforce allows users to set the CreatedById, CreatedDate, LastModifiedById, and LastModifiedDate fields when inserting data via the API or Apex. This is particularly useful when migrating legacy data and you want to preserve this original audit information from the legacy system.

So it seems natural to think, “if we can preserve the audit information, let’s only generate Ledger Entries for data that’s created after our migration date.”

And that’s where Accounting Subledger’s “Accounting Records Start Date” setting comes into place. This setting lets you specify a Created Date with which to filter Opportunities (e.g. the date/time the migration and testing is complete), and voila, ASL will ignore everything older than that.

But this actually won’t solve our original challenge; our legacy pledges with incomplete payments will have a system audit date of pre-migration and thus will be ignored by ASL. Said another way, if ASL filters out opportunities based on their created date and we use the migration date as the filter, then all legacy records will be ignored. Additionally, this approach also won’t work for legacy pledges or gifts that were still in a “draft” status.

One obvious solution is to pick a different, earlier date. But this is wasteful; the system will end up with a ton of unnecessary Ledger Entries consuming valuable data storage. Additionally, if not careful, these extraneous Ledgers could be posted to the accounting system, essentially double-counting the gift. Not good.

It would be better if ASL filtered on the Close Date OR the payment date. With this logic, ASL would generate records for all new opportunities AND all legacy opportunities with new payments (payments are one of the primary sources of data that ASL uses to generate Ledger Entries). This also works for legacy gifts that are still in a draft or pending state.

So, what to do?

Sometimes the solution is “fix it manually”. But with the volume of incomplete pledges (500+) still in the system, we didn’t think that approach was appropriate here.

So instead, we decided to not use the “Accounting Job Filter Criteria” field, but learned that we can’t leave it blank as ASL will instead default to using the package install date. So we must specify a date that will include our legacy gifts, but also find a way to only get the correct gifts. To do this we decided to utilize an additional filter setting that ASL provides called “Search Filter”.

This setting allows us to specify a SOQL WHERE clause (actually, the stuff after the WHERE). This is genius and I’m so happy the ASL team provided this feature as it gives subscribers a lot of flexibility to address challenges like this one.

So we immediately set to work setting our SOQL as follows:

CloseDate >= 2020-08-11
OR
Id IN (SELECT npe01__Opportunity__c
         FROM npe01__OppPayment__c
        WHERE npe01__Payment_Date__c >= 2020-08-11
		 AND (npe01__Paid__c = true OR npe01__Written_Off__c = true)
)

This SOQL essentially says “get all opportunities where the Close Date is greater than or equal to 8/11/2020, OR get all opportunities that have a payment that was paid or written off on a date greater than or equal to 8/11/2020” (additionally filtering on opportunities that are created after the “Accounting Records Start Date” date).

But, this didn’t work as well as we’d hoped because ASL is injecting this WHERE statement into an inner query, and thus a 2nd inner query is not supported.

But the approach is still valid, it just needed to be tweaked slightly. Instead of using an inner query to find opportunities with qualified payments, we can use a Rollup Summary field to persist that data point directly to the opportunity.

The RUS definition above saves the date of the most recent (MAX) paid (or written-off) payment directly to the opportunity. Now we can alter the SOQL to drop the inner query and reference the new RUS field as follows:

CloseDate >= 2020-08-11
OR
MaxPaidOrWrittenOffPaymentDate__c >= 2020-08-11

So there you have it! ASL’s “Accounting Records Start Date” setting is fantastic, but it’s not quite enough by itself and needs to be thoughtfully combined with the “Accounting Job Filter Criteria” setting.

If your organization is considering Salesforce’s Accounting Subledger, or needs help setting it up, click the contact button below to learn how we can help you.