Learn How To Add Amount Remaining on NetSuite Customer Deposits

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.

1. Create NetSuite Customer Deposits from Opportunities or Estimates
2. Using NetSuite Payment Methods to Drive Customer Deposits
3. Solved: NetSuite Customer Deposits and Advance Payment Accounting
4. Yes You Can: Generate a NetSuite Customer Statement on an Invoice with Customer Deposit Accounting
5. Use NetSuite SuiteScript to Automate Posting Customer Deposits to Invoices
6. Applying NetSuite Sales Order Customer Deposits on Independent Invoices
7. Converting NetSuite Customer Deposits to Credit Memos
8. 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:

1. Deposit Consumed: how much of the customer deposit has been consumed so far?
2. 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:

1. Name: I like to parenthetically name the saved search to indicate it is used in other structures.
2. Criteria: Type equal to “Customer Deposit”.
3. Results: One column “Applying Transaction: Amount”. Use the “Sum” summary type.
4. 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.
5. 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:

1. Name: Deposit Consumed. I like to prefix our custom fields with PRI to stand for the name of our firm, Prolecto Resources, Inc.
2. 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.
3. Type: Currency
4. Search: Reference the saved search you created in the previous step.
5. 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.
6. 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:

1. Name: Deposit Remaining
2. ID: Give your custom field an ID. For consistency, I called mine: custbody_pri_deposit_remaining
3. Type: Currency
4. 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.
5. Formula and Store Value: Set the Formula checkbox on. Turn off the Store Value checkbox.
6. 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.
7. 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.

Be Sociable, Share!

• 
• 

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube