We are moving over to Epicor and I am trying to figure out how to make historical serial number information available without loading in thousands of orders and transactions.
My thought is to load in serial number, part number, and status (shipped) into the SerialNo table. Everything else including ship date and specs gets loaded into a UD table. Then I can run a join to pull up the history of the serial number.
My question is how do I make a serial number tracker that can incorporate the ship date in my UD for old serial numbers, but also show the ship date for serial numbers that are shipped out after I go live? Is there a way for a field to search one column and if the field is blank show a data from a different column? Or do I just need to perpetually duplicate the new data into the UD table?
If I want to create a shipped date for the serials themselves I have to create a Ship hed, PCID, etc. I don’t want to load in all that old historical transactional data from the 90’s lol
I ran a BAQ to connect SerialNo to ShipHead and pulled the ship date from there. I don’t want to do that for my historical serial numbers as those date back to the 90’s and that’s a lot of transactional data to load in that I don’t need. I just need the date.
So I did this and it works great. The issue is I want a ship date. There is no ship date on serial it’s linked to a shiphead and I need a packnum to link those. I don’t want to create shiphead and pack nums for all the historical serial numbers so I stuck the date in a UD Field. See my BAQ below on how it all connects.
In the future when we start shipping things out through Epicor I would like to pull the ship date from shiphead. So my question is there a way to merge the data so I can have one field on my tracker that can pull from two tables depending on where the data is located. Or do I have to just make a BPM that pushes the data from the new serial numbers in my UD table… which essentially creates a lot of duplicate data which I would rather not do if possible.
What is the picture though - is that a BAQ you made? Or is that a standard Kinetic screen? (They keep changing the layouts, so for all I know, that’s some new version of Serial Tracker.)
I tried my best to explain it hah. We are a very old company with a lot of data that is important for service and parts orders but has been stored different ways throughout the years.
Tables: Serial Number, ShipHed, UD05, UD06, Customer
Background:
We are currently going through implementation from our old ERP to Epicor and will go live in January. We service large equipment that we have manufactured as far back as the 90’s. (Actually even earlier, but those are physical files and it doesn’t happen that often). So we need to load in all those historical serial numbers from our current ERP system and some of the relevant data attached to the serial number. The documentation we need to reference in order to properly service that equipment is based on serial number, part number, ship date, and various other data. I need to have a screen that is a serial number tracker where someone can type in the serial number, see the part description, see when it shipped, and other misc data I am storing in UD06.
My current plan is to load the equivalent of OrderHed and ShipHed for the old orders into UD05 including Serial Number, Part Number, Ship Date, Customer, Sales Person. OrderDtl equivalent will be loaded into UD06 and just include serial number and part number. These UD tables will hold all the historical data for all serial numbers shipped before we go live with Epicor. Post go-live this information will be captured through native Epicor tables and processes like ShipHed for ship date.
What I need:
Serial number tracker that can show ship date in the same field regardless of whether that data is in the UD table (old serial numbers) OR connected through ShipHead (new serial numbers). There are other datasets other than Ship Date that I will need to apply this to as they have a native place in Epicor.
The reason I am loading all this data into UD tables is I don’t want to load old transactional data that isn’t needed into the system just to track the Ship Date. Additionally, all the part numbers and CustIDs referenced exist in the Epicor Part and Customer tables as well as natively on the SerialNo tracker page so that’s an easy part to have work properly. It’s any additional fields I am adding to my tracker where the data is stored differently in Epicor vs. Historical imported data.
I tried to explain as succinctly as I could. Thank you for any help!
If I am understanding your BAQ and the data you are trying to display, my approach would be to create a calculated field using conditions that will use the appropriate Ship Date value. If needed, this logic can be expanded to additional fields.
One thing to keep in mind is that your need to STORE data and your need to DISPLAY stored data can be two entirely separate things.
I’d go with what several others have said here… create a single UD field in the Serial Number table that you then populate with the ship date on the legacy serial numbers. Use the built-in functionality for all newly-created serial numbers.
Then any reports and dashboards can be written so that they first look to see if the legacy ship date is populated (so they can use that), and if it is not then use the built-in functionality to find the ship date from the existing shipment record.
Separate what can be separated. Don’t mush things together that makes it more complicated.
