How to create a query that resembles a previously saved search using the Postman REST API.

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' " }

Leave a comment

Your email address will not be published. Required fields are marked *