Multiple Formula Columns
Tue Aug 08 2023 12:12:28 GMT+0000 (Coordinated Universal Time)
Saved by @mdfaizi
Example:
let getallDataBeforeDueandAfterDue = (listofCustomer, mainarraytoStoreAllJSON) => {
/**
* Invoice Search ||To Find Dues Based On Days Crossed
* https://6453995-sb2.app.netsuite.com/app/common/search/search.nl?id=768&e=T&cu=T&whence=
*/
try {
var invoiceSearchObj = search.create({
type: "invoice",
filters: [["type", "anyof", "CustInvc"], "AND", ["customermain.internalid", "anyof", listofCustomer], "AND", ["mainline", "is", "T"]],
columns: [
search.createColumn({
name: "internalid",
join: "customerMain",
summary: "GROUP",
label: "Customer Internal ID",
}),
//if u have multiple formula columns then u need to manually give different name to each formula column.As netsuite keep all columm name same.IMP
search.createColumn({
name: "formulanumeric_1",
summary: "SUM",
formula: "CASE WHEN {daysoverdue}>0 THEN {fxamountremaining} ELSE 0 END/*COMMENT: */",
label: "Formula (Numeric)",
}), //AR Overdue(all overdue amount by atleast 1day or more)
search.createColumn({
name: "formulanumeric_2",
summary: "SUM",
formula:
"CASE WHEN ROUND({duedate}-{today})>0 AND ROUND({duedate}-{today}) <=10 THEN {fxamountremaining} ELSE 0 END/*COMMENT:In this case duedate would always be more so we will do duedate -today to get value in positive */",
label: "Formula (Numeric)",
}), //Due Amount When 10days Left in Overdue
search.createColumn({
name: "formulanumeric_3",
summary: "SUM",
formula:
"CASE WHEN ROUND({today}-{duedate}) >=0 AND ROUND({today}-{duedate})<=15 THEN {fxamountremaining} ELSE 0 END/*COMMENT:In this case today would always be more so we will do today-duedate to get value in positive.Comman sense logic because if it's due then due date have already crossed ,so present date would be more than due date. */",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 0 to 15days
search.createColumn({
name: "formulanumeric_4",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=16 AND ROUND({today}-{duedate})<=30 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 16 to 30days
search.createColumn({
name: "formulanumeric_5",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=31 AND ROUND({today}-{duedate})<=60 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 31 to 60 days
search.createColumn({
name: "formulanumeric_6",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=61 AND ROUND({today}-{duedate})<=90 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 61 to 90 days
search.createColumn({
name: "formulanumeric_7",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=91 AND ROUND({today}-{duedate})<=180 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 91 to 180 days
search.createColumn({
name: "formulanumeric_8",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=181 AND ROUND({today}-{duedate})<=365 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue betweeen 181 to 365 days
search.createColumn({
name: "formulanumeric_9",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=365 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}), //Due Amount after Overdue more than 1 year
search.createColumn({
name: "formulanumeric_10",
summary: "SUM",
formula: "{fxamountremaining}",
label: "Formula (Numeric)",
}), //TOTAL AR IN PHP (just total of all invoice)
],
});
log.debug("invoiceSearchObj", invoiceSearchObj);
var searchResultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count", searchResultCount);
//get all the sum values and store it in mainarraytoStoreAllJSON
invoiceSearchObj.run().each(function (result) {
var arOverdueAmount = result.getValue({
name: "formulanumeric_1",
summary: "SUM",
formula: "CASE WHEN {daysoverdue}>0 THEN {fxamountremaining} ELSE 0 END/*COMMENT: */",
label: "Formula (Numeric)",
}); //AR Overdue(all overdue amount by atleast 1day or more)
var dueInNext0to10Days = result.getValue({
name: "formulanumeric_2",
summary: "SUM",
formula:
"CASE WHEN ROUND({duedate}-{today})>0 AND ROUND({duedate}-{today}) <=10 THEN {fxamountremaining} ELSE 0 END/*COMMENT:In this case duedate would always be more so we will do duedate -today to get value in positive */",
label: "Formula (Numeric)",
}); //Due Amount When 10days Left in Overdue
var dueInNext10to15Days = result.getValue({
name: "formulanumeric_3",
summary: "SUM",
formula:
"CASE WHEN ROUND({today}-{duedate}) >=0 AND ROUND({today}-{duedate})<=15 THEN {fxamountremaining} ELSE 0 END/*COMMENT:In this case today would always be more so we will do today-duedate to get value in positive.Comman sense logic because if it's due then due date have already crossed ,so present date would be more than due date. */",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 0 to 15days
var dueInNext16to30Days = result.getValue({
name: "formulanumeric_4",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=16 AND ROUND({today}-{duedate})<=30 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 16 to 30days
var dueInNext31to60Days = result.getValue({
name: "formulanumeric_5",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=31 AND ROUND({today}-{duedate})<=60 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 31 to 60 days
var dueInNext61to90Days = result.getValue({
name: "formulanumeric_6",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=61 AND ROUND({today}-{duedate})<=90 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 61 to 90 days
var dueInNext91to180Days = result.getValue({
name: "formulanumeric_7",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=91 AND ROUND({today}-{duedate})<=180 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 91 to 180 days
var dueInNext181to365Days = result.getValue({
name: "formulanumeric_8",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=181 AND ROUND({today}-{duedate})<=365 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue betweeen 181 to 365 days
var dueInNextMoreThan365Days = result.getValue({
name: "formulanumeric_9",
summary: "SUM",
formula: "CASE WHEN ROUND({today}-{duedate}) >=365 THEN {fxamountremaining} ELSE 0 END",
label: "Formula (Numeric)",
}); //Due Amount after Overdue more than 1 year
var totalARInPHP = result.getValue({
name: "formulanumeric_10",
summary: "SUM",
formula: "{fxamountremaining}",
label: "Formula (Numeric)",
}); //TOTAL AR IN PHP (just total of all invoice)
var customerInternalId = result.getValue({
name: "internalid",
join: "customerMain",
summary: "GROUP",
});
log.debug("customerInternalId", customerInternalId);
log.debug("arOverdueAmount", arOverdueAmount);
log.debug("dueInNext0to10Days", dueInNext0to10Days);
log.debug("dueInNext10to15Days", dueInNext10to15Days);
log.debug("dueInNext16to30Days", dueInNext16to30Days);
log.debug("dueInNext31to60Days", dueInNext31to60Days);
log.debug("dueInNext61to90Days", dueInNext61to90Days);
log.debug("dueInNext91to180Days", dueInNext91to180Days);
log.debug("dueInNext181to365Days", dueInNext181to365Days);
log.debug("dueInNextMoreThan365Days", dueInNextMoreThan365Days);
log.debug("totalARInPHP", totalARInPHP);
var index = mainarraytoStoreAllJSON.findIndex((x) => x.customerInternalid == customerInternalId); //finding the array index no where this customer json obj is stored. so that after getting the index we would be able to get the json object and add other key value pairs as added below.
if (index != -1) {
mainarraytoStoreAllJSON[index]["arOverdueAmount"] = arOverdueAmount;
mainarraytoStoreAllJSON[index]["dueInNext0to10Days"] = dueInNext0to10Days;
mainarraytoStoreAllJSON[index]["dueInNext10to15Days"] = dueInNext10to15Days;
mainarraytoStoreAllJSON[index]["dueInNext16to30Days"] = dueInNext16to30Days;
mainarraytoStoreAllJSON[index]["dueInNext31to60Days"] = dueInNext31to60Days;
mainarraytoStoreAllJSON[index]["dueInNext61to90Days"] = dueInNext61to90Days;
mainarraytoStoreAllJSON[index]["dueInNext91to180Days"] = dueInNext91to180Days;
mainarraytoStoreAllJSON[index]["dueInNext181to365Days"] = dueInNext181to365Days;
mainarraytoStoreAllJSON[index]["dueInNextMoreThan365Days"] = dueInNextMoreThan365Days;
mainarraytoStoreAllJSON[index]["totalARInPHP"] = totalARInPHP;
}
//log.debug("mainarraytoStoreAllJSON[index]", mainarraytoStoreAllJSON[index]);
return true;
});
log.debug("mainarraytoStoreAllJSON", mainarraytoStoreAllJSON);
return mainarraytoStoreAllJSON;
} catch (e) {
log.error("error", e);
}
};
Comments