Smartlist for orders but exclude orders that contain certain items
I’m trying to figure out a way to create a smartlist for all orders-but exclude orders that contain certain items. The problem is i would want to exclude the whole order not just the one line that has the item. Any thoughts or ideas?
Interesting question – can’t say that I’ve heard of this one before but I’m sure that in another context (ie not Sales orders and items) maybe done something similar.
So I wrote this, seems to work. You would just use this as your main “table” and it would give you all of the SOP “header” data.
select a.* from slbSalesOrders a
left outer join
select SOPTYPE,SOPNUMBE from slbSalesOrderLines
where ITEMNMBR in (‘ACCS-HDS-1EAR’,’BK MOUSE’)
group by SOPTYPE,SOPNUMBE) b
on a.SOPTYPE= b.SOPTYPE and a.SOPNUMBE = b.SOPNUMBE
where b.SOPNUMBE is null
You could also just the SOP10100 and SOP10200 tables if you don’t care about SOP History.
In the where clause, we are LOOKING for the items you want to exclude (seems counterintuitive but that’s how it works). Once it finds those SOP documents to exclude, we requery the table and then look for the records NOT in that list (thus the where SOPNUMBE is null clause)
This gives you all the SOP Documents that do not have one of those items.
If you would like to submit an answer or comment, please sign in to the eOne portal.