Item Pricing Saved search

Requirement

Need to generate a search that delivers a full listing of all our items and prices for each pricing level and quantity break.

Solution

Create an Item saved search by utilizing the Pricing Field

Note: The columns are configured based on the existing Price levels, and they are hardcoded in the search. If there are any changes to Price levels in the future, the search will need to be updated accordingly.

var itemSearchObj = search.create({
   type: "item",
   filters:
   [
      ["name","startswith",""], 
      "AND", 
      ["type","anyof","@ALL@"], 
      "AND", 
      ["pricing.pricelevel","noneof","@NONE@","2","3","6","4","5","-1"], 
      "AND", 
      ["isinactive","is","F"]
   ],
   columns:
   [
      search.createColumn({
         name: "type",
         summary: "GROUP",
         label: "Type"
      }),
      search.createColumn({
         name: "itemid",
         summary: "GROUP",
         sort: search.Sort.ASC,
         label: "Name"
      }),
      search.createColumn({
         name: "displayname",
         summary: "GROUP",
         label: "Display Name"
      }),
      search.createColumn({
         name: "averagecost",
         summary: "MAX",
         label: "Average Cost"
      }),
      search.createColumn({
         name: "currency",
         join: "pricing",
         summary: "GROUP",
         label: "Currency"
      }),
      search.createColumn({
         name: "quantityrange",
         join: "pricing",
         summary: "GROUP",
         label: "Quantity Range"
      }),
      search.createColumn({
         name: "minimumquantity",
         join: "pricing",
         summary: "GROUP",
         sort: search.Sort.ASC,
         label: "Minimum Quantity"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "MAX",
         formula: "CASE WHEN {pricing.pricelevel} = 'Base Price' THEN {pricing.unitprice} ELSE NULL END",
         label: "Base Price"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "MAX",
         formula: "CASE WHEN {pricing.pricelevel} = 'L 2 - Price' THEN {pricing.unitprice} ELSE NULL END",
         label: "L 2 - Contr1"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "MAX",
         formula: "CASE WHEN {pricing.pricelevel} = 'L 3 - Price' THEN {pricing.unitprice} ELSE NULL END",
         label: "L 3 - Contr2"
      })
   ]
});
var searchResultCount = itemSearchObj.runPaged().count;
log.debug("itemSearchObj result count",searchResultCount);
itemSearchObj.run().each(function(result){
   // .run().each has a limit of 4,000 results
   return true;
});


/*
itemSearchObj.id="customsearch1705064986747";
itemSearchObj.title="JJ Price Realignment by Item Search PFGN-25 V2.0 (copy)";
var newSearchId = itemSearchObj.save();
*/