BAQ doesn't show Orders without Lines

Epicor ERP 10
1

Hey folks,

I have a quick question that I have never been able to figure out and have not tried in sometime, but need the data now.

I am trying to run a BAQ that shows me Sales Orders without Lines. Meaning the Sales Order was created, but no lines were added.

I added the partnum field and did a criteria partnum=isnull and the report returns 0 rows. I tried adding an =empty string instead of =insnull, but that doesn’t work either.

Does anyone know if it can be done and if so, how?

2

You can use a subquery and filter that subquery by orderline=null

select
[SubQuery1].[OrderHed_OrderNum] as [OrderHed_OrderNum],
[SubQuery1].[OrderDtl_OrderLine] as [OrderDtl_OrderLine]
from (select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine]
from Erp.OrderHed as OrderHed
left outer join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum) as SubQuery1
where (SubQuery1.OrderDtl_OrderLine is null)

3

I will try that

4

@ahsalloum Use the EXCEPT operator in the BAQ. Try it like this:

image

Use OrderHed in your top-level, and OrderDtl in the subquery, and set the subquery as Except:
image

5

Well I noticed there is a column OrderHed.TotalLines !!!
image

Couldnt you use a condition where TotalLines = 0 ??

Pierre

6

Yes, that works. That is an easy way to do what I am wanting, but I need to learn Subqueries for more complex BAQs. I am not yet live with E10 and Subqueries are new to me.

7

Is there some good documentation on Subqueries? I am new to E10 and I could use some studying time to get good at it.

8

@Hogardy @ahsalloum I was also unaware of the TotalLines column, but when I queried that and joined with OrderDtl, I got some interesting results…
image

YMMV