Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Andrew Matthews]

Hello. This is going to come across somewhat rantish (because it kind of is), but I also have a couple of technical questions I would like to get answers to if I can.

Ours is a Dynamics AX 2009 environment, and I am currently working on a piece of code that involves the automation of BOMVersion (“BOM versions”) records and the BOM (“BOM lines”) records associated with them. The code mostly works, but I have run into a couple issues/oddities that I need some feedback on.

ISSUE/ODDITY 1: I ran my code that creates my BOMVersion and related BOM records, then viewed the BOMVersion in the BOM line form. Everything looked as it should. To be thorough I deleted the BOMVersion record from the BOM line form, then I re-ran my code and looked up the BOMVersion it created. Everything looked fine, except that there were twice as many BOM records as there should be; every BOM line I expected appeared twice. So I deleted the BOMVersion, re-ran my code, and looked the BOMVersion up again…and found that there were three times as many BOM records as there should be. Upon inspection of Data DictionaryTablesBOMVersionDeleteActions I discovered there was nothing to cleanup BOM records. The SYS layer implementation of Data DictionaryTablesBOMVersionMethodsdelete also contains no BOM cleanup logic. This promts me to ask the following questions:

• WHY?!?
• I can pound out some X++ code to delete the related BOM records (by joining on BOMId) easily enough, but are there any hidden caveats I should be aware of? This is a less incendiary phrasing of my “WHY?!?” question.

ISSUE/ODDITY 2: While inspecting the BOMVersion table, I stumbled across the ItemIdx index, which is a unique index (it does not allow duplicates). The index contains the following fields: ItemId, BOMId, and – get this – RecId. The BOM table has a similar unique index (NumIdx, comprised of BOMId, LineNum, RecId), and to clarify, these exist on the SYS layer (meaning they’re not something I decided to add or tweak). In case you haven’t picked up on why this seems odd, the inclusion of RecId (which is already always unique) in a unique index essentially emasculates it, rendering it incapable of actually enforcing uniqueness in any useful sense. Anyway, upon seeing this, I am torn between one of two conclusions:

• Whoever did this is a genius, and is somehow leveraging these emasculated unique indexes for performance gains.
• Whoever did this was high on life (and by “life” I mean a psychotropic compound) when they did it.

Which one of these conclusions is correct? Is this in fact a very clever performance tweak? On the other hand, if this is in fact broken, what are the ramifications of “fixing” it (removing RecId from the indexes)? Will “fixing” it render all things BOM horribly, horribly broken?

——————————
Andrew Matthews
Cayman Chemical Company
Ann Arbor MI
——————————

[David Smith]

I see what you did there.

——————————
David Smith
Key Technology, Inc
Walla Walla WA
——————————
——————————————-

[Andrew Matthews]

Hello all,

I want to share an exercise that anyone can perform to see what I’m seeing, and hopefully give me some feedback:

• Open the BOM form (Inventory management module, Common Forms -> Bills of materials), and create a new BOM record with a descriptive name. I named mine “BOM Diggity”, but you could name yours “Atom BOM” or something else descriptive. Save your changes and close the form.
• Open the Item form (Inventory management module, Common Forms -> Item details), and select an item of item type BOM. Then click the BOM -> Lines menu button item on the right hand side to open the BOM line form.
• In the BOM line form, click in the Versions grid at the top to focus it, then create a new BOMVersion record. For the BOM field enter “BOM Diggity” (or whatever the name of your BOM record is) and save the changes.
• With the record you just created selected, click in the Overview grid at the bottom to focus it, then create a new BOM record. Just enter an item number and a quantity and save the changes. Create a couple of BOM records in this fashion.
• Still in the BOM line form, click in the Versions grid at the top to focus it, then create a new BOMVersion record. For the BOM field enter “BOM Diggity” (or whatever the name of your BOM record is), then save the changes.
• Take note of how the BOMVersion record you just created inherits the BOM records from the previous BOMVersion you just created.
• Play around a bit with adding, deleting, and modifying the BOM records, and take note of how the changes are reflected across both BOMVersion records.

So given what we have seen above, my questions are as follows:

• Can anyone give me a compelling reason why this behavior is desirable, or for that matter, anything other than completely unacceptable?
• If different BOMVersion records reporting to the same BOMTable record (by virtue of the fact that they share a BOMId) must all contain the same lines, then what is the point, really, of having multiple BOMVersion records for a BOMId at all?
• Why must BOM records be related to all BOMVersion records sharing a BOMId, as opposed to only one specific BOMVersion record?
• Alternatively, should BOMVersion’s BOMIdx (the BOMId and ItemId fields) disallow duplicates (the SYS level definition currently allows them)?

——————————
Andrew Matthews
Cayman Chemical Company
Ann Arbor MI
——————————
——————————————-

[Evert Bos]

Yes, Jonathan says it precisely. I am adding one other thing.
For most customers, no, for all customers that I know, the first learning curve with the AX BOM functionality is hard because they are not used to having one Item master potentially being connected to many BOM (versions).
But now something interesting.
1 Native AX does not have BOM revision control. What many manufacturers , using AX, have doine though is to use these BOM Versions to respresent different revisions.
Often an interface with a PDM system will import a new BOM version for each new Revision.
2 The fact that in your test you use an auto generated BOM id is certainly possible. Most manufacturing companies I know do not do that. THey make that Bom version ID equal to the part number, then underscore, then revision letter. This is how the PDM interface creates the new BOM versions in AX. The end result is rather attractive for these companies: one has a separate BOM for each revision, and can follow the evolution of a BOM quite easily although we still lack a “BOM Compare” feature in AX. 
When older revisions have to be produced in the prod module, it is very easy to select an older BOM version.

3. Creating new BOM’s manually is typically not done the way you do it. Much better and more intuitive to create when on the ITem, then click BOM lines and create a new BOM version. (if using number sequence , do not click NEW, because you will get stuck, but click Create New BOm version.
4. I have never seen the BOM versions being used where many different items are connected to th same BOM versions although this is certainly what the design suggests you can do. Maybe in other industries I have not seen… ?? Jonathan’s example makes sense but I have not experienced it.
5.I completely understand your experience but I sincerely wish you will go and try again. After  the learning curve , customers I know did not have too many gripes. THey love the Designer view of the BOM, for example, and the option to easily go back to older BOm versions. We are still waiting for BOm compare and automatic component substitution, which the Formula does have, which creates some discrete envy.().

——————————
Evert Bos
Sikich LLP
Naperville IL
——————————
——————————————-

[Andrew Matthews]

Jonathan, Evart, et al:

Thank you for disambiguating (at least partially) this functionality for me.  Thank you all, also, for bearing with me in my frustration.  I think I have all the answers I need to move ahead.

——————————
Andrew Matthews
Cayman Chemical Company
Ann Arbor MI
——————————
——————————————-

[Jonathan Anderson]

Andrew,

I believe your understanding of a BOM version is incorrect and that is what is leading to the confusion.  In AX, a BOM version is the link between the BOM and the item.  It is NOT a different revision or version of a single BOM for a different item.  All items that use the same BOM number MUST have the same BOM lines.  This is done so that one BOM can be used for multiple items and if this BOM is changed, it is updated on all the items it is used for.  This is useful in manufacturing processes where different items are made from the same amount of raw material just using a different manufacturing process, such as pieces of wood that are cut from a larger piece and then routed to have a different design on them (think cabinet doors).  

This is the reasoning behind having both a BOM and a BOM version in AX.  

In order to have a different revision of a BOM, you must have a different BOM ID.

Jonathan

——————————
Jonathan Anderson, Managing Consultant
BKD LLP
Denver CO
——————————
——————————————-

[Archive User]

Sounds to me that your code is written incorrectly.  Standard AX has a many to many relationship between BOMTable and InventTable which is maintained by the BOMVersion table.  If your code is always creating a BOMVersion record and BOM (Not BOMTable records but BOM Table) records than I would expect what you describe to happen.   

Deleting a BOMVersion record doesn’t delete the BOM or BOMTable records because those records could be attached to other items.

I would advise that you adapt your code to check for existing records before you create anything.

——————————
Matt Dickson
Business Analyst
Lawrenceville GA
——————————
——————————————-

[Andrew Matthews]

“Deleting a BOMVersion record doesn’t delete the BOM or BOMTable records because those records could be attached to other items.”

Matt,

I will preface what I am about to write with two things:

• There is a world of difference between could and should.
• I’m not picking on you. Really, I’m not.

Anyway…I crafted my code based on the names of the tables and the behavior of the formula line form (FormsBOMConsistOf) itself and the hierarchy they imply:

• BOMTable: Labeled “Bills of materials”.
• BOMVersion: Labeled “BOM versions”, which implies that it is used to hold different versions of a “Bills of material” (BOMTable) record.
• BOM: Labeled “BOM lines”. Since my initial posting I have discovered that BOM has a SYS layer relation BOM.BOMId == BOMVersion.BOMId, which means a BOM’s creation/existence depends on the existence of said BOMVersion records. This also implies that BOM records *should* be associated with BOMVersion records directly, and should not be associated with BOMTable records directly (though they would be circuitously by virtue of the fact that the BOMVersion is associated with the BOMTable).
• FormsBOMConsistsOf: Labeled “Formula line”, this is a form containing two grids. The top grid presents BOMVersion records; the bottom grid presents BOM records relevant to the selected BOMVersion record above. This also implies that BOM records should be associated with BOMVersion records directly, and should not be associated with BOMTable records directly.

Given this information the following hierarchy is implied pretty heavily:

• Each item (InventTable) record has:
  • 0, 1, or more bills of materials (BOMTable) records related to it, each of which has:
    • 0, 1, or more BOM versions (BOMVersion) records related to it, each of which has:
      • 0, 1, or more BOM lines (BOM) records related to it, each of which has:
        • 0, 1, or more [ArbitraryTable] records from any number of disparate tables related to it.

Now I understand why deleting a BOMVersion record doesn’t delete the related BOMTable, and as such I have refrained from adding delete() code and/or a delete action to BOMVersion to do so. But why should deleting the BOMVersion record not result in the related BOM records also being deleted? Or, to put it another way, why should a BOM record ever be directly associated to anything other than a BOMVersion record? All of the evidence thus far seems to suggest the following:

• The whole bill of materials implementation is ham-fisted, and the table relations are more akin to the database equivalent of an orgy (dirty/unsafe/nothing good ever comes of it) than they are to proper, well-defined one-to-many or many-to-many relations.
• A such, despite how things currently function, a BOM record is intended to be directly associated with a single BOMVersion record, and only a single BOMVersion record, denoted as BOM -> BOMVersion.
• Said BOM -> BOMVersion relation does not preclude the existence of other [SomeRecord] -> BOM relations.
• When said BOMVersion record is deleted, all said BOM records should also be deleted.
• When deleting a BOMVersion record, If any related BOM records cannot be deleted, deletion of said BOMVersion record should also be prevented.

Now I don’t claim to be a super-educated systems architect with decades of ERP development experience. I also know I shouldn’t be throwing around terms like “orgy” and “ham-fisted” lightly, or for that matter suggesting architectural changes in a cavalier fashion. But to borrow from the title of a 2003 UFO documentary, I know what I saw, and based on what I have seen, I am lead to believe that the BOM -> BOMVersion relation (and related constraints) needs to be implemented in order to enforce sanity and structure. How does this strike everyone? Is it reasonable? Our environment is AX 2009; has this architecture changed at all in AX 2012?

——————————
Andrew Matthews
Cayman Chemical Company
Ann Arbor MI
——————————
——————————————-

[Colby Gallagher]

Andrew, 
  It sounds like your assumptions of what a BOMVersion is might be causing confusion:

”

• BOMVersion: Labeled “BOM versions”, which implies that it is used to hold different versions of a “Bills of material” (BOMTable) record.”

In AX, a BOMVersion is really just the relationship between BOMTable and InventTable.  Since BOMTable records can exist without ever being associated with an InventTable record.  This makes sense to me as sometimes I have an engineering BOMTable record that I dont actually make into inventory items and thus I don’t need an InventTable associated with it.  

It might help if you use the BOMTable form at Inventory Mgmt -> Common -> Bill of Materials.  

•   The top of the form is BOMTable records. 
• The “Lines” button on the top of the form gets you to the BOM records that correspond to the BOMTable you have selected. 
• The bottom of the form shows the BOMVersions that exist for this BOMTable.  

In terms of AX 2009 vs. AX 2012, BOM’s haven’t really changed except that 2012 allows for Formula’s (process manufacturing) standard, where in 2009 this required an add-on from Fullscope.  

——————————
Colby Gallagher
Manufacturing Systems Consultant
Agility Business Solutions
Brecksville OH
——————————
——————————————-

[Andrew Matthews]

Colby,

I concede that my understanding of the BOM ecosystem is somewhat flawed and/or incomplete. I’ll explain what I am trying to do again, and my code is attached so you can see exactly how I’m trying to do it. Hopefully someone can point out how it is broken and tell me how to “make it go.”

Anyway… I am adding functionality to the item master form, accessible via a Formula -> import formula menu button, to allow users to quickly create formulas with 50+ item/config/quantity lines. When the user clicks the button they are presented with a dialog that asks for the following information:

• Input file: A path to a CSV file containing the ItemId, ConfigId, and Qty headers and corresponding data.
• Configuration (optional): A ConfigId drop-down.
• Activate formula after creation: A NoYes checkbox.

After the user clicks OK, I read in the dialog parameters and massage the CSV contents into an array of Map objects. I, of course, do lots and lots of integrity checking on the CSV file contents. After I have completed said parameter aggregation I then call the following method which I have added to the InventTable record:

• public server void createFormula(ConfigId _configId, Array _formulaLines, boolean _activateFormula = false)

The _configId and _activateFormula parameters should be pretty self-explanatory. The _formulaLines parameter is an array of Map(Types::String, Types::String) objects, each of which contains the “ItemId”, “ConfigId”, and “Qty” keys, corresponding to the lines read from the CSV input file. In brief, this method does the following:

• Drafts a BOMId derived from “[ItemId] [_configId]”, or “[ItemId] BASE” if an empty _configId was supplied.
• Locates a BOMTable matching said BOMId, or creates one if it does not exist.
• Creates a BOMVersion initialized from the InventTable record and said BOMTable record.
• For each Map in _formulaLines:
  • Creates a BOM record having said BOMId and associated with the InventTable record and configured per the item, configuration, and quantity values specified in said Map.
• Optionally flags the BOMVersion record as active and approved if the _activateFormula parameter is true.
• Optionally flags the BOMTable record as approved if it is a new BOMTable record (one the method just created) and the _activateFormula parameter is true.

As I have previously stated, this all works as expected…until I open up the formula line form (the Formula -> Formula menu button on the item master) and delete the BOMVersion record I just created. After doing so and re-importing the same exact formula, when I view the formula I see the new BOM records as well as the old ones (that I believe should have been deleted when the corresponding BOMVersion record).

So that’s how the BOMVersion -> BOM relationship is working for me right now; here’s how I need it to work:

• When a BOM is created by this method, it needs to be explicitly (as opposed to surreptitiously or indeterministically) related to the BOMVersion in question. Looking at my code, I can tell there is no mechanism to do this. If such a mechanism does exists and someone could point it out to me and tell me how to use it, that would be great.
• The BOM must be related to the BOMVersion in question. By “the BOMVersion in question” I don’t mean any arbitrary BOMVersion that happens to have a matching BOMId (because there could be more than one), but rather the actual BOMVersion that was created at the same time as the BOM via the createFormula method.
• The BOM should be related to the BOMVersion-in-question and BOMTable records alone, and in our use case is not to be related to or latched onto by any other entities, ever (unless said entities are in turn only related to the BOMVersion-in-question and BOMTable records, I suppose). No relational orgies allowed; I can’t go for that (no can do).
• When a BOMVersion record is deleted, any related BOM records (as specified per the BOM -> BOMVersion-in-question relation) also need to be deleted.
• When a BOMVersion record is deleted, the related BOMTable record SHOULD NOT be deleted. Originally I looked into deleting the BOMTable record, bit quickly realized that it was a horrible, horrible idea that would break lots and lots of things.

So, based on what I have just described, is there already a way to accomplish what I need to do? Adding RefTableId and RefRecId fields to the BOM table and corresponding code in InventTable.createFormula and BOMVersion.delete would absolutely work, but it’s not exactly my “Plan A” approach. Is there currently a better way (or any way at all) to reign in these relationships?

——————————
Andrew Matthews
Cayman Chemical Company
Ann Arbor MI
——————————
——————————————-