This article is relevant if you would like to see the remaining balance or amounts available for use on a NetSuite customer deposit.
Background
I have written a number of articles suggesting advanced use cases for NetSuite customer deposits.
- Create NetSuite Customer Deposits from Opportunities or Estimates
- Using NetSuite Payment Methods to Drive Customer Deposits
- Solved: NetSuite Customer Deposits and Advance Payment Accounting
- Yes You Can: Generate a NetSuite Customer Statement on an Invoice with Customer Deposit Accounting
- Use NetSuite SuiteScript to Automate Posting Customer Deposits to Invoices
- Applying NetSuite Sales Order Customer Deposits on Independent Invoices
- Converting NetSuite Customer Deposits to Credit Memos
- Learn How To Recurrently Accept NetSuite Customer Payments and Deposits
Fundamentally, a customer deposit is a credit (liability) to a customer. These liabilities can be disbursed in multiple ways. NetSuite does not provide a convenient way to see the remaining funds available from a single customer deposit. The good news is that you can use NetSuite point-and-click customizations features to calculate this amount. Here is how to do it.
Example Transaction to Prepare for Setup
I recommend you have a sample transaction that has the situation you are concerned about to set up the structures. See related image. In this example, I show the end goal with two new fields:
- Deposit Consumed: how much of the customer deposit has been consumed so far?
- Deposit Remaining: the difference between the customer deposit and the amount consumed.
In the example transaction (see related image), the original $10,000 deposit has been used six times to apply funds against invoices. Natively, NetSuite illustrates the amount and it will conveniently indicate how much has been applied so far. Yet, this information is not readily available to determine how much is now remaining.
Use Summary Saved Search to Determine the Deposits Consumed
The first order of business is to create a transaction-based saved search that summarizes how related transactions have been applied to the customer deposit. The setup requires the creation of a transaction based saved search as follows:
- Name: I like to parenthetically name the saved search to indicate it is used in other structures.
- Criteria: Type equal to “Customer Deposit”.
- Results: One column “Applying Transaction: Amount”. Use the “Sum” summary type.
- Filter: Set it to the internal ID. You do not need to show it. The reason we do this is that when a customer deposit form (record) loads, it can pass its internal ID to effectively bind itself to the saved search to filter on the respective customer deposit.
- Visibility: Set Public switch on.
Once you have your Summary Saved Search, you can create your first custom field.
Create Custom Transaction Body Field to Reference Summary Saved Results
The next step is to create a custom transaction body field so you can show it on your customer deposit form. See related image to create a field similar to the following:
- Name: Deposit Consumed. I like to prefix our custom fields with PRI to stand for the name of our firm, Prolecto Resources, Inc.
- ID: Create an ID for the name of your field. In my case, I called it custbody_pri_deposit_consumed which follows our naming conventions.
- Type: Currency
- Search: Reference the saved search you created in the previous step.
- Access Level: Set the field to be Default Access Level = “View” and “None” for Default Level for Search / Reporting. I will indicate why we do this below.
- Applicability: Set the field to be applicable to Deposits so that it shows up available on Customer Deposit forms
With this new field, you can now load your customer deposit record and you should see the results of your work. This amount should equal the amount that NetSuite provided. See related image.
Create Custom Transaction Body Field to Determine Available Amount
Now that you have calculated the amount of customer deposit that has been consumed, you can determine the difference between the amount of the customer deposit to get the remaining funds available. Thus, create a custom transaction body as follows:
- Name: Deposit Remaining
- ID: Give your custom field an ID. For consistency, I called mine: custbody_pri_deposit_remaining
- Type: Currency
- Default Value: Take the total amount {payment} less than your custom field. In my example case, as referenced above, it was {custbody_pri_deposit_consumed}. See related image for how to craft the formula.
- Formula and Store Value: Set the Formula checkbox on. Turn off the Store Value checkbox.
- Access Level: Set the field to be Default Access Level = “View” and “None” for Default Level for Search / Reporting. I will indicate why we do this below.
- Applicability: Set the field to be applicable to Deposits so that it shows up available on Customer Deposit forms
You can now format your customer deposit screens to your liking.
Getting Access to Custom Field Information via Saved Search
Now for some not great news. Unfortunately, Saved Search Summary Fields that return a value do not necessarily show up during subsequent use of the field in Saved Searches. Meaning, now that we have these two new fields, they don’t return values during their use in Saved Searches. See related image for how they output. This is why I indicated to turn them off in the Access Level definitions noted above. If you need these values in a Saved Search, then the clues for producing them are in the definition of the first Summary Saved Search above. Solving for this problem generally demands a different strategy.
Extend NetSuite to Make Life Easier
My hope is that this article illustrates the point-and-click power of the NetSuite platform. In my NetSuite Systems Integration practice, all of our business analyst (non-software development types) must be able to perform this kind of NetSuite customization. Now after 10 years being in the community, it is second nature to enhance NetSuite to solve challenges. Yet I appreciate how adding a new field to a conventional / traditional database system and business application used to be a major endeavor. I am grateful for how productive we all can be in the NetSuite application. If you would like to discuss your specific use case, comment below or let’s have a conversation.
One thing to add to the first custom field which is holding a summary saved saerch value, this custom field need to set Store Value tonincheck, otherwise, thensummarybresult cannot be shown in the field when viewing it on the actual form.
Hope this helps.
Hi Phoebe,
I actually see the summarized search value on the form without the Stored Value switch on. Are you not able to see it? I think it’s important to be careful with the stored value switch. I believe it will work the first time, but as the data changes, you won’t see the latest information.
Marty
I recently needed to find the available amount on deposit records as well and did it by creating a sum forumla (numeric) column in the saved search which gave the same result in one step.
CASE
WHEN {appliedtotransaction.type} IN (‘Sales Order’)
THEN {amount}
WHEN {applyingtransaction.type} IN (‘Deposit Application’)
THEN {applyingtransaction.amount} ELSE 0
END
Thank you Dan. Some environments may not have sales orders but this theme is great.
Marty
Marty,
I have created all of the fields and searches necessary. I keep running into an issue where even if I add the internal ID as a filter, it doesn’t recognize it on the customer deposit screen. If I make the filter visible, it appears that that internal IDs that are pulling in the search are unrelated to the deposits. No matter which internal ID field I use, it doesn’t display the same IDs as the customer deposits.
Also, I have added an additional criteria where internal ID = one of my customer deposits, and it works just fine. For some reason the available filters aren’t behaving the same. Any suggestions?
Thank you,
Hello Isaac. The practice should work. It is using NetSuite’s standard Summary Search hook up techniques. I suspect you are not adding the internalID to the first filter condition right in some way. Thus, the form’s internal ID is not being passed as a parameter to right place. Did you try NetSuite Support to look over your shoulder?
Marty
Hello Marty, readers might be interested in this solution that exposes the target data in a saved search: https://netsuite-insights.com/how-to-expose-unapplied-customer-deposit-or-payment-amount-in-a-netsuite-saved-search/
Hello Chidi,
Your article is easy to understand. This Customer Deposit Saved Search is most helpful. Thank you for the reference.
Marty