var salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type","anyof","SalesOrd"],
"AND",
["mainline","is","F"],
"AND",
["status","anyof","SalesOrd:B"],
"AND",
["shipping","is","F"],
"AND",
["taxline","is","F"],
"AND",
["item.type","noneof","Discount","Description"],
"AND",
["formulanumeric: {quantity}-{quantityshiprecv}","notequalto","0"],
"AND",
["custbody_rv01_approved_to_send_to_mai","is","T"],
"AND",
["location","anyof","14"],
"AND",
["quantitycommitted","greaterthan","0"],
"AND",
["name","noneof","5368"]
],
columns:
[
search.createColumn({name: "custbody_rv01_approved_to_send_to_mai", label: "Approved to Send to MAI"}),
search.createColumn({name: "entity", label: "Name"}),
search.createColumn({name: "line", label: "Line ID"}),
search.createColumn({
name: "internalid",
sort: search.Sort.ASC,
label: "Internal ID"
}),
search.createColumn({name: "tranid", label: "SO#"}),
search.createColumn({name: "otherrefnum", label: "PO/Check Number"}),
search.createColumn({name: "custbody_rv01_internal_memo", label: "Internal Memo"}),
search.createColumn({name: "trandate", label: "Order Date"}),
search.createColumn({name: "shipdate", label: "Requested Ship Date"}),
search.createColumn({
name: "internalid",
join: "item",
label: "Internal ID"
}),
search.createColumn({
name: "itemid",
join: "item",
label: "Name"
}),
search.createColumn({
name: "salesdescription",
join: "item",
label: "Description"
}),
search.createColumn({name: "quantity", label: "Order Quantity"}),
search.createColumn({
name: "formulanumeric",
formula: " {quantity}-{quantityshiprecv}",
label: "Qty Remaining"
}),
search.createColumn({name: "quantitycommitted", label: "Quantity Committed"}),
search.createColumn({name: "shipmethod", label: "Ship Via"}),
search.createColumn({name: "shipaddressee", label: "Shipping Addressee"}),
search.createColumn({name: "shippingattention", label: "Shipping Attention"}),
search.createColumn({name: "shipaddress1", label: "Shipping Address 1"}),
search.createColumn({name: "shipaddress2", label: "Shipping Address 2"}),
search.createColumn({name: "shipaddress3", label: "Shipping Address 3"}),
search.createColumn({name: "shipcity", label: "Shipping City"}),
search.createColumn({name: "shipstate", label: "Shipping State/Province"}),
search.createColumn({name: "shipzip", label: "Shipping Zip"}),
search.createColumn({name: "shipcountrycode", label: "Shipping Country Code"}),
search.createColumn({name: "entity", label: "Name"}),
search.createColumn({name: "statusref", label: "Order Status"}),
search.createColumn({name: "location", label: "Location"}),
search.createColumn({name: "shipcomplete", label: "Ship Complete"}),
search.createColumn({
name: "attention",
join: "shippingAddress",
label: " Attention"
}),
search.createColumn({name: "custbody_revo_desired_ship_method", label: "Desired Shipping Method"}),
search.createColumn({name: "custcol_nm_ss_order_id", label: "ShipStation Order Id"})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count",searchResultCount);
salesorderSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
/*
salesorderSearchObj.id="customsearch1695992026550";
salesorderSearchObj.title="MAI UPLOAD - 2 (copy)";
var newSearchId = salesorderSearchObj.save();
*/
QUERY
{ "q": "SELECT p.displayname,s.addrphone,s.addr2,s.zip,s.addr1,s.addressee,s.addrtext,s.attention,s.city, s.country,s.state, i.itemid, j.item,i.description, t.id,t.custbody_rv01_approved_to_send_to_mai, t.shipcomplete, t.otherrefnum, t.entity, t.tranid, t.trandate, t.status,t.custbody_rv01_internal_memo, t.shipdate,t.custbody_revo_desired_ship_method, j.location,j.linesequencenumber ,ABS(j.quantity) AS Quantity,j.quantitycommitted,j.shipmethod,t.shipcarrier, j.custcol_nm_ss_order_id,ABS(j.quantity) - j.quantityshiprecv AS QtyRemaining FROM transaction AS t INNER JOIN transactionline AS j ON t.id = j.transaction INNER JOIN item AS i ON j.item = i.id INNER JOIN transactionshippingaddressbookentityaddress AS s ON t.shippingaddress = s.nkey INNER JOIN ShipItem AS p ON j.shipmethod = p.id WHERE t.recordtype='salesorder' AND t.status IN 'B' AND j.mainline='F' AND t.custbody_rv01_approved_to_send_to_mai='T' AND j.taxline='F' AND j.itemtype NOT IN ('Discount', 'Description') AND j.quantitycommitted > 0 AND j.entity !='5368' AND (ABS(j.quantity) - j.quantityshiprecv) <> 0 AND j.location IN '14' " }