How to get Remaining Amount of Customer Deposits in Saved Search (includes SuiteScript code)

Question

I need to get the Remaining Balance of all Customer Deposits linked to a specific Sales Order

Remaining Balance = the available/unapplied amount

Below, I will show you how I solved this in a saved search. As a bonus, I'll also include the SuiteScript 1.0 code to do the same.

Answer 1

To show the Remaining Amount of all Customer Deposits linked to a specific Sales Order record:

Create a Transaction Saved Search as follows:

• Criteria (Use Expressions)

  • ( Type IS Deposit Application AND
  • Created From Fields > Sales Order IS Sales Order #xyz ) OR
  • ( Type IS Customer Deposit AND
  • Created From IS Sales Order #xyz )

• Results

  • Formula (Numeric) (Summary type SUM): CASE WHEN {type} = 'Customer Deposit' THEN {debitamount} ELSE -{creditamount} END

BONUS, SuiteScript 1.0 code to get Remaining Amount of all Customer Deposits linked to a specific Sales Order record:

function customerDepositsRemainingBalance(salesorder_internalid) {
    var filters = [[["type","anyof","DepAppl"],"AND",["createdfrom.salesorder","anyof",salesorder_internalid]],"OR",[["type","anyof","CustDep"],"AND",["createdfrom","anyof",salesorder_internalid]]];
    var columns = [new nlobjSearchColumn('formulanumeric',null,'SUM')];
    columns[0].setFormula("CASE WHEN {type} = 'Customer Deposit' THEN {debitamount} ELSE -{creditamount} END");
    var search = nlapiSearchRecord('transaction',null,filters,columns);

    if(search == null) return 0;
    return Number(search[0].getValue(columns[0]));
}

// Example Usage
balance = customerDepositsRemainingBalance(3247434); // returns 50
balance = customerDepositsRemainingBalance(3256644); // returns 0