When we create a customer deposit saved search we cannot differentiate whether the amount has been refunded or applied to an invoice. so in the customer deposit search we won’t be able to get the two data. then we have tried search on deposit application. in the deposit application search we are able to see the corresponding amount of deposits , the amount applied, amount refunded and the total net remaining amount to be applied. The demerit of this would be we will not be able to get the details of customer deposit that doesn’t have a deposit application or not yet applied to any of the transaction.
In the customer deposit search if we set the criteria to be created from a particular sales order then the remaining lines will be cut off as the sales orders only linked to mainline.
samples are show created is exported and shown below
var transactionSearchObj = search.create({
type: “transaction”,
filters:
[
[[“type”,”anyof”,”DepAppl”],”AND”,[“createdfrom.createdfrom”,”anyof”,”15141″],”AND”,[“mainline”,”is”,”F”]]
],
columns:
[
search.createColumn({
name: “createdfrom”,
join: “createdFrom”,
summary: “GROUP”,
label: “SO#”
}),
search.createColumn({
name: “createdfrom”,
summary: “GROUP”,
label: “Created From”
}),
search.createColumn({
name: “amount”,
join: “createdFrom”,
summary: “MAX”,
label: “Total Deposit Amount”
}),
search.createColumn({
name: “formulacurrency”,
summary: “SUM”,
formula: “case when {paidtransaction.type} != ‘Customer Refund’ then {paidamount} else 0 end”,
label: “Paid Amount”
}),
search.createColumn({
name: “formulacurrency”,
summary: “SUM”,
formula: “case when {paidtransaction.type}= ‘Customer Refund’ then {paidamount} else 0 end”,
label: “Refund Amount”
}),
search.createColumn({
name: “formulacurrency”,
summary: “SUM”,
formula: “MAX({createdfrom.amount})- SUM(case when {paidtransaction.type} != ‘Customer Refund’ then {paidamount} else 0 end)- SUM(case when {paidtransaction.type}= ‘Customer Refund’ then {paidamount} else 0 end)”,
label: “Remaining Amount in Deposit”
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug(“transactionSearchObj result count”,searchResultCount);
transactionSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
/*
transactionSearchObj.id=”customsearch1679476845685″;
transactionSearchObj.title=”Transaction Search 2 from deposit (copy)”;
var newSearchId = transactionSearchObj.save();
*/
Demerit: If the customer deposit that is not yet applied to any transaction will be eliminated from this search as this is an deposit application search
