# Epicor BAQ Recursive BOM Join on PartMtl, Previous Operation Complete and Stale Headers > Source: https://netray.co/erp-help/epicor-baq-recursive-bom-join-partmtl > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer A BAQ cannot run a recursive query, so a multi-level bill of materials is built by joining PartMtl to itself once per level, or by using a function or the BOM explosion tools for unlimited depth. Previous operation complete uses a subquery against JobOper. Stale column headers usually come from the saved dashboard layout, not the BAQ. ## Kind advanced ## System epicor ## Erp Epicor Kinetic ## Module BAQ / BOM and Operations ## Query epicor baq how to recursive join mtlparts ## Alternate Queries - epicor baq multi level bom partmtl - epicor baq previous operation complete - table relations epicor baq - epicor baq table headers not updating name ## Applies To Epicor Kinetic and 10.x. Table and field names are from the standard schema; verify in the Data Dictionary Viewer for your release. ## Steps ### How to build a multi-level BOM BAQ **Items:** - Create a BAQ with PartMtl as the first table and give it an alias such as Level1. - Add PartMtl again with alias Level2 and set the relation: Level2.PartNum = Level1.MtlPartNum, plus Company, and revision and alternate method as your data requires. - Set the relation to left outer so parts without sub-components remain. - Repeat for as many levels as your deepest BOM needs (usually three to six). - Add Part joins to show description and type for each level. - Filter to the top-level part with a parameter and, if needed, only released revisions. - Test against your deepest BOM. If depth is unknown, use a function for the explosion. ## Sections ### Why no true recursion, and the join-per-level pattern **Body:** T-SQL has recursive common table expressions, but the BAQ designer builds a fixed query from tables and subqueries, so there is no recursive form. The standard workaround is a fixed number of self joins of PartMtl, each aliased, where level two's parent is level one's component. Each level adds a left join, and query cost rises quickly with depth. Two caveats. PartMtl is the engineering BOM for a part and revision, so you must match the revision and alternate method or you will see several versions. For manufactured jobs, JobMtl holds the actual job material, which may differ from the engineering BOM. ### Previous operation complete **Body:** To test whether the prior operation is complete, join JobOper to a subquery that finds the previous OprSeq for the same job and assembly: the highest OprSeq lower than the current one. Then compare the OpComplete flag of that row. A subquery of type top 1 ordered by OprSeq descending does this. Show operations ready to run as those where the previous operation is complete or does not exist, and the current one is not. Use ISNULL on the subquery result so the first operation, which has no previous one, passes. **Code:** SELECT o.JobNum, o.OprSeq, o.OpComplete, ISNULL((SELECT TOP 1 p.OpComplete FROM Erp.JobOper p WHERE p.Company = o.Company AND p.JobNum = o.JobNum AND p.AssemblySeq = o.AssemblySeq AND p.OprSeq < o.OprSeq ORDER BY p.OprSeq DESC), 1) AS PrevComplete FROM Erp.JobOper o ### Table relations and stale headers **Body:** Table relations in Query Builder define the join: type (inner or outer), and the field pairs. If row counts look wrong, open each relation and confirm Company is included and the pairs match the real key. An unintended cross join looks like a huge duplicate result. When a changed field label does not show, check where the header is coming from. A saved dashboard or grid layout keeps the column caption it stored, so the BAQ edit does not appear until the layout is reset or the dashboard is updated. Also confirm you changed the display label, not the field name, saved the BAQ, and refreshed the cache by reopening the dashboard. If the problem persists, remove the column from the dashboard grid and add it back. ## Pitfalls - Joining PartMtl without revision and alternate method and getting duplicate versions. - Building ten levels of joins and slowing the database for every run. - Treating the engineering BOM as the as-built job material. - Forgetting that the first operation has no predecessor. - Editing the BAQ label and expecting a dashboard with a saved layout to change. ## Ai Angle Self-join chains and subqueries are easy to get wrong by hand. An assistant that knows the schema can draft the join chain for the number of levels you need and flag a missing revision condition. Your developer checks it against a known BOM before use. On-prem deployment keeps engineering data private. ## Faq ### Can an Epicor BAQ do a recursive BOM explosion? **Answer:** Not directly. Use a fixed number of PartMtl self joins, or use a function or the standard BOM tools when depth is unknown or large. ### How do I check previous operation complete in a BAQ? **Answer:** Join JobOper to a top 1 subquery for the preceding OprSeq of the same job and assembly, and test its OpComplete flag, treating no previous row as complete. ### Why are my BAQ column headers not updating? **Answer:** The dashboard or grid layout often stores its own captions. Reset the layout or re-add the column after changing the BAQ display label and saving. ### What is a table relation in a BAQ? **Answer:** It is the join between two tables in the query: the join type and the field pairs. Include Company and the full key to avoid duplicate rows. ## Search terms epicor baq recursive join, epicor baq partmtl, epicor multi level bom baq, epicor baq previous operation complete, epicor baq table relations, epicor baq header not updating, epicor jobmtl baq, epicor joboper baq --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json