SmartLists - specific manufacturer

[JulieAndrus]

Is it possible to create a Newsletter SmartList for Customers who ordered from a specific manufacturer or a product from a specific category? (Similar to the to Smartlist for "Customers who ordered a specific product.")

My objective is to send out a newsletter/promotion announcing a sale on all items from a specific manufacturer or a specific category.

Yes?

[cbsteven]

I'm 95% sure this is possible, it will just be a pretty tricky SQL query for the SmartList. Once that is somewhat beyond my ability to do without sitting down and looking at a bunch of SQL syntax examples. But yes, it should be possible.

[jcocking]

Try these. They will get you close. The first one is by manufacture. You will need to use the manufacture ID number. The second will give you by category ID number. There is the order date fields to give a date range to work from. You will need to define which order status codes you want. Right now it is pulling status 1 through 7.

SELECT DISTINCT orders.oemail AS email
FROM orders LEFT JOIN (oitems LEFT JOIN products ON oitems.catalogid = products.catalogid) ON orders.orderid = oitems.orderid
WHERE (((orders.order_status) In (1,2,3,4,5,6,7)) AND ((products.manufacturer)=[field3]) AND ((orders.odate) between [field1] and [field2]));

SELECT DISTINCT orders.oemail AS email
FROM orders LEFT JOIN (oitems LEFT JOIN product_category ON oitems.catalogid = product_category.catalogid) ON orders.orderid = oitems.orderid
WHERE (((orders.order_status) In (1,2,3,4,5,6,7)) AND ((product_category.categoryid)=[field3]) AND ((orders.odate) between [field1] and [field2]) ); 

Code provide free of charge and thus has no warranty or support. Use at your own risk. No guarantee on how well it works since I did it off the top of my head.

[JulieAndrus]

Thanks so much. I'll give it a shot.