We are going live Aug 1 and I am trying to figure out how to port over historical customer data. For reference we are basically a custom made to order manufacturer and we still service machines 30 years old. I need to bring in our customers and serial numbers associated with those customers. The issue is some of the customers have been bought out in that time where they were Company A and now they are Company B. They still may have all those same serial numbers operating at that location though. What would be the best way to import this historical info? For instance it is still important that we know Serial# 1234 was sold to Company A in 2010 but in 2015 Company A was bought out and became Company B (same address). If I search company B I want to know the current machines that are there as well as that it used to be Company A. Is there any way to load this historical data with DMT?
Edited to add: We are going with Kinetic Cloud. In our old ERP the customer is the distributor and the ship to is (potentially) the customer (could also be a rigger or the distributor). We are flipping that in Epicor and utilizing alternate bill-to. Lastly we have an in-house SQL database with another property database which was our work around from our current ERP not having a way to accommodate our need for 3 addresses per order.
Without knowing any more than what you’ve said here, TYPICALLY the best way to upload data of this type (historical transactional data) is into a UD table, meaning that although it isn’t linked to any CURRENT transactional data, you can create all the dashboards and reports you like to search and report.
The rub with this is that if that customer now wants to buy something new, you’ll have records both in the “current” data as well as the “historical” (UD table) data… and will need some way to link the two.
There really is no perfect solution, but in my experience this has worked the best most often.
I would use a UD table and have a company field and a company search field. In the search field make a pipe or tilde delimited string of all of the names and then in the search do a contains.
We keep a copy of our old ERP data in a DB that sits on the Epicor DB server. I then added it as an External BAQ source.
I think it’s better to have the original data in the original format in a convenient location rather than trying to cram it into the UD tables. I realize this is a luxury of being on-prem and not available to those cloud users who lack direct DB server access.
Haha I’ve been researching and agree there doesn’t seem like a perfect solution. Lots of these customers are still purchasing service/spare parts for the equipment so we definitely still need the customers active. Plus occasionally for older equipment our documentation may be… not quite correct and it is helpful to look up older orders for that serial number/property to confirm the correct part is being ordered.
Start by thinking about what data points you need to keep: obviously the serial number, some type of part number, date of sale, some sort of customer identifier, things like that. Develop a process for creating a “new” transaction with one of those “old” serial numbers (in case you need to repair it), and another for creating a “new” customer with that old customer information and how you can link the “new” and “old” records together. If you have the skillset available, seriously consider @jtownsend’s idea about a complete external database and external BAQs… if you don’t have that skillset available then spending the time up front to set the data up correctly will save a WORLD of hurt later on…