Select Page
How to Update Multiple Tables in Epicor DMT

How to Update Multiple Tables in Epicor DMT

by | Sep 27, 2020 | Epicor, Epicor 10, Epicor Administration, ERP Benefits & Features

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.

Multiple Table Updates Data Management Tool Epicor

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:

Epicor ERP DMT Search Resource Group
DMT Engineering Resource Group
Fortunately, you can utilize the Resource Group load program to update the Resources related to all Resource Groups. Within Epicor’s Data Management Tool, multiple table update routines utilize a specific field naming convention to differentiate the primary table from its child tables:
 
  • 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:

multiple table updates in Epicor DMT resource group required 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:

DMT Resource Group Template Builder
To perform the ResourceGroup-Resource parent-child table update to the finite-scheduling field that I had intended, I now have a load template with the following fields, named as follows:
 
  • 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:

DMT Resource Group Template

Finally, by loading the spreadsheet file into DMT, I can now perform the necessary update. The file, as defined below, loads without error:

Multiple Table Updates in Epicor DMT Resource Group Finite Update

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.

Learn about Epicor cloud
Epicor ERP Event: EstesGroup Fall Summit 2020 (Video)

Epicor ERP Event: EstesGroup Fall Summit 2020 (Video)

by | Sep 15, 2020 | Epicor, Epicor 10, ERP Consulting

Epicor ERP Event

​An Epicor ERP Event to Begin a New Season

Are you ready for change? In June, we gathered friends and strangers together at an Epicor user event covering everything from master file setup to security to embedded customization. To further support this ERP community, we’re meeting again on October 7th for another Epicor event that will raise awareness of new techniques for manufacturers and distributors using ERP software solutions. This fall summit will serve to assist Epicor users with customization and optimization steps that are often overlooked by ERP project teams.

 

Epicor is a large and complex application and can be configured in a multitude of ways. Depending on how you’ve configured your application, different capabilities and different issues present themselves, and understanding how other Epicor customers have addressed these issues is often a great way to add perspective to your own challenges. On the technical front, the tools available for super users often span the skillsets of multiple roles, so for those of you who wear multiple hats by necessity, a better understanding of some of Epicor’s key technical foundations can assist in better maintaining and optimizing your installation. If you’ve experienced work culture shifts because of the pandemic, you might benefit from new Epicor consulting techniques that can address challenging project checkpoints (like conducting an Epicor CRP remotely, for example).

Making the Most of the Planning Workbench

Job management and scheduling are critical to any ERP implementation, but no two companies manage the work orders passing though their factories in quite the same way. Not surprisingly, Epicor offers a number of planning and scheduling tools that often go underutilized. Epicor’s Planning Workbench is one such tool, and integrating it into your suite of management tools can take a little fiddling, and its tendrils extend into Epicor’s part master setup, into its Time Phase and MRP programs, and even into Epicor’s underlying PartDtl table. In this session, we’ll be working though the logic underlying the Planning Workbench and demonstrating how it can be leveraged to keep ahead of quantity and timing issues on your shop floor.

 

SQL Server: Tips and Tricks For the Epicor Administrator

For those in the user community with Epicor experience that dates back to the days of 905 and earlier, the challenges of working with Epicor’s legacy backend are long remembered, like bad memories from another place and time. With the advent of Epicor’s E10 application, Epicor moved to an entirely Microsoft-centric stack, resulting in a much more stable, robust and scalable platform. And now that Epicor’s E10 ERP platform is built upon Microsoft’s SQL Server database engine, there are many new tricks and techniques that can be applied to best leverage the capabilities of the SQL Server RDBMS, while avoiding the common pitfalls of SQL Server administration. Understanding the principles of configuration, tuning and optimization at the database level can have a significant positive impact on your Epicor application’s performance and stability. In this SQL Server session, we’ll provide key insights to keeping your database server running smoothly.

 

SSRS Reporting: Modifying Standard Reports

Speaking further along the lines of Epicor’s Microsoft-centric stack, Epicor’s use of Microsoft’s SQL Server Reporting Services (SSRS) as its primary reporting platform allowed for tighter integration across the different elements of the application. But the move from Crystal Reports to SSRS was a significant one, given that SSRS differs significantly from Crystal in its fit, form, and function. For users unfamiliar with the paradigms underlying SSRS, simple things like adding a logo or a field to an existing report, in order to address the needs of a given company, are not always self-evident.  In this SSRS Reporting session, we’ll provide some steps that will allow you to make basic modifications to standard reports to help fit them effectively into your business.

​

Watch this ERP event

Watch the Epicor summit recording

Watch our Epicor ERP fall event preview to begin a new season of ERP solutions.

Planning Workbench 

Presented by Brad Feakes

SQL Server

Presented by Daryl Sirota

SSRS Reporting

Presented by Joe Trent

How to Manage Epicor Part Replacement

How to Manage Epicor Part Replacement

by | Sep 9, 2020 | Business Consulting, Epicor, Epicor 10, ERP Consulting

One area of Epicor consulting that we frequently get asked about spins around engineering change orders. Engineering updates to the part master once the company is live has been likened to working on a car’s engine while speeding down the freeway. This is especially true when creating new parts to replace existing parts in existing Bills of Materials. It would be an understatement to say that parts are rather important to ERP systems—even novice Epicor consultants know that parts are one of the foundational building blocks upon which everything else rests.
Epicor Part Replacement Management

How to Introduce Engineering Change Order

Shake-ups to the part structure invariably have tremor effects on the upper decks, so you would want to minimize these by following a careful approach to introducing engineering change order. Such an approach involves two steps: carefully understanding the exposure of the legacy parts to be replaced and planning the update accordingly, and then systematically executing the necessary updates to pull off the switch. In one case you might be renaming a part or a number of parts, to ensure consistency across your part master. But such changes affect a number of areas, so the steps to execute such changes need to be well planned out and executed. The following is a guide to do just that—planning and executing Epicor part replacement within your business system.

 

 

Planning and Review: Steps for Replacing Parts in Epicor

Before making changes, it is a good idea to plan through the changes you wish to make and ensure that all areas of the system that might be affected by these part changes will be addressed. The following tasks should be performed before a change is implemented:

  • Review any cases where the part exists on an open sales order and review any related allocations.
  • Review any cases where the part exists on an open purchase order or purchase order suggestion.
  • Review any cases where the part exists as a job material—this might include open firm jobs and unfirm jobs.
  • Review any cases where the part exists as the part to be made on a job header— this might include open firm jobs, unfirm jobs, and even part suggestions in the planning workbench.
  • Review the cases where the part exists in the BOM of another part.
  • Review the cases where the part is located in a Part Bin as on-hand inventory.

Execution of Part Replacement in Epicor

Once ready to execute the changes, care should be taken to ensure the proper steps are done, and in the proper order:

  • Create the new parts, revisions, MOMs, part costs, etc.
  • Run an update to replace the material parts in any part Bills of Materials.
  • Remove any Sales Order Allocations. Run an update to replace the parts in any Sales Order Lines.
  • Run an update to replace the material parts in any open, firm Jobs’ Bills of Materials. Delete any unfirm Jobs that contain the legacy parts as materials.
  • Run an update to replace the Job Header parts in any open, firm Jobs. Delete any unfirm Jobs for the legacy part. Delete any Part Suggestions for the legacy part.
  • Run an update to replace the parts on any open purchase order lines. Delete any PO Suggestions for the legacy part number.
  • Run a quantity adjustment update to remove the on-hand quantities of the legacy parts. Run a quantity adjustment update to add the on-hand quantities of the new parts, using the legacy part quantities that were previously on-hand.
  • Inactivate the legacy part.

 

Playing the Part in Epicor Part Replacement

Above all, care should be taken to make sure that sufficient communication has been made across the organization, beginning with your designated Epicor consulting team and extending throughout your entire company’s infrastructure. As you can see, such a change affects multiple modules, so you can anticipate that many people and departments will likely be affected by such a change in Epicor. As such, make sure to communicate accordingly. Following the proper steps, you can help keep your part master clean, and your business system running smoothly.

 

 

Looking for more tips from our Epicor consulting team?

Read our white paper on Epicor Part Setup.

Get an Epicor ERP Part Setup & Manufacturing Best Practices Whitepaper Today

Name(Required)
Email(Required)
Epicor Administration & Helpful Tools of the Trade

Epicor Administration & Helpful Tools of the Trade

by | Aug 25, 2020 | Epicor, Epicor 10, Epicor Administration

A Day in the Life of an Epicor Admin

One of the best parts of working as an ERP consultant is the opportunity to work with so many smart people, and you’ll find many working in Epicor administration. Companies are successful for a variety of reasons, and the strengths, skills, and character of individual employees are at the core of organizational performance. The privilege of working with devoted coworkers makes it all worthwhile. As they say, iron sharpens iron — and even should a few sparks fly, I am thankful for the grind. Epicor admins come with a toolset capable of managing the application’s configuration, performance, and architecture — administration responsibilities that demand precision and prowess.

Epicor Administration

What exactly is an Epicor admin?

Of the many ERP positions I’ve encountered, none are so perplexing and nebulous as those responsible for Epicor administration. What I have come to understand is that the Epicor Administrator is as thankless a role as it is undefined. In my years as an Epicor consultant, I’ve encountered countless ERP administrators, and their skills and responsibilities extend in all directions.

 

For example, an average day for an Epicor admin might look something like this:

  • Address system errors first thing in the morning.
  • Perform an ad hoc backup of the production database and restore it over the test environment to give new employees a place to train.
  • Troubleshoot intermittent MRP issues.
  • Educate the engineering department on correct part setup.
  • Deploy yesterday’s dashboard tweaks.
  • Blow away some pesky personalizations that have been flummoxing a particular user.
  • Help the finance department with some BAQs in anticipation of the coming month’s end.
  • For free time, work on a chip-away-at SSRS report.
  • On the way out the door, kick off a DMT run to mop up some of last week’s indiscretions.

What does tomorrow bring for the Epicor administrator?

 

Every admin I’ve met hosts a similar blend of firefighting, process improvement, and general oversight. And in spite of the tremendous breadth that such a position demands, I have encountered many an admin whose depth of knowledge has matched their breadth. Over the years, I’ve done my best to learn from them and to document what I’ve learned. As such, here are a few Epicor administration tricks I’ve picked up along the way that you can bookmark, revisit, and share.

 

Need your BAQ to return more than 10,000 rows? BAQ Execution Settings help Epicor administrators get more data.

Learn about BAQ Exec

Have you wondered how to manage the multiple sessions flag in user security works?

Get multi-session tips

Do you need to enable new functionality through the admin console? Check out our post on Epicor licensing.

Enable functionality with the admin console

Looking to launch a form’s custom version from a right click or an MES button? Learn more about binding Epicor customizations using process calls and menu IDs.

Read about binding customizations

Trying to work out auto-login? Review Epicor sysconfig files and auto-login capabilities.

Work it out with auto-login

Need to relate UD tables to their parents? Learn how the SysRowID fields relate parents to children.

Relate

Are you running into the “CGCCode Mismatch” error when importing dashboards in Epicor 10? Learn how to edit a dashboard definition.

Create a dashing dashboard

Are you looking to deploy customizations in a multi-company environment?

Learn more about menu maintenance

Beyond Epicor Admin

Are you looking to go beyond administration knowledge?

Sign up for a free consultation with an Epicor consultant by chatting with us now.

Epicor DMT: Data Management Tool Tips & Tricks

Epicor DMT: Data Management Tool Tips & Tricks

by | Jul 30, 2020 | Epicor 10, Epicor Administration, ERP

connected technology

The Magic of Epicor DMT Licensing

 

A common user challenge over the years with Epicor’s Data Management Tool (DMT) relates to the licensing of the actual module. Epicor DMT came from a very different evolutionary thread than the main application, and when trying to bind these threads, things often become tangled.

 

Licensing can be especially twisted, and more than a few us have felt the frustration of figuring out just who to send the license request to. Then remembering that we needed to cull the list of Company IDs from the database to be licensed. Then anxiously waiting for the returned file to arrive, hoping that we weren’t going to receive, by accident, a license file configured for a manufacturer of manure spreaders, headquartered in Glasgow.

 

But fear not, dear users. Epicor provides us a DMT remedy. For versions less than 10.02.5xx, you will still need to send Epicor a list of your Company IDs so that they can generate the appropriate license file for your use. But for versions 10.02.5xx and above, the DMT license is now included as part of your E10 license and is no longer reliant on your Company IDs.

 

As you can see below, DMT can now be licensed through Epicor’s standard license, which removes the need to drop individual DMT license files in user client folders. This greatly simplifies the deployment of the Epicor DMT application.

 

Epicor DMT Data Management Tool Tips and Tricks

 

That said, there are a couple of related tricks that can help you separate the forks from the sporks in your Epicor kitchen. One way to identify your DMT licensing from the DMT application itself is to utilize the settings icon at the bottom of the application and open the “DMT License Info”:

 

 

Epicor Data Management Tool

This will raise a properties window, providing details about the DMT license:

Epicor DMT License Information

 

DMT is a client-based application. Therefore, much of the magic behind the DMT module is to be found in the application’s client folder.  This includes licensing. Going back to our talk about license, it’s important to understand that for a DMT client to function correctly, one of the following must be the case:

 

• DMT must be licensed in Epicor’s Standard License file, which is installed on the application server.
• A traditional DMT .lic file must be deployed in the user’s client folder, where the DMT application normally resides.

 

Let us, as an example, assume we’re in a situation in which DMT is not licensed through the Admin Console license file. In lieu of the main license, a DMT-specific .lic file will need to reside in the client folder:

 
Epicor Search Client

 

Were I to remove the .lic file from the client folder…

 

EstesGroup Client Screen

 

…upon my attempt to log into DMT, I will receive a licensing error:

 

Module License Configuration

 

As such, if you don’t have the DMT module licensed through the Admin Console, you’ll need to make sure you’re still planting the company-specific license file in the appropriate client folders. If you think about it in the sense of a cooking motif, you can think of it as a situation in which the pantry is emptier than Mother Hubbard’s cupboard, the grocery store delivery is still a day out, and your mask is at the bottom of the laundry basket. Sometimes you need to cook with only what’s already in your house. Just like your dinner occasionally falls together as a tasty combination of tomato soup and wanton noodles, you’ll sometimes need to get a company-configured license file and stick it in your client folder. With these steps, you can get the Epicor DMT program to run.

 

 

DMT is great in an E10 Upgrade.

Learn more
Epicor BAQ: Returning Too Much of a Good Thing

Epicor BAQ: Returning Too Much of a Good Thing

by | Jun 17, 2020 | Epicor, Epicor 10, Epicor Administration

Epicor BAQ

The Epicor BAQ (Business Activity Query) toolset allows you to leverage the mounds of data that your system generates. But the problem with mounds of data is its volume—when we say mounds, we mean… mounds. As such, Epicor has built in a feature to its BAQ designer to limit the number of rows returned.

 

This feature prevents a “runaway query” from tanking a company’s performance. This functionality was especially helpful when I first delved into queries, as it prevented me from needlessly tanking my environment. Looking back at some of my early queries, they certainly were tank-worthy.

 

But for experienced Epicor users working with large datasets, this limitation can be… well, limiting. When a query generates a dataset that is more than 10,000 rows, the following warning message displays:

 

Severity: Warning, Table: , Field: , RowID: , Text: Test results are forcibly limited to 10000 rows to prevent the application server memory overload:

 

Activity Query Epicor BAQ

 

This can be immensely frustrating to Epicor super-users, for there are cases when the entire dataset needs to be returned, to gauge the efficacy of a given BAQ. In the past, the workaround to this limitation was to embed the BAQ in a dashboard, as the 10K row limitation disappeared when the BAQ was part of a dashboard.

 

But such an additional step seemed like an unnecessary contrivance—scaling the fire escape when all you needed was a step ladder.

 

Fortunately, Epicor modified the BAQ designer to allow the person creating the BAQ to modify the Execution setting that limited the number of returned rows. The steps to make this possible are below.

 

From the Actions menu, select “Execution Settings”:

 

Activity Query BAQ Execution

 

Click the new icon to create the new execution setting.

 

This creates a new execution setting that needs to be defined. Then you can perform the following additional steps:

  • For the “Setting Name” select “RemoveTestRowLimit”
  • Set the Setting Value to “True”
  • Check “Persist In Query”
  • Click OK:
BAQ Query Test Execution

 

Thereafter, the BAQ will return all the available rows:

 

Epicor Activity Query Designer

 

The execution setting needs to be defined for each query for which you wish to return more than the default number of rows. Make sure to save the query after the execution setting has been defined.

 

Ready for a quintessential query?

 

Successfully navigating the Epicor application is rarely a matter of taking one great leap forward. More often than not, it is a series of small, incremental steps. With Epicor BAQ, your goal is to take your data and turn it into information—without getting lost in the volume.

 

 

To learn more about Epicor management and administration, please watch our video on cloud ERP by clicking here.