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.