BAQ earliest ship by date in job demand summary

Epicor ERP 10
1

I am trying to build a BAQ to show when a jobs Req By date does not match the date of the next open order to be shipped (earliest open Ship By date in the job demand summary)

What I want the row to return is:
Part #
Revision
Order #
Order Line
Order Release
Release Qty
Release Ship By date
Job #
Job Req By date

Currently I have a single SubQuery and is setup as follows.

Table List:

# Name(Alias) Schema Database Table Name
1 OrderRel Erp OrderRel
2 JobProd Erp JobProd
3 JobHead Erp JobHead

Table Relations:

  • OrderRel - JobProd
# And/Or ( Not OrderRel field or any expression Operation JobProd field or any expression )
1 FALSE Company = Company
2 And FALSE OrderNum = OrderNum
  • JobProd - JobHead
# And/Or ( Not JobProd field or any expression Operation JobHead field or any expression )
1 FALSE Company = Company
2 And FALSE JobNum = JobNum

Table Crietera:

  • OrderRel
And/Or ( Not Field Operation Filter Value )
FALSE OpenRelease = 1 constant
  • JobProd
And/Or ( Not Field Operation Filter Value )
FALSE OrderLine = OrderRel.OrderLine table field value
And FALSE OrderRelNum = OrderRel.OrderRelNum table field value
  • JobHead
And/Or ( Not Field Operation Filter Value )
FALSE JobReleased = 1 constant
And TRUE JobClosed = 1 constant
And TRUE ReqDueDate = OrderRel.ReqDate table field value

Display Fields:

JobHead_PartNum
JobHead_RevisionNum
OrderRel_OrderNum
OrderRel_OrderLine
OrderRel_OrderRelNum
OrderRel_SellingReqQty
OrderRel_ReqDate
JobProd_JobNum
JobHead_ReqDueDate

Sort Order:

JobProd_JobNum
OrderRel_ReqDate

Currently when testing, this results in returning rows for all releases on the job with a Ship By date that do not match the jobs Req By date as shown bellow.

image
image1608×806 98.5 KB

So far I have been unable to figure out how to only return the row with the earliest Ship By date related to that job. I have been unable to create a table criteria to achieve this. I have also tried setting this SubQuery to an inner query and creating a separate top level with a calculated field to get the min ReqDate from the current results but have not had any success with that either. If anyone has any insight as to how I might find a resolution to this or maybe a different approach all together I would appreciate the feedback.

2

You are almost there! Create a subquery that returns pretty much what you have in that image. Then remove the ShipBy date from the subquery, and add a calculated field = Min(ShipBy). Make sure to group by job num (and all the other no aggregated fields). Also make sure to remove any unneeded fields from the subquery to make sure you only return the job number and the minimum req date from the sales order. Then you can just link up the job number at the top level and return your calculated field from the subquery.

Good luck fellow ‘Nathan’!

3

Thanks for the recommendations Nate. Going to try this out sometime this week hopefully when I get the time. Will post an update after doing some more testing.