NetSuite: Saved Search to Show Sales Orders that Have Specific Promotions

Imagine having two promotions, whose coupon codes are "ItemPromo1" and "ItemPromo2". If you want to find sales orders who have both promotions applied, do the following:

1. Navigate to Lists > Search > Saved Searches > New
2. Click Transaction
3. Search Title: Enter a title
4. Click Criteria
5. Click Standard
6. Filter:
1. Select Main Line
1. Main Line: Select Yes
2. Click Set
2. Select Type
1. Type: Select Sales Order
2. Click Set
7. Click Summary
1. Summary Type: Select Maximum
• Field: Select Formula (Numeric)
1. Formula: Enter instr(NS_CONCAT(Distinct({promocode}||{promoapplicabilitystatus})),'ItemPromo1Applied')
2. Formula (Numeric): Select greater than
3. Value: Enter 0
2. Summary Type: Select Maximum
• Field: Select Formula (Numeric)
1. Formula: Enter instr(NS_CONCAT(Distinct({promocode}||{promoapplicabilitystatus})),'ItemPromo2Applied')
2. Formula (Numeric): Select greater than
3. Value: Enter 0
4. Click Set
8. Click Results
9. Click Remove All
10. Field: Select Document Number
• Summary Type: Select Group
11. Click Save & Run

 

Why does this work?

The formula uses NS_CONCAT to return a list of promo codes and their corresponding statuses. INSTR returns a number greater than 0 if a specific string is found within the NS_CONCAT's result. That string can be set to a specific promo code with its status appended to it (Example: ItemPromo1Applied), hence the same is found in the formula's INSTR's parameter.

If you want the search to return orders with both promotions applied, you will need to add the formula twice (once for each promotion), changing the INSTR parameters accordingly. You can actually do more than two promotions, applying the same logic.