Skip to content
+1-888-319-3663

COMMUNITY FORUM

Smartlist for orders but exclude orders that contain certain items

Stephanie asked 5 months ago

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?

Answers
Patrick Roth Staff answered 5 months ago

Stephanie,

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.

patrick

Stephanie replied 3 months ago

You’re a genius Patrick! Thank you!


If you would like to submit an answer or comment, please sign in to the eOne portal.