Microsoft Dynamics AX: Item Data Management in a Franchise Environment

By - February 1, 2017

Microsoft Dynamics AX uses many tables and fields to house item data and perform Dynamics AX operations. This is useful for achieving a great deal of flexibility with item setup, allowing 1:n relationships for important pieces of data like bar codes and external vendor IDs but it can pose challenges. While companies with small to moderate item catalogs and no franchises could feasibly manage their Item data via standard processes, this can be a difficult task for companies with larger item catalogs and franchise companies. Imagine trying to manage item data for a catalog of 50,000 items and 100+ franchises. What if one company needs a slightly different setup than the other companies, for example a distribution company? Without an intelligent approach, it will be a challenge to manage item data by the end user without the use of IT running DIXF or SQL updates. An intelligent plan for item management will make it possible for a merchandising team to be self-reliant.

One Field at a Time

A strategy to manage item data should be decided per field. If the field is going to contain the same value across all companies, it may make sense to add the field to the EcoResProduct table and add custom code to synchronize the custom EcoResProduct field across all InventTable records. This can be accomplished by leveraging the EcoResProduct.getCompaniesWhereReleased() method when a field is modified and using it to synchronize values from the EcoResProduct table.

What if the field is consistent for a group of items but may change on a company by company basis? In this case, it may be useful to use the Retail product hierarchy form (Retail –> Setup –> Product categories –> Retail product hierarchy) to manage this item data. The Retail product hierarchy can make updates across many companies and many items at once and apply them in batches. It can also carry a unique value for each company if needed. For example, if an over-delivery percentage of 20% is acceptable to one company, but only 10% is acceptable to another, then that field can be added to the Basic product properties of the form.


Always remember to share

The next strategy needed is for managing item data for auxiliary item data, such as external item ID, approved vendors, and bar codes. Fortunately, these are the easiest to manage across companies, and can be accomplished by setting up a Virtual Company Account and adding all legal entities to the virtual company account. I recommend using VCA over modifying the table to set “Save data per company” to “No” for the reason that the DAX POS requires the table to have a data area ID to be synchronized. Some tables that are a great candidate to be shared by VCA are:

  • Barcode and Barcode setup table
  • Physical dimensions table
  • Approved vendor table
  • External item ID table
  • Item Group table
  • Item Model group table

By maximizing the amount of data shared across companies, you can reduce table size and improve ease of management.

Automate, automate

The final piece of the puzzle is streamlining the item creation process. Leveraging the Retail product hierarchy is a great first step, since new items will inherit item data from the retail product hierarchy upon release. However, the process can still be cumbersome and arduous. Since many retailers gather most item information when working with vendors to introduce new items, a practical solution may be to use an excel uploader tool to import the worksheet the retailer is already using to import items. This can be used to import into a staging table for review, before the user releases into Dynamics AX. The staging table can be flat with fields to house auxiliary item table data, or it can have auxiliary staging tables, such as a staging table for the Physical dimensions. By creating a system to automate common fields with the Retail Product Hierarchy, and then getting the vendor to populate the data in a spreadsheet that is loaded into Dynamics AX, the majority of the heavy lifting should be done without much effort. The merchandising team will mostly just need to normalize the data the vendor sends them.

Managing a large item catalog across many companies can certainly present some challenges, however intelligent planning and considerations into the way the data will be utilized can give the team some tools to make the task manageable, mostly leveraging existing Dynamics AX tools (Virtual Company Accounts, Retail Product Hierarchy). Other strategies can be implement with minimal effort, such as adding data or fields to the Retail Product Hierarchy or propagating specific field data from the EcoResProduct table to the InventTable.

For more information about how to get the most out of Microsoft Dynamics AX, subscribe to our Microsoft Dynamics Community News publication.

by Curtis McDonald for RSM

Receive Posts by Email

Subscribe and receive notifications of new posts by email.