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?
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.
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.