Backstory: I have a BAQ that is used to grab an indented BOM for a part. When they search for the part, they are able to use a wildcard so they can return back multiple boms at once. We wanted a way to make sure we knew where one bom ended and another began. Currently, I have this setup here. [ima…

[image] Anthony_Mattice: This would work if my company would use the correct MTL seq setup, instead of using 1 in some places and 10 in others. Instead of using the actual material number, you can use the windowing function to give you a row number ordered by the material seq. example: ROW_…

You may want to add that the bom level is 1 for displaying if you do not have a way to keep the top parent part.

I suppose but that still brings me back to my original issue, which is that both IAQ-REF**** and 510-037 are BOM Level 1, and are both showing the top level assembly. I only want it to show once.

And since my mtl sequence doesn’t match up over all parts, I was hoping there was some way to distinguish what the first part that is returned is. Or, we could possibly still use the Min Mtl sequence? I just need to know, IF BOMLEVEL = 1 and PartMtl.MtlPartNum = Min(MtlSeq). Not sure the syntax on t…

What about a window function similar to a previous suggestion usin min() over (partition by) ?

[image] CSmith: (partition by) I’m open to anything, I’m just not sure how to implement it.

See if this resource helps: [image] Query Problem in SQL ERP 10 That’s a Windowing Function, and they are super helpful in the right instances.

@Anthony_Mattice Here is an example that @timshuwy posted with his exploded bom query that has a calculated Indented view that may give you some clues on how to determine when [image] Build BOM in Query ERP 10 Are you trying to do it via SQL or as a BAQ?

I looked into this as well while trying to get my BAQ to work originally. I never was able to get his BAQ up and running in our system. This would work if my company would use the correct MTL seq setup, instead of using 1 in some places and 10 in others.

@CSmith so maybe something like this? I know this is not correct but I don’t know how to make everything align properly. Also, when I check syntax it says possible SQL injection. Will the BAQ allow me to use this? SELECT PartMtl_MtlSeq, MIN(PartMtl_MtlSeq) OVER (PARTITION BY PartMtl_MtlPart…

Your calculated field something like: CASE WHEN BOMLEVEL = 1 AND PartMtl.MtlSeq = Min(MtlSeq) OVER (PARTITION BY PartMtl.PartNum) THEN PartMtl.PartNum ELSE '' END I haven’t examined your query very well, but something like this should work for you in determining when to display the PartNum.

BAQ Question for keeping results separate

Epicor ERP 10

CSmith (Clint Smith) January 26, 2023, 5:16pm 26

See if this resource helps:

Topic		Replies	Views
BAQ calculated field to show part number of parent assembly Epicor ERP 10	15	4864	February 11, 2025
Indented BOM will no longer go any more than 2 levels deep into an assembly Epicor ERP 10 baq	12	1167	October 19, 2023
Creating a Part Search for BAQ Epicor ERP 10 baq	54	7973	January 18, 2023
BOM baq Kinetic ERP baq , bom	57	7585	August 2, 2021
Indented BOM search by Material Part Epicor ERP 10 e10 , epicor , baq	18	2434	September 2, 2024

BAQ Question for keeping results separate

Related topics