I'm trying to write a suitelet for the qty of pcs between two different date ranges. My saved search results pull the information just fine. The values being pulled out in the script are different.
Here is the search created in the script:
function getWebOrdersSearch() {
var getWebOrders = search.create({
type: "salesorder",
settings:[{"name":"consolidationtype","value":"ACCTTYPE"}],
filters:
[
["type","anyof","SalesOrd"],
"AND",
["mainline","is","F"],
"AND",
["formulanumeric: case when {otherrefnum} like '%web%' or {otherrefnum} like '%Web%' or {otherrefnum} like '%WEB%' then 1 else 0 end","equalto","1"],
"AND",
["status","noneof","SalesOrd:C"],
"AND",
["shipping","is","F"],
"AND",
["taxline","is","F"],
"AND",
["trandate","notbefore","8/1/2023"],
"AND",
["formulanumeric: case when {quantityshiprecv}={quantity} then 1 else 0 end","equalto","1"]
],
columns:
[
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "case when {custcol_scm_itemsub_original_item} is not null then {custcol_scm_itemsub_original_item} else {item} end",
label: "Formula (Text)"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {trandate} >= TO_DATE('8/1/2023', 'MM/DD/YYYY') AND {trandate} <= TO_DATE('8/1/2024', 'MM/DD/YYYY') THEN {quantity} ELSE 0 END",
label: "Total Web Sales QTY between 8/1/2023 thru 8/1/2024"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {trandate} BETWEEN TO_DATE('8/1/2023', 'MM/DD/YYYY') AND TO_DATE('8/1/2024', 'MM/DD/YYYY') THEN {quantity} ELSE 0 END / 12",
label: "8/1/2023 thru 8/1/2024 Monthly AVG"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {trandate} BETWEEN TO_DATE('8/2/2024', 'MM/DD/YYYY') AND TO_DATE('8/1/2025', 'MM/DD/YYYY') THEN {quantity} ELSE 0 END",
label: "Total Web Sales QTY after 8/1/2024"
})
]
});//end search create
return getWebOrders;
}//end function
Here is where I pull the results:
var getWebOrders = getWebOrdersSearch().run().getRange({
start: 0,
end: 1000
});
var getWebOrdersResults = getWebOrders.length;
for (i = 0; i < getWebOrdersResults; i++ ){
var itemText = getWebOrders[i].getValue({
name: "formulatext",
summary: "GROUP",
formula: "case when {custcol_scm_itemsub_original_item} is not null then {custcol_scm_itemsub_original_item} else {item} end",
label: "Formula (Text)"
});
var twentyThree = getWebOrders[i].getValue({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {trandate} >= TO_DATE('8/1/2023', 'MM/DD/YYYY') AND {trandate} <= TO_DATE('8/1/2024', 'MM/DD/YYYY') THEN {quantity} ELSE 0 END",
label: "Total Web Sales QTY between 8/1/2023 thru 8/1/2024"
});
var twentyThreeAvg = getWebOrders[i].getValue({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN {trandate} BETWEEN TO_DATE('8/1/2023', 'MM/DD/YYYY') AND TO_DATE('8/1/2024', 'MM/DD/YYYY') THEN {quantity} ELSE 0 END / 12",
label: "8/1/2023 thru 8/1/2024 Monthly AVG"
});
Any help on where I'm going wrong would be appreciated.
Thanks!
