BAQ Help - [Count All Parts Scheduled in Future Jobs AND Sales Orders]

Epicor ERP 10
1

I am having a bit of a grey-matter obstacle on what seems, at least to me, to be a fairly easy BAQ task.

I’d like to get all of our distinct parts, by ascending part number and perform a simple count of [1] how many times they appear in upcoming scheduled jobs and [2] how many times they have been placed on sales orders with a future request date. The date range would be parameterized so that the person running the BAQ can select how far out they wish to look.

On the surface, this seems pretty easy to configure, but for some reason, I’m struggling with how to approach this.

I had originaly started with the Erp.Part table; then, believing that the counts for jobs and sales orders would not calculate unless Erp.JobHead and Erp.OrderDtl were in separate subqueries, I linked those tables back to Erp.Part in their own subquery. This may, or may not be sensible and someone may want to provide me with some guidance there.

As far as the counts are concerned, I attempted to partition the counts over the Erp.Part.PartNum field because these were to be distinct counts for each part:

COUNT(1) OVER(PARTITION BY Part.PartNum)

… but I’m not exactly sure that this is the best way.

As far as returned values (or output), all I’m really looking for is the distinct part number list along with the separate counts as outlined above. Perhaps, like so:

(Let's assume this is for a 02/01/2019 to 05/01/2019 date range):

PartNum		TotalJobs	TotalOrders
-------		---------	-----------
A-1		    9		     2
A-2		    0		     1
B-1		   13		     0
C-1		   41		     1
C-2		    6		    30
D-1		    2		     8

I’m having some trouble knowing if this approach is sound/sensible - and - actually getting the counts in the same query. It seems every time I try to separately link the Erp.JobHead and the Erp.OrderDtl subqueries back to Erp.Part, the output becomes “wonky” and doesn’t validate against SQL query validation.

What advice/guidance might there be for obtaining counts of part numbers that are expected to exist in two different tables?

2

You will need a left join to your part number table, so don’t forget that, also, you are doing too much work in you calculated field. You want to group the part number with the checkbox and then just do a count(field) in your calculated field.

3

Would this work for you? Two subqueries. One with just the orderdtl table, one with just the jobhead table. Filter the tables based on your parameters so they return only the rows you need. Have two fields displayed, partnum and a calculated field, Count(pick any field). Group By on part number.

Then, on your top level. Use Part as your table. Bring in both sub queries linked on part. Return all values from part. Display Part.PartNum and the calculated count field from both sub queries.

If you need a zero instead of a null, then do a calculated field on the top level that is if null then 0 otherwise the fields value.

4

All of the above answers for the calculations:

plus/or i would suggest looking into the PartDtl table instead of JobHead and OrderDtl tables.

PartDtl.SourceFile = ‘JH’ gives you all top level job parts that are open
partDtl.SouceFile = ‘OR’ gives you all sales order parts that are open
PartDtl.SouceFile = 'JM" gives you all job material components that are open.

You could group by part and do a count or sum on partdtl.InvtyQty to sum open qtys.
you could probably link those subqueries back to the part table with outer join.

5

I agree with @althomas. Also, you may be able to simply use the PartWhse table to get the Order and Job Demand…