BAQ to search all lines

Epicor ERP 10
1

We have written BAQs so that teams can quickly see sales orders by line for orders related to their area.
The issue is there is some overlap depending on the mix of sales order lines. I am trying to write a baq that if the part number begins with a certain code on ANY LINE , it wont be displayed to certain departments. Also the opposite. If an order only has parts that begin with a certain code on ALL LINES, it is only displayed on thats department baq. Suggestions? thanks!

2

Create two BAQ’s and use the like function.

Example: Part.PartNum not like %XYZ%

This will exclude the given lines for that part number and you would just replicate for the next BAQ then pull them into a dashboard and have two different tabs.

3

Good idea … i will give that a try. thanks!

4

Actually, that did not solve the issue. Im trying to get the baq to not displaythe sales order at all if ANY of the lines meet a certain criteria. The soluiton posted will screen out the lines itself, im trying to screen out the whole sales order.

5

Maybe:

  1. Create a subquery.
  2. In the subquery display fields, add OrderNum and check “Group By”
  3. Create an integer calculated field with a case statement, something like
SUM(Case when Part.PartNum like '%XYZ%' then 1 else 0 end)
  1. Join that subquery in your main query

In the end, if the value of the calculated field is > 0, that sales order should not be displayed

6

Great idea… ill give it a go. thanks!

7

I would make a subquery of just the OrderDtl table. Add any rows that meet the criteria, and add OrderNum as the only display field. Check the “Group by” box on ordernum.

Then you can add a table filter to OrderHed or OrderDtl where OrderNum is NOT = ANY values from selected Subquery

8

Thanks Kevin and Mathieu, the subquery worked perfect. Took me awhile to figure out how to link the subquery to the main query, but once i did i see how subqueries will assist in a bunch of stuff we want to do…