How To Expose Unapplied Customer Deposit or Payment Amount in a NetSuite Saved Search

• April 14, 2023:
  • Updated to include a simpler approach shared by Lee Hopper.

In this article, I explain two ways you can create a single NetSuite saved search that exposes the applied vs. unapplied portion of a Customer Deposit or Customer Payment.

TL;DR

By following the steps outlined in this article, you will produce saved search output similar to the one illustrated below.

Context

While working with a client recently, he asked me if it was possible to produce a control search that captures all payments and deposits with unapplied balances. In my client’s business, deposits are created when money is received against a sales order that is pending fulfillment. Typically, these deposits are automatically generated as prepaid orders from their eCommerce frontend are synced to NetSuite. Upon fulfilling the sales order for which the funds were captured, the customer deposit is used to pay the invoice and recognize revenue accordingly. Using customer deposits represents a superior accounting practice as opposed to using cash sale transactions for this use case.

Thus, for this client, unapplied deposit balances were an indication of an anomaly that needed to be investigated and fixed. In some cases, taxes were off due to issues with the tax computation in the front end, orders had changed and a refund was pending, etc. Understandably, reviewing all deposits/payments and/or performing lookups in Excel to determine which ones required attention was undesirable. My client wanted to “manage by exceptions” i.e. only review transactions with unapplied balances. The reasoning is similar for customer (pre-)payments resulting from incoming wire transfers.

Based on prior experience and results of Google searches on the subject, I was inclined to believe that it was impossible to capture this information in a single saved search. The basic limitation of conventional approaches is that the applied amount is available at the line level. Thus, to compute the unapplied amount on a given deposit or payment, one would somehow need to sum the applied amount across all lines and subtract it from the header-level total amount. The latter step of subtraction is where the challenge lies.

While SuiteQL would likely produce the desired result using inner queries or similar strategies, the client required that the results be automatically emailed to individuals responsible for reviewing and handling these exceptions. At Prolecto, we have developed a Query Renderer tool that has the requisite email capability. Thus, I had a fallback. However, before going down the SuiteQL route, I stumbled upon an article that offered a breakthrough!

Breakthroughs

SuiteAnswer ID: 22423^[I]NetSuite (January 23, 2023). Transaction Saved Search > Show the Amount of Customer Deposit, Applied and Unapplied Amounts Sorted by Customer Name. Available at: … Continue reading offered the basic pattern for customer deposits which I extended to cover customer payments as well.

Thanks to an amazing pointer from one of our regular readers, Lee Hopper, I learned about a simpler solutions. Many thanks to Lee for this tip!

Next I’ll explain the two solutions beginning with simpler one.

Solution 1: Easy Approach

This solution is simple, elegant and gets the job done without complicated formulas.

We create a Transaction saved search (Lists > Search > Saved Searches > New > Transaction). The search criteria are pretty basic: Add filters Type is any of Payment, Customer Deposit and Main Line is false as illustrated below. If necessary, add other criteria e.g. to limit the results to a particular subsidiary or GL accounts. In case of a GL account filter, be sure to use the “Account (Main)” field rather than the “Account” field as the bank account will be on the header (i.e. main) line.

In the Results subtab, enter the columns illustrated below:

The formulas should be pretty straightforward to understand.

• The key is the amountpaid search result field amountpaid which captures the sum of the paid amounts and is available on each line – exactly what we need! Interestingly, this field is always zero at header-level.
• The unapplied amount is thus the difference between amount and amountpaid. We take the absolute value of the amount as it is negative on Payment transaction lines.

That’s it!

Note that there is also a paidamount field which captures the amount paid per line. This could be interesting as well. Note though that it is only available on Payments, not Customer Deposits. Here are the results of the search to help clarify the difference between amountpaid and paidamount.

Pro Tip: If you’d rather not have a summary search, all you need to do add search criterion “Line Sequence Number is 1”. This is guaranteed to always look at the first non-header transaction line. See related article “Understand Line ID vs. Line Sequence Number in NetSuite Transactions” if you do not understand why this works.

Solution 2: Advanced Approach

Next, I’ll present the original solution that I came up with. As you will see, it is complex and hard to understand. Thus, I recommend the previous solution. However, I’ve left this one in as I suspect there are other use cases where this pattern will be useful (I recently worked on an item search where I leveraged the same technique).

Creating the Saved Search

Again, we create a Transaction saved search (Lists > Search > Saved Searches > New > Transaction). The search criteria are identical to those in solution 1 above. Thus, we focus on the results. In the Results subtab, enter the columns illustrated below. I will focus on explaining the formula fields as the others should be self-explanatory.

1. Field: Formula (Text)
   • Summary Type = Group
   • Formula: '<a href="/app/accounting/transactions/transaction.nl?id='||{internalid}||'">'||{tranid}||'</a>'
   • Summary Label = View
   • This adds a convenient link to open the transaction without having to first drill down which would be the case if we added the Document Number field directly. It is an optional but handy trick to eliminate one extra click.
2. Field: Amount
   • Summary Type = Maximum
   • Function = Absolute Value
   • Summary Label = Total Deposit / Payment Amount
   • This captures the total deposit or payment amount. We take the absolute value as the value is negative on payment transactions. Note that we use the Maximum summary type to ensure that the column is visible at the summary search level. Minimum or Average will also work as the (header) amount is the same on all lines. This observation also applies to the following formula fields.
3. Field: Formula (Currency)
   • Summary Type = Minimum
   • Formula = abs(sum(NVL(DECODE({typecode}, 'CustDep', {applyingtransaction.amount}, {appliedtolinkamount}),0)))
   • Custom Label = Summary Label = Applied Amount
   • This formula sums the total applied amount. I will explain this formula in more detail in the next section.
4. Field: Formula (Currency)
   • Summary Type = Minimum
   • Formula = max(abs({amount}))-abs(sum(NVL(DECODE({typecode}, 'CustDep', {applyingtransaction.amount}, {appliedtolinkamount}),0)))
   • Custom Label = Summary Label = Unapplied Amount
   • This formula captures the total unapplied amount. We will explore how it works shortly.

If you’ve followed the above steps accurately, you should now have a search that produces the desired output. Congratulations! But before you jump to the next challenge, hang on a bit, and let’s try to understand what is happening here.

Decoding the Saved Search

Let’s zoom in on the last two formulas. To help you understand, I’ve placed images of the line details of the four transactions in our example next to each other. Click on the image to enlarge it if necessary.

Formula for Applied Amount

Observe the following:

• Unapplied deposits and payments have a non-header line albeit with zeroes or blanks in the fields on interest (recall that our search was filtered to mainline = false so every line in the output is a non-header line).
• We are deliberately using {appliedtotransaction.amount} instead of {appliedtolinkamount} on the one hand and {applyinglinkamount} instead of {applyingtransaction.amount} on the other hand for reasons explained below.
  • For payments, the applied to transaction is what we care about. However, whereas Applied To Transaction : Amount ({appliedtotransaction.amount}) gives the total amount of the applied to transaction, we need to use the Applied to Link Amount ({appliedtolinkamount}) as it captures the exact amount that was applied. For example, in line 3 of PYMTH00003429, only $50 was applied to the JE and that is the amount we need instead of the total JE amount of $100!
  • For deposits, the applying transaction is the deposit application and the Applying Transaction : Amount ({applyingtransaction.amount}) captures what was applied to the target invoice, JE, etc. In our example, we deliberately used a deposit that was partially applied to confirm. Notice in the screenshot of the deposit application below that the $113 corresponds to the total amount applied to the two invoices which is less than the total original amount ($170.73) and the total amount due ($136). Thus, we confirm that we are capturing exactly what was applied using this field. Moreover, the Applying Link Amount ({applyinglinkamount}) is blank for unclear reasons.

To summarize, the formula abs(sum(NVL(DECODE({typecode}, ‘CustDep’, {applyingtransaction.amount}, {appliedtolinkamount}),0))) sums the total applied amounts, taking empty values (i.e. unapplied payments) as well as nuances between the various amount fields into account. The abs() function is necessary as the applying transaction amount is negative.

Formula for Unapplied Amount

This formula for the unapplied amount is the essence of the solution. Pay close attention. It reuses the previous formula and can be expressed conceptually as max(abs({amount})) - formula_for_applied_amount.

This formula is challenging to understand, at least for me. It produces the desired results by somehow computing the total applied amount across all lines and subtracting it from the total deposit/payment amount! Notice that the max function only applies to the total deposit/payment amount and not the entire expression. It is not very intuitive to have a max here because the {amount} is the same across all lines as we saw in the screenshot above. Moreover, I observed from experimenting that the max is somewhat of a placeholder; min or avg also produce identical results. It thus appears that max is simply used to trick NetSuite to bypass the normal formula restrictions and allow us to access powerful aggregation/analytics functionality. A similar pattern is used to access Oracle’s analytics functions as Marty Zigman explains here. This might be yet another instance of the same trick. By the way, removing the max breaks the search: "An unexpected error has occurred.  Please click here to notify support and provide your contact information." It remains a bit mysterious to me and I would be glad if a more knowledgeable reader can explain precisely what is happening here.

Conclusion

Someday, I am sure I will better understand the advanced solution. In the meantime, I am glad that Lee led us to a simpler approach. Never say never!

Finally, while researching, I came across SuiteAnswer ID: 65454 which explains how to create a saved search that shows the total applied and unapplied amount on all customer deposits for each customer. It approaches the problem using a customer search rather than a transaction search. I assume that we will achieve identical results using the above transaction search by stripping all Summary Type = group fields except the Name (i.e. customer) field. The interested reader is invited to explore this further and share their findings.

I hope you have found this article insightful. If so, let me know by dropping a comment. Keep an open mind, keep learning, and keep sharing.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!

Related Posts

1. How To Hyperlink Multi-Select Fields in NetSuite Saved Search Results
2. Saved Search Deployment Failed? Try This Before Resorting to Manual Deployment!
3. Understand Line ID vs. Line Sequence Number in NetSuite Transactions