We have a very overdue initiative for bringing some historical data (mainly orders) into Epicor.
I am not sure why this was not done before moving to Epicor and am stuck with this.
The problem is all of these orders need to be placed at index 1 of the table (and push all other orders down). The challenge is I cannot use a prefix or even leading zeros and have run out of ideas plus those orders already have some order numbers from the previous platform and I need to preserve those as well (I could use a UD field, no problem though).
Could you please let me know if you can come up with any ideas?
Are these orders all closed? If so, I would highly recommend not attempting to import them into your live system into the sales tables. Instead, use a UD Table, and create a “history” UD table to hold this historical data, and then create BAQs to show the history as needed.
STORY TIME: many years ago, at my former employer, I was told that “We NEED the history… We refer to it on a daily basis, going back 5 years.” Long story short… we decided as a company that we would leave our old system running for historical research and we would not import any history. FAST FORWARD 6 months after going live. I discovered that the old system’s server was not available. I went in and rebooted it. Turns out that it had failed THREE MONTHS prior, and nobody noticed. I decided to turn it off, and I waited… nobody ever complained. Hmmm… the “NEED” was not really there. Moral to the story: Make sure that the need for the historical data is real. Historical data does cost money. More money than it is probably worth.
And I may even suggest a completely different store (other SQL, Postgres, Indexed blob, …) so as not to increase the size of the DB, backups, restores, etc.
When we moved to Epicor 10 years ago this was the first thing everyone asked. We exported the sales history from the legacy system into an Access database. I asked all the salesmen if they need anything let me know.
There were very few queries and we did not have to use the Access database more than a few times.
When we went to Epicor at my last job, we wanted to keep a five-year sales report similar to the old one. We put key sales data in a UD table and used a union query to seamelessly merge the legacy and Epicor data.
You just have to pick the data you want to keep to keep it from being a big storage issue.
We are going to do the same thing that everyone including @timshuwy and @josecgomez said
A comment on what @Mark_Wonsil said is that we wish to be able to pull the data through native BAQ (vs External BAQ) so it makes sense to use an out-of-the-box functionality/capacity.
We have not prepared the data yet and most likely we will use UD fields for the UD table.
I remember @josecgomez said in a post that UD fields add almost no overhead since the relation between them is 1 to 1 join.
