A bit of an odd question (sorry just bear with me). Our old ERP used incrementing numbers to represent a customer record. The numbers start from 40000 and end at 99999. Note that there are gaps in that range so i.e. 40001 may exist by 40002 may not.
My goal is to leverage our old customer numbers into the CustNum field in Kinetic and change the incrementing position at 100000 so that there are no collisions as new customers are created.
Questions are the following:
Is there any reason not to do this?
I understand for Invoices and other direct accounting functions this isn’t desirable since you want the chronology of the IDs maintained. For customers and suppliers, I don’t see any issues.
I realize creating a UD field would be the most appropriate but I’d like to avoid maintaining three datapoints and one that’s forever legacy. Integrating it into customer number should be feasible.
Yes. CustNum is completely internal to Kinetic. Nobody is going to enter that number into the system. CustID is a string that users see and can be changed to whatever you want at any time.
Separately, the same is true for VendNo and VendID.
The number is never meant to be entered or changed, it’s a historic value that eventually will be irrelevant (until our customers fully migrate to CustID). It can be added to any screen for searchability (which would have to happen with a UD field as well).
I want to avoid inputting it into CustID because the goal is to migrate to proper alphanumeric customer IDs. So I’ll need a place to store the numeric value, may as well be CustNum is the logic.
UD Fields are going to be your best bet. I’m not even sure you can load a custnum, I think it’s autogenerated by Epicor and it doesn’t appear on anything external facing. One Custnum can have multiple CustIDs attached to it over time as you can update the CustID as needed.
What we ended up doing, and this may not be the cleanest solution, but it’s what worked for us was I made a UD field to store the historic customer numbers from our old systems as references. We actually had two (Property ID and Shipto ID). I then gave all of our customers new CustIDs during implementation so that they were alphanumeric. You can still search a customer by looking at the UD Fields, but they also have the new clean CustIDs.
Additionally, I wrote a BPM to track CustID and address changes to a UD Table. I did this because sometimes our customers get bought out and the name and address changes, but they still have our equipment. With this, when a Cust ID is changed along with a name or Add1 changes, it records the CustNum, CustID, Name, and Address fields to UD02. I then have a BAQ grid that displays all the lines in UD02 linked with that CustNum. This way I can see a customers “history”. You could do something similar but just track when a CustID changes if you weren’t ready to have all the new format CustIDs ready by golive.
That being said, it’s harder to be able to search and filter by them as cleanly vs having a UD field on the Customer table.
With this you aren’t maintaining 3 datapoints at all, but only loading in the historic datapoint once into the UD field and that’s that. You don’t maintain CustNum, epicor does. You could even have CustID and the historical UD field match until you are ready to update CustID.
Yeah this is basically the same strategy we are going to employ and were planning on from the beginning but it just doesn’t sit well with me.
I’ve designed and built a lot of complex database structures myself and would have no trouble utilizing incrementing primary keys the way I described. I’m just continuing the numbering convention our legacy system used. It’s not rocket science. Also DMT does allow you to assert the customer number and Kinetic lets you change the next increment ID. There’s theoretically no reason not to do it.
My primary driving force for wanting to do it this way is: no legacy data, no BPMs, no hacky workarounds or customizations. That’s a pretty convincing reason in my books.
What I came here to inquire about is to understand the unknowns. Kinetic does some weird and sometimes counterintuitive stuff sometimes, so I wanted to know what the footguns were before committing.
No one is giving me a clear sense of those footguns, which leads me to believe no one even knows. Hence UD field it is.
