Epicor 10 BAQ - How to Pull Parent Part with Highest Issued Qty - by Mtl Part Num?

Amay · July 22, 2019, 10:46pm

Hi, I’ve been going a little nuts trying to figure out a way to get the results I want for a BAQ I’m writing.

I want to have a query that displays material (component) part num, parent part num, and the issued quantity for all jobs. I’ve used the JobHead and JobMtl tables to do this. I want my end result to be - by material part - the parent part that had the highest quantity issued. So every material part that has an issued quantity should only be listed once with the parent part it was issued to most.

To do this I first used the sum(JobMtl.IssuedQty) expression and group by function for a subquery:

This gave me a sum of all issued quantity of material part numbers by parent part number. Now in the next subquery I want the parent part that had the highest issued qty of each material part. For example I want only rows 1 and 3 in the sum subquery results below.

I can’t figure out how to do this. I was trying to find a way to use the top function for taking the first(highest issued quantity) result of each material part number but have had no luck with research. Any ideas?

Banderson · July 22, 2019, 11:24pm

You need to to another aggregate function. On this level use max(field). Just like you did the sums on the first level.

Amay · July 23, 2019, 2:20pm

That’s the first thing I tried to do. I created another sub query with the sum subquery in it, used group by and max(MtlPart.Calculated_IssuedQty) as my calculated quantity (just to be clear MtlPart.Calculated_IssuedQty = sum(MtlPart.IssuedQty) from the sum subquery). Unfortunately, it didn’t do anything.

I think it doesn’t work because I have both the material part number and the parent part number displayed. If I only have the material part number displayed I get the results I want but then I of course don’t have the parent part number. I’ve tried this and then linking another table that has the parent part number to my main subquery but I don’t know how to get a 1 to 1 relationship between the two because my options for table relationships are so limited.

Banderson · July 23, 2019, 2:24pm

What do you mean by “it didn’t do anything”? Can you post a screen shot of your results?

My guess would be you have the sum field included in the group by, by don’t fully understand what you are talking about with your description. I need more info.

Amay · July 23, 2019, 2:36pm

Sure thing, I’ll try to walk through it. So I have the subquery where I use the sum aggregate function:

I then created another subquery and put the sum subquery in it:

In this subquery I used the max function on my summed issued quantity results from the sum subquery.

After doing this I get the same results as if I only had used the sum subquery .

Hope that clears up what I’m trying to say.

Banderson · July 23, 2019, 2:39pm

Can you show me your subquery settings? I’m wondering if you have the subquery set as the top level.

Also, just for clarity, can you make your calculated fields different names? That might help.

EDIT I just noticed that your parent part numbers were not the same, (I thought they were). The query is acting as I would expect. You need the max for each mtl part number right? And you want to see which parent part number goes with that max? Use a windowing function.

I’ll get you the code in a second.

knash · July 23, 2019, 2:47pm

Create a SubQuery

Table JobMtl

Select the following fields
Company
JobNum
AssemblySeq

group by on Company, JobNum, and AssemblySeq

Create Calculated Field
SUM (Issuesd QTY)

create MAIN query
Add JobAsmbl table
Add Subquery

Join the tables on
Company
JobNum
AssemblySeq

Select fields you want to see.

Banderson · July 23, 2019, 2:51pm

Calculated field.

Remove your group by’s and for the calculated field do this.

max(SumMtlPart.Calculated_IssueQty) Over (partition by [loc], [Mtl Part Number])

Obviously replace your fields with the correct names.

Then in your subquery criteria, set it so the summed quantity equals the max qty.

Amay · July 23, 2019, 3:24pm

Okay, I removed the group by’s and used the partition by equation:

I then made a subquery criteria where the sum qty calculated field equaled the max qty calculated field:

but I get this error when I ran the query:

knash · July 23, 2019, 3:31pm

What is the objective?

Total Material Issued by parent part (job and asm) by Child material part?

Most Material Issued by parent part (job and asm) by Child material part?

Or Both?

Amay · July 23, 2019, 3:38pm

Ken, the objective is that for each child part, get the parent part that it was issued to the most.

knash · July 23, 2019, 3:59pm

Create a sub query with just the JobMtl Table

select Compalny, JobNum, AssemblySeq, PartNum, IssuedQty

create a calculated field (int)
ROW_NUMBER() OVER(PARTITION BY JobMtl.PartNum ORDER BY JobMtl.IssuedQty DESC)

Now create a main query
select the subquery you just created
add table selection to only show row = 1

  select  * 
from  (select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
	(ROW_NUMBER() OVER(PARTITION BY JobMtl.PartNum ORDER BY JobMtl.IssuedQty DESC)) as [Calculated_Row]
from Erp.JobMtl as JobMtl)  as JobMtlRowRank
where (JobMtlRowRank.Calculated_Row = 1)

this is all jobmtl ever. You will need to add the business logic; ie. open jobs, open assemblies, etc…

Amay · July 23, 2019, 5:26pm

It finally works! Thanks so much for both of your help with this, I really appreciate it. Nash, that formula you shared was really useful.

A quick recap for anyone looking to do something similar and like me is not exactly a BAQ expert:

I first used the sum aggregation subquery:

I pulled this subquery into another subquery where I then used Nash’s formula to create the “Row” calculated field but also included Company after the partition by section, thanks to seeing Brandon do this:

I then put this subquery into the final top level subquery and used Table Criteria = 1 constant for the calculated Row field:

and voilà, here it is:

Thanks again guys, I would’ve never been able to figure this out without your help.