Use NetSuite SuiteScript to Automate Posting Customer Deposits to Invoices

This article is relevant if you working to automate the posting of customer deposits to invoices in NetSuite and you need SuiteScript reference information.

Background

NetSuite offers a way to post customer deposits (appropriately as liabilities) received by customers typically for advanced payments before goods are delivered.  I like to think of these similar to retainers commonly used in professional services organizations, such as attorney firms.  Smartly, NetSuite will automatically apply a customer deposit to a freshly generated (unpaid) invoice if it both transaction documents (the customer deposit and the invoice) is connected to a common Sales Order.

However, while the Sales Order connection is convenient, it also can be restrictive.  In my article last week, Applying NetSuite Sales Order Customer Deposits on Independent Invoices, I demonstrated a trick to apply Customer Deposits to Invoices not connected to a Sales Order.

As I have been studying how to increase my firm’s billing operations, which in many respects, may resemble a staffing firm as we heavily use NetSuite Advanced Projects the Timesheet as the basis for accounting for services, I began to see ways that the Sales Order was cumbersome to work with. ¬† While subject to another article, what I had to do is break the connection, in general, to the Sales Order as an organizing transactional concept.

Once you don’t have a Sales Order between your time entries and your invoice work, then NetSuite’s automatic customer deposit application no longer functions. ¬†What I needed was to preserve that automatic function so that unpaid invoices could be immediately paid based on monies (deposits) on account.

Automating Application of Customer Deposits to Open Invoices

Early in my work to automate this function, I thought I would try to apply a customer payment to the invoice. ¬† There is a transform function that will get the payment record in play. ¬†In this case, I would use the “Deposits” sublist to apply payment to the invoice. ¬†While this works in the user interface, it does not behave the same way via SuiteScript. ¬† ¬†I could see the deposit lines but I could not get them to actually apply. ¬†I speculate that applying a deposit from the Customer Payment screen actually does another transform behind the scenes.

As I inspected the record structures, I could see there is a record called a Deposit Application which effectively allows a many-to-many (M:M) relationship between Customer Deposits and Invoices.  NetSuite does not provide a user interface (UI) to create one of these deposit application records from scratch.  To create one, you always start from a Customer Deposit record.

Although there is a reference in the SuiteScript Record Browser, I was frustrated as there is no documentation on how to really work with this record. ¬†Per the NetSuite Help documentation, there is no Tranform support Deposit Application record. ¬† Yet, unwilling to concede, I found a clue by inspecting the URL for the Deposit Application and I could see “transform” as one of the parameters (side note: always watch what is happening in the URL as it is a clue to how the system is built). ¬†With some trial-and-error, I was able to figure out what I think is an undocumented feature to get what I needed.

SuiteScript Reference for Working with Customer Deposit Application Records

The following function was being called in an invoice AfterSubmit UserEvent.   I have added comments to help illustrate what is happening.  As of the time of this article, this transform function does not appear to support the respective record types; as such, use with care.

function applyDeposits(invId, cusId){ //invoice id and customer id
	var func = 'applyDeposits ';
	var depositbalance = nlapiLookupField('customer', cusId, 'depositbalance')

	//if there are no monies to apply, avoid doing work
	if (depositbalance > 0){
		//find all the related open deposit records
		var filters = new Array();
		filters[0] = new nlobjSearchFilter('entity', null, 'anyof', cusId);
		//indicates not full applied
		filters[1] = new nlobjSearchFilter('status', null, 'noneof', 'CustDep:C');
		filters[2] = new nlobjSearchFilter('mainline', null, 'is', 'T');
		//don't try to get those deposits hard linked to sales orders
		filters[3] = new nlobjSearchFilter('salesorder', null, 'anyof', '@NONE@'); 

		var columns = new Array();
		columns[0] = new nlobjSearchColumn('internalid').setSort(false);

		//hunt for the records
		var records = nlapiSearchRecord('customerdeposit', null, filters, columns);

		if (!records){
			nlapiLogExecution('DEBUG', func  + 'Found no deposit records; but they were expected.');
			return
		};

		nlapiLogExecution('DEBUG', func + ' starting deposit application work');
		for ( var r = 0; r < records.length; r++ ) {
			//transform below does not appear documented; but works as expected
			var deposit = nlapiTransformRecord('customerdeposit', records[r].getId(), 'depositapplication');

			deposit.setFieldValue('trandate', nlapiDateToString(new Date()));
			deposit.setFieldValue('memo', 'Applied Invoice: ' + new Date());

			//walk the invoice list that we want to apply; find the invoice we are working on
			var a = deposit.getLineItemCount('apply');
			for (var i = 1; i <= a; i++){
				if ( deposit.getLineItemValue('apply', 'internalid', i) == invId ){
					nlapiLogExecution('DEBUG', func  + 'working on invoice line:' + i, deposit.getLineItemValue('apply', 'refnum', i));
					//find the related line and mark it on; appears safe to apply as much as you can; NetSuite appears to
					// handle if there is insufficient funds and it will match the amount of the invoice
					deposit.setLineItemValue('apply', 'amount', i, deposit.getLineItemValue('apply', 'total', i));
					deposit.setLineItemValue('apply', 'apply', i, 'T');
				};
			};
			nlapiSubmitRecord(deposit);
		};
	};
};

Demand More from Your NetSuite Business System

NetSuite was designed to be optimized for business requirements. The platform opens up your space for innovation which can streamline your business operation. If you would like to get more out of NetSuite, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

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

| Tags: , , , , | Category: NetSuite, Technical | 10 Comments

10 Comments

  1. HIlde Van Heddegem
    Posted February 4, 2016 at 8:04 am | Permalink

    How to create the postings BE GAAP for inventory
    Next to asset account and vendor account
    Extra posting 3 é 609 account
    Thanks

  2. Posted February 9, 2016 at 5:27 pm | Permalink

    Hello Hilde,

    I don’t believe I can offer you thinking. I have no background in BE GAAP requirements. However, if you can point me to an explanation of the outcome, I may be able to comment.

    Marty

  3. Gabriel Lawrence
    Posted August 17, 2016 at 10:32 am | Permalink

    Hi Marty, This looks like pretty close to what I was looking for, but instead of customer deposits I need the script to apply an unapplied payments / credit memos to new invoices. Do you have any experience using something similar with those two records?

    Thanks,

    Gabe

  4. Posted August 25, 2016 at 6:22 pm | Permalink

    Hi Gabriel,

    Yes, we are experts in the customer deposit area but we have done extensive work with unapplied payments and auto applying credit memos. If you can describe the way you want things applied, we can script it.

    Marty

  5. Edgar Moran
    Posted November 2, 2016 at 5:48 am | Permalink

    Hi Marty,

    To set the JE as approved, can be do it by default on creation or should be when it is updated? So far It seems I can set the credit list as the JE is available only when it has been approved. Thanks

  6. Posted November 3, 2016 at 5:31 am | Permalink

    Hello Edgar,

    Have you seen this article which discusses automating journal entries?
    http://blog.prolecto.com/2013/05/01/how-to-script-to-automate-netsuite-journal-entries/

    Marty

  7. Amy
    Posted July 20, 2017 at 1:35 pm | Permalink

    Hi Marty,
    In the scenario that both the bill and bill payment or invoice and payment are all recorded as Journal Entries, is there a way to mass apply them to each other to close them out?

  8. Posted August 3, 2017 at 12:54 pm | Permalink

    Hi Amy,

    Yes, via script you could produce that application using a Mass Update. So long as you can emulate the process with the UI, you should be able to script it. We have done this for clients where they have many old records that were not properly applied.

    Marty

  9. Rajitha
    Posted November 26, 2017 at 9:36 am | Permalink

    Hi Marty,

    Can we create the invoice from the billable expense record?

  10. Posted December 9, 2017 at 3:39 pm | Permalink

    You can set a billable expense with the name of a customer / project and then, when creating invoices, the billable expense line will become a candidate to invoice during the next invoice attempt for that customer / project.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>