Select Page
How to Update Multiple Tables in Epicor DMT

How to Update Multiple Tables in Epicor DMT

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.

Cloud Business Solutions for the Virtual Office

Cloud Business Solutions for the Virtual Office

Virtual offices become the business solution of the now

The term “cloud” is a term as elusive as it is enormous, and a virtual office these days often appears to be anything you want it to be. The sky, after all, is a big place. And fitting lightning in a bottle is no easier than pinning a hard-and-fast definition on the digital computing donkey known as the cloud. When it comes to software deployments, cloud application deployment can mean different things to different people. Unfortunately, this amorphous ambiguity has tangible, deleterious effects on the user community. At its core, a cloud business solution implies real-time data access, and a virtual office is simply a cloud-based environment that enables secure and complete data interaction from anywhere in the world.

Remote Worker in a Private Cloud

SaaS vs. Managed Application Hosting

Let’s begin with the simple admittance that not all clouds are created equal. In cloud computing, you can make a vast sky-and-earth distinction between web and private hosting environments. Let’s lightly look at both.

 

Web-based solutions:

Purely web-based applications are hosted by a vendor, not the customer. The customer accesses these applications over the internet, often through a simple web browser. Technology consultants often call these deployments “software as a service” (SaaS). This is due to their subscription-based costing model.

 

Private cloud business solutions:

Private cloud deployments replicate on-premise versions of the software. Customers work with a surrogate hosting partner. The hosting of the application isn’t controlled by a software vendor.

 

These are the basic options for cloud deployment in a computing environment. This is important to know because if you choose the best cloud business solution for your company infrastructure, you can expect tremendous impact on your company’s capabilities. Thus, you can achieve your strategic objectives.

 

Does your hybrid cloud lining need a business solution tailor?

In software circles, “tailor-ability” refers to the customization capabilities of an application. Can you safely tailor your application to the needs of your organization? Compare this to core code modifications that were common and often detrimental to ERP implementations of earlier eras. An easy customization process ensures that your cloud solution can adapt to your business like a good ERP lets you easily upgrade.

 

In this new world, software vendors tout themselves based on toolsets. These computing tools allow customers to tailor an application. The IT department, or an IT consultant, can then address idiosyncratic needs. These solutions promise maintainability and upgradability. And all is well in the world.

 

However, as software vendors move enterprise platforms increasingly to web-based cloud architectures, the highly touted tailoring functionality can vanish faster than a morning mist in the desert. Moving from traditional office to virtual office is obviously the future of application management, but a web-based infrastructure can limit users.

 

Fortunately, a hybrid cloud environment assists companies with needs that revolve around complex business environments. Premiere data centers, secure virtual conference rooms, remote worker empowerment, and even futuristic capabilities like machine learning, all become accessible and customizable computing tools.

 

 

Will SaaS be enough?

As cloud deployments go, hybrid cloud computing can save companies time, money, and headaches. This is especially true if SaaS is not the most applicable cloud management application available. Software-as-a-Service, or SaaS, is a management tool that is ideal for companies with standard requirements. Cloud infrastructure for configure-to-order environments, for example, needs highly adaptive and robust capabilities. Virtual office services create a cloud-based business address for remote teams to securely meet.

 

An ideal solution often isn’t the first choice of companies moving to cloud services. Cloud applications are as diverse as the businesses that could benefit from a computing solution that transcends a physical office. What if the sales cycle ends with meeting rooms in the cloud that aren’t specifically helpful to the software buyer? You might regret ever giving out your phone number.

 

 

Are you on-premise and going cloud?

I once heard the CEO of a software vendor describe his own transition to the cloud this way: “On-premise vs. cloud has become a matter of customizability vs. configurability.” That is to say, if you are bound to the web-based or SaaS version of the application, and you’re in search of customizability or tailor-ability, you’re out of luck.

 

Unfortunately, this memo has been slow to reach the prospective user community. Sales engineers demonstrate the software in its on-premise form, on locally-deployed machines, with the full gamut of features and capabilities, only to have the customer ultimately sign the dotted line for the web-based cloud version of the application, a neutered version, bereft of many of the bells and whistles that were so brightly touted during the beauty contest that was the software selection phase. Tricky cloud.

 

What happens when tire meets the cloud terrain of virtual office?

Companies frequently move through a software selection cycle that ends with a cloud-based solution deployment:

  • Closing the sale and finally owning the software license
  • Implementing the purchased software
  • Training employees and customizing the solution based on business needs

In the third phase of cloud-based application deployment, disappoints surface. For example, clients often struggle to implement an enterprise resource system in a large, and complex business environment. One customer came to us amid such disappointment. Company management purchased an ERP application in the cloud in its Software-as-a-Service (SaaS) form. In this case, “cloud” meant an underpowered, web-based subscription service version of the application. Vapor-ware.

 

 

What are some alternatives to SaaS?

Alternatives available in private cloud hosting become necessary in complex environments common in the manufacturing and distribution industry. Frustrated with the limitations of the web-based version, our customer first came to us scrambling to understand just what they had been mandated to implement and whether there were any other options for implementing the software that did not so badly hamstring the organization. Had the management team received an impartial explanation of “the cloud” and its variants, they may have averted many of the frustrations of trying to implement an enterprise system in a complex business environment with a tool that was frankly too underpowered to be up for the task.

 

A business cloud solution can surface confusion.

If you’re looking at a web-based cloud version of a software, ensure that the vendor’s demonstrations use that specific version. Similarly, if you’re deliberating between the on-premise application and a version of the cloud, work for answers to the following questions:

  1. Web-based applications operate largely on the server, and operate in a shared environment. This normally limits the amount of server-side tailoring available. Given the thin or zero-client environment, what kinds of tailoring capabilities are available in such an environment?
  2. Reporting solutions frequently operate on the server, creating challenges when trying to develop custom reports. Does the web-based solution have answers to these challenges?
  3. User-defined data is often a key capability in complex manufacturing and distribution environments. How does the system in question handle these requirements when deployed in a web-based manner?
  4. What options are available when it comes to cloud-based versions of the application? How do they differ, in terms of features and capabilities?
  5. What are the core capabilities of the application, in terms of both configuration and customization? Are these capabilities present in all versions?
  6. Are there any specific modules of interest that might be affected by a cloud decision, such as field service or product configuration? Do these modules differ in their capabilities based on their cloud versions?

Addressing these concerns at the time of selection verses the time of reflection is key. Nobody wants to reflect on an overlooked version of a software, especially when making the decision to move to a business solution in the cloud.

 

 

Epicor Supplier Relationship Management 2020 Trends

Epicor Supplier Relationship Management 2020 Trends

In March 2020, before the shutdown, I traveled to a few customers and had an opportunity to talk supply chain with some of their commodity managers. Given the centrality of China-based supply chain sourcing, I wondered if pending restrictions on material movement between countries and potential productivity downturns overseas would affect these clients. At the time, the impact was uncertain—many of these companies had placed forward-buys on key commodities, such that they expected to have a bit of a buffer to ride out the ensuing uncertainty. Strategic supplier relationship management proved to be the ideal way to weather 2020 supply chain challenges.

Epicor Supply Chain Management

How do supply chains keep up with demands?

During the subsequent months, strange things abounded. On the home front, demand patterns changed drastically, trimming back the need for auxiliaries and tertiaries, leaving much of the stockpiled inventory pushed to a corner, waiting for needs to level off and go back to their old ways. In other markets and verticals, demand for certain products and services had gone through the roof, and companies struggled to realign their supply chains to support the fulfillment of surging demand.

 

This resulted in a great deal of wheeling and dealing, including searches for alternate suppliers. Local companies took on the manufacturing of components that had long been outsourced. These activities are ongoing for everyone balancing new supplier relationship management trends.

 

As situations continue to evolve, folks immersed in the supply chain community continue to try and understand just what can be learned from this strange turn of events. One point of interest has to do with the actual dynamics of demand. Strangely enough, it was not the downturn in supply that created the many supply chain challenges, but rather, it was the spiking nature of demand. Product and service demand did not decrease uniformly. Rather, it scraped bottom in certain product categories, markets and verticals, and sky-rocketed in specific niches.

As a supply chain manager, predicting such strange peaks and valleys would be a fool’s errand. Rather, the successes in Epicor SRM that I’ve encountered have had more to do with the ability to rapidly react to challenges than to anticipate them. This ability to react is normally due to a few key capabilities:

  • The ability to develop a broad supplier base. This means locating multiple potential sources of supply, in the event that one source of supply goes dark.
  • The ability to leverage alternate parts and methods to manufacture high-demand finished goods, in the event that primary components become unavailable.
  • A highly capable supplier relationship management toolset that can closely monitor and maintain incoming supply, as to ensure that incoming supply will meet the company’s needs and provide maximum reaction time, in the event that supply will not make it in on time.

SourceDay can assist in this final capability, which is the ability to organize supply in order to ensure that Epicor customers can support the shifting and shifty demand patterns of their own customer base.

Has 2020 changed your supplier relationship management strategy?

See how companies like yours respond to supply chain disruptions.

Watch a webinar to understand 2020 industry trends:

Covid-19’s Impact on Your Supply Chain 

Presented by our partner SourceDay

On September 17th, 2020

Covid-19’s Impact on Your Supply Chain

Epicor ERP Event: EstesGroup Fall Summit 2020 (Video)

Epicor ERP Event: EstesGroup Fall Summit 2020 (Video)

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 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

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)