CMS Customer Filter for customers with a specific item on Back Order

From NewHaven Software Wiki

Jump to: navigation, search

Customer filter overview

Some of your operators may want to retrieve a list of all customers that have a specific product on Back Order. They may also need to send a mass email to all these customers to let them know about:

  • the availability of the Back Ordered Item
  • the Date the back order is expected to be available
  • the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A custom mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

NOTE : This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

Actual SQL filters

The following SQL filter is the simple version (with no email validation). It will return all customers with unfulfilled orders for the specified Back Ordered item, whether or not they have a valid email address.

SELECT DISTINCT C.* 
FROM CUST C INNER join orderhdr o ON c.id = o.cust_num
            INNER join itemsord i ON o.order_id = i.order_id
WHERE not o.proforma = 1
      and not o.tempsave = 1
      and i.cur_onbo > 0
      and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')


This second version of the SQL filter will return all customers with unfulfilled orders for the specified Back Ordered item who also have a valid email address (with email validation)

SELECT DISTINCT C.* 
FROM CUST C INNER join orderhdr o ON c.id = o.cust_num
            INNER join itemsord i ON o.order_id = i.order_id
WHERE not o.proforma = 1
      and not o.tempsave = 1
      and i.cur_onbo > 0
      and not (EMAIL <> '' AND NOT
       (
       CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
       AND  LEFT(LTRIM([EMAIL]),1) <> '@'
       AND  RIGHT(RTRIM([EMAIL]),1) <> '.'
       AND  datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
       AND  (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
       ))
      and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
Personal tools