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?
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.
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.