Multi-table updates for Epicor implementation and beyond
As part of Epicor ERP’s overall implementation and optimization toolset, the Data Management Tool (DMT) is a fundamental aspect of a successful implementation strategy. It gives you the ability to load, update, and even remove data. These capabilities allow you to cleanly load the setup and master file data as part of an environment build activity prior to go-live. When a user imports data, DMT keeps upload and update behaviors in check so that data migration is efficient and effective.
Epicor DMT also enables you to load live transactional records such as sales orders and purchase orders. These are essential to a successful cutover. Better yet, DMT possesses the ability to maintain records and improve the system’s data integrity long after the system is live. This allows an Epicor admin to efficiently clean up and even optimize the master file. This benefit also applies to transactional files in the live environment.
The Epicor ERP “Part” Routine
Within this context, one especially helpful capability involves the ability not only to update multiple columns of a table but to also update multiple related table records at once. For example, DMT’s “Part” routine allows for the creation and maintenance of records existing in the “Part” table. But DMT’s “Part Combined” routine allows not only for the creation of Part records. It also provides the ability to simultaneously add and/or update related Part Site (PartPlant), Part Warehouse (PartWhse), and Part Revision (PartRev) records. One could update these records individually, but Epicor provides the ability to perform multiple row updates in a single pass.
Sometimes DMT necessitates a multi-table setup in order to load and update data
For instance, the Resource table requires that you utilize the Resource Group routine to make updates to the Resource table. Let’s assume that you are the Epicor admin for your company. What if your operations manager decides to forgo using Epicor’s finite scheduling functionality? As such, you need to run an update to uncheck the Resource.Finite flag on all active resources. Should you search for a routine in DMT to update the “Resource” table, you will discover that none are available. Only the “Resource Group” load program is available:
- Primary Table: The field names are sufficient—given that the program knows the table context, the parent table (in this case, the “ResourceGroup” table).
- Child Table: Epicor utilizes a [ChildTableName]#[ChildFieldName] convention when defining the fields for the child table to be utilized.
For example, should you click the “Required” fields button for the “Resource Group” load, you will discover that to update the “Resource” table, you will need a number of key Resource Group fields but also the “Resource#ResourceID” and the “Resource#ResourceDescription” fields:
This is also evident when using the “Template Builder…” functionality to create a load template.
Given the above scenario, I opted to include the “Resource#Finite” field when creating the load template:
- Company
- Plant
- JCDept
- ResourceGrpID
- Description
- Resource#ResourceID
- Resource#Description
- Resource#Finite
As is the convention with Epicor’s Data Management Tool loads, I entered my data into a spreadsheet:
Finally, by loading the spreadsheet file into DMT, I can now perform the necessary update. The file, as defined below, loads without error:
Epicor’s Data Management Tool is replete with capabilities.
However, these features that are not always well documented or communicated. But with a little foreign key fiddling and a few Epicor consulting friends, DMT can be of great assistance to your Epicor ERP implementation.
