NPSP’s Accounting Subledger (ASL) is a fantastic new addition to the Salesforce.org suite of products. The Growth Edition of this product can generate Credits and Debits needed by accounting systems. However, out of the box, ASL does not have any reports that show account balances which can be very useful when reconciling and ensuring accuracy. Let’s fix that!

But first, a disclaimer: since we likely don’t have a complete history of transactions, balances may not 100% match the accounting system. Instead, these balances will only represent the balances based on the transactions that do exist in Salesforce. This is still useful however, for reconciling based on activity dates or filtering by a specific gift.

The first challenge is that ASL’s “Ledger Entry” object does not have any direct way of indicating what type of account (asset, liability, equity, income, or expense) is being debited or credited. So we need to build some way to make this fact known.

Why? This touches on one of the most confusing aspects of accounting; debits and credits can be an increase or a decrease of funds based on the type of account being transacted with. This concept is fundamental to accounting and is critically important to get right in order to understand the balance of any given account.

When we debit an asset or expense account, we’ve increased it’s balance, and when we credit that same account, we’ve decreased it’s balance. The opposite is true for the income, equity, and liability account types. Given these fundamentals, we therefore represent balances as “the increases less the decreases”. Therefore, we have the following equations:

• Asset Balance = Debits - Credits
• Liability Balance = Credits - Debits
• Equity Balance = Credits - Debits
• Income = Credits - Debits
• Expense = Debits - Credits

Ok, enough class time. tldr; without an “account type” indicator we’d summarize all accounts as debits less credits (or vice versa) which isn’t correct. In the picture below, the negative value is for an income account and makes it incorrectly look like we had negative income.

So, we need a field that describes the account’s type. The simplest method we’ve discovered for determining the type of account being debited/credited is the following logic:

1. Fundamentally, ASL focuses only on Income, Expense, and Asset account types. So, we can ignore the others.
2. When ASL ledgers are created, ASL copies a value into the “GL Code” field. The values placed into this field are one of:
   1. The value specified on the settings page for “Default Pledge Code”. This is an asset account.
   2. The value specified on the settings page for “Default Write-Off Code”. This is tricky as it could be a contra asset or an expense account. We’ll assume “expense” for this example. Either way, the formula is the same for both: debits less credits.
   3. The value for the Payment’s Payment Method. This is an Asset Account.
   4. The value for the related GAU’s name. This is an Income account.

Ok, so how do we actually build this? With a simple formula:

CASE(abacus__GL_Code__c
 , $Setup.abacus__Ledger_Entry_Settings__c.abacus__Default_Pledge_Code__c, "Asset"
 , $Setup.abacus__Ledger_Entry_Settings__c.abacus__Default_Write_Off_Code__c, "Expense"
 , TEXT(abacus__Payment__r.npe01__Payment_Method__c), "Asset"
 , abacus__General_Accounting_Unit__r.Name, "Income"
 , "ERROR"
)

A quick explanation:

• We evaluate the value of the abacus__GL_Code__c field
• If it matches the value of the pledge code (as defined in the settings), it’s an asset
• If it matches the value of the write-off code (as defined in the settings), it’s an expense
• If it matches the text value of the payment method, it’s an asset
• If it matches the text value of the GAU’s name, it’s an income
• Otherwise we error out (my opinion is that we should never assume an account type and instead use the “ERROR” as a way to find flaws in logic)

With this formula field in place we can now move on to building a balance report.

One of my favorite recent features is Salesforce’s new “Row-Level Formula” within reports. With this feature, we can dynamically determine whether to treat a ledger as a negative or positive number so that we can summarize it correctly. Of course, we can always build a formula directly on the object for this, but let’s use this opportunity to also try out a nifty new feature.

The formula you see here essentially says “Take the debit and less the credit, and multiply by -1 if the account type is ‘Income’”. Here it is as copy/pastable text:

(BLANKVALUE(abacus__Ledger_Entry__c.abacus__Debit_Amount__c,0) - BLANKVALUE(abacus__Ledger_Entry__c.abacus__Credit_Amount__c,0)) * IF(abacus__Ledger_Entry__c.FEAccountType__c = "Income", -1, 1)

After adding this formula to our report, we now see our income accounts with positive balances (yay, we made money!).

Pro-tip: add a filter to this report that filters by opportunity, couple it with a detail page button, and this report can now also be used to view the balances for a specific opportunity. This can helpfully show development and accountant users the receivables balance, etc.

Conclusion

ASL has everything we need to be able to build a balance report which can be very helpful when reconciling and validating data it’s generated. A couple simple formulas along with some basic reporting is all it takes.

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.