I am struggling with what I think should be a simple query - Orders without Jobs.
We build to stock, so jobs are not linked to Sales Orders. I want to find orders where there are no jobs for the part in OrderDtl.
I have tables OrderHed, OrderDtl and JobHead. I have an outer join from OrderDtl to JobHead Company and Partnum is the relationship. Criteria in JobNum ISNULL.
When I run it, the jobnum field is null, but there ARE jobs for that partnum.
Thanks in advance.
for each OrderHed no-lock where OrderHed.OpenOrder = True , each OrderDtl no-lock where (OrderHed.Company = OrderDtl.Company and OrderHed.OrderNum = OrderDtl.OrderNum ) and OrderDtl.OpenLine = 1 And OrderDtl.PartNum BEGINS ‘70400’ , each JobHead no-lock outer-join where (OrderDtl.Company = JobHead.Company and OrderDtl.PartNum = JobHead.PartNum ) and ISNULL(JobHead.JobNum) by OrderHed.Company by OrderHed.OrderNum .