BAQ- Filtering Out Unreleased Jobs without Losing Sales Orders

Epicor ERP 10
1

I am creating a BAQ to act as a Pick By Day report to know what materials to pick for different jobs and sales orders. I want to only see released jobs. I am using the PartDtl table as it contains nearly all of the information that is needed to get the material requirements for orders or jobs (or POs but we dont care about those for this report). I thought I could use a matching rows join with the JobHead table with table criteria indicating ‘JobReleased = 1’. The problem is I have to join these tables with the JobNum as the table relation and a matching row join would then filter out all of the sales orders because they do not have JobNum.

I then thought I would simply use JobReleased as a display field. Then, in a dashboard, I could use grid filters to hide all rows that are false for this column. This again hides the SOs as they are also false for this value.

How else can I filter out unreleased jobs while still keeping the sales order information in my BAQ?

Thanks in advance.
Jakob

2

Use a LEFT outer join between PartDtl and JobHead. In newer versions of E10, the relationship type actually spells out what will happen. Something like:

image

You want “All rows from PartDtl”

You’ll get a blank value for JobHead fields when there’s no job. And a Order number 0 when there’s no order.

image

1 Like
3

I have done that but I would like to only show jobs that are released. If I add table criteria on JobHead (JobReleased = 1), it still shows all the unreleased jobs as well because it is showing ‘All rows from PartDtl’. At the moment it is joined with PartNum as the table relation.

What is odd though is if I add table criteria to JobHead that states ‘JobClosed = 0’ then it only shows the open jobs. This seems to work but I cannot get the JobReleased to work in the same way.

4

I have another somewhat similar issue as well. I would also like to filter out requirements that have Assembly sequence of 0 and Job sequence of 0 (0/0 upper level materials). I would also like to exclude POs but still include Sales Orders. Here is what I have as table criteria for the PartDtl table but this is not correct as I am getting materials for many different dates.

*I will take out the criteria that dictates the due date as a constant date but for testing the BAQ it should work.

image
image1109×669 43.1 KB

5

Add a subQuery criteria. And you no longer need the table criteria on JobHead.

Something like:

image
image803×179 4.34 KB

Without that SubQ criteria I get all records. Note that P/N AN-0005 has a SO demand and two Job Demands (one released and one not)

image

With the criteria I get

image

1 Like
6

Thanks Calvin. That has helped a lot. I haven’t played with how to use Subquery criteria very much.

Jakob

7

I think of the SubQuery criteria as a way to filter the results of the query, after any table and/or relationship conditions are applied.

1 Like