[Dan Cantley]

I am trying to figure out how a sub-component of a BOM that has multiple versions (the component) can be found (AX2012-R3)?

Lets say I have a BOM B12345 and I have a component 23456 that has 10 versions (10 different subBOMs).  I use version B23456V3.  But when I look at the BOM table, I see that BOM B12345 uses component 23456, but I don’t see how I can link it (through SQL to the version I want – B23456V3).  Right now I pull in all 10 because I don’t know how to only select only the correct subcomponent BOM for that BOM 

Can someone point me in the correct link direction? 

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Plano TX
——————————

[Dan Cantley]

Never mind.  I see how to do it now.  The BOMversion table works in reverse of how I thought it did.  It is all clear to me now.

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Plano TX
——————————
——————————————-

[Paul Challinor]

Hi Dan,

The unique key to each BOM Version is the combination of Company, Item, Site, BeginDate and End Date.  So when joining to the BOM Version table you’ll have to specific values for each field that return a single unique value.

——————————
Paul Challinor
Merit Solutions
Naperville IL
——————————
——————————————-

[Dan Cantley]

Thanks Paul,

I will look into this.  I thought I figure it out by matching InventIDimId’s – which worked for many of the BOMs, but not all – probably a coincidence because of the way we structure our BOM versions.  I have been tearing my hair out trying to figure out how to match the sub level version to the levels above, but haven’t figured it out despite reading a bunch of web pages trying to figure this out – Plenty of sites telling me how to enter BOM’s, but nothing that explains the structure and relationships between the tables to get what I want.  

I will post back if this works or not. 

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Plano TX
——————————
——————————————-

[Dan Cantley]

Doesn’t work for us for some reason.  We have for instance an Item – 10001021 that has 10 different BOM versions:

BOM00000005

BOM00000006

BOM00000007

BOM00000008

BOM00000009

BOM00000010

BOM00003950

BOM00004536

BOM00004536

BOM00004727

– and they all have the same company, site, begin date and end date and all active and approved.

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Plano TX
——————————
——————————————-

[Adam Wilch]

Dan,

There is one additional key field for BOM Versions.  In addition to Company, Item, Site, BeginDate and End Date, you have to look at the from quantity. 

AX will allow an item to have multiple approved BOM’s, but only one active bom for a given date range and quantity.  The only way to have 10 BOM versions approved and active is if they are all active for a different production quantity.  The from quantity is (BOMVersion).FromQty.

Adam

——————————
Adam Wilch
Bush Brothers & Company
Knoxville TN
——————————
——————————————-

[Dan Cantley]

I finally got it to take the correct path.  I was working from someone else’s T-SQL code and missed something important.  I thought I was comparing the required fields in the InventDimId from the SubBom’s Version to the Bom’s InventDimId (can’t compare the inventDimId’s because they can be different if one or the other has warehouse), but a different InventDimId field wasn’t being passed in the recursive CTE.  I added the proper InventDimId, and it all started working.  Thanks for the help.

——————————
Dan Cantley
Systems Analyst
Bedrock Manufacturing Co, LLC
Plano TX
——————————
——————————————-