BAQ to show highest MTL seq on BOM

As the title says, I’m looking to write or filter a BAQ to show me only the highest material sequence listed on that part’s approved BOM. I already have it filtered down to only show me a specific list of Part BOM’s and only if that revision is approved, but it will dump out all of the listed materials and I’m trying to use this export to allow me to build a DMT import to add a new material line to said list of parts.

Unless I’m missing something, to add a material line to an existing BOM (using the DMT tool) you need to identify a sequence number so if you want to just add them at the end you need to know the highest number on each existing BOM correct?

Group by PartNum (and your other Part or PartRev fields) and then create a calculated field set to max(PartMtl.MtlSeq).

3 Likes

Create a subquery. Have the fields PartMtl.Company, PartMtl.PartNum and a calculated field Max(PartMtl.MtlSeq). Group by PartMtl.Company and PartMtl.PartNum. Then, link that back to your main query by Company and Part.

From a performance perspective, I’d add some criteria on the subquery that matches your main query, so your subquery might get a little thicker. Such as linking PartMtl back to PartRev and including only approved Revs. I’m not a query execution path expert, but my guess is if you have a wide open subquery, that will create a dataset that includes ALL PartMtl, which if you have a lot of history might include a lot more data than you want.

Yikes… that took some stumbling around and testing to get right… First time I’ve ever used the group by clause section in the BAQ’s.

Thank you for the suggestion.

This sounds a bit more complex than what I needed. The reply from jtownsend was helpful and got me the list I needed, PN, the Rev, and the highest MTL sequence number on that rev.