0

enter image description hereenter image description hereI’m trying to build an item saved search which can show total quantities sold by couple order types, plus couple columns which will show how much available quantities are at couple locations. For order types I have these two formula columns which work fine:

CASE WHEN {transaction.type} = 'Sales Order' AND {transaction.custbody1} = 'Direct' THEN {transaction.Quantity} end CASE WHEN {transaction.type} = 'Sales Order' AND {transaction.custbody1} <> 'Direct' THEN {transaction.Quantity} end

Above formulas work just fine until I try to add inventory columns and the entire saved search then displays incorrect numbers: It happens when I try to add something like this:

CASE WHEN {inventorylocation} = '1.Wholesale Fulfillment' THEN {locationquantityavailable} ELSE 0 END

Any insight what I’m doing incorrect here? Any input is greatly appreciated. Thanks!

Imran
  • 93
  • 2
  • 15
  • Can you show a more complete query? I suspect that when you add the inventory you are also adding an extra join. Is that right? – TomC Sep 25 '18 at 06:23
  • Sample data, desired results, and a proper database tag would all help. – Gordon Linoff Sep 25 '18 at 11:34
  • I've added screen shots of both criteria and results on what I've built so far...hopefully it helps. Basically, I'm trying to build saved search to show quantity sales of items, plus current quantity available to sell. Every time I try to add quantity available to sell (in red), the numbers go wonky. I've tried both item search and transaction search. Thanks! – Imran Sep 25 '18 at 14:53

0 Answers0