After spending a few weeks setting up our company on a clean database for E10.2.100, I used DMT to import some customers from our v5.2. The DMT tool did not match up the CustNum that I had there in the .CSV, and instead created its own CustNum numbering which doesn’t match up with my records and will cause problems bringing over Orders.
My question is, how do I reset CustNum to 0? Once I do this I can import customers with DMT again, which are now in perfectly sequential order. The Customer table apparently doesn’t not have an SQL Identity assigned, so the number sequence must be stored elsewhere. Even deleting all customers and starting over, the CustNum continues to increment ever higher.
I did run an SQL trace when adding a customer, and found something that might be pointing in the right direction, but I’m still not sure where to find this in the database:
exec Erp.GetNextCompanySequence @Company=‘ABCMFG’,@SequenceName=‘CustNumSequence’
Typically, when using DMT, you would not need to use the CustNum to do the importing, instead using CustID, and let the system create/use the new CustNum from the new Database. This way you import customers, and then orders, and everything in the database should sync up.
Orders and Invoices reference the CustNum, not the CustID. So if I did what you suggest, all of my imported orders would be referencing incorrect customers. Big mess! This is why a reset may be necessary, so that they can all be in sync.
CustomerCustID is the field in the Sales OrderDMT that relates the Sales order to the customer ID, so as Tim said you would just use that.
If you didn’t want to join in the customerid in from your old transacitonal data, then I would have thought that importing the customer with the custnum as well as the custid it would have picked it up the custnum that you provided in the DMT source and not incremented as you have suggested.
Have you tried importing 1 customer with a number that has a value less that the next custnum in the sequence table?
Import Customers first using the Cust ID, that generates the custnum. Then import the Sales orders using the Cust ID value in the CustomerCustID field. No need to use the CustNum.
It is nice to know where that value is stored though
Every DMT import that needs to reference a customer, uses the CustID. The CustNum you provide is ignored. The import process looks up the CustNum for the CustID you provided, and uses that.
Other DMT “gotcha’s”…
Importing OrderDtl records with gaps in the OrderLine, end up with a different Line number. The subsequent OrderRel records won’t line up as their OrderLine values point to the wrong OrderDtl. I ended up padding the gaps for importing, then deleting them later.
Also, the job numbers using the Order-Line-Rel format wont match the order.
Importing job materials will ignore the MtlSeq and just number them sequentially 10, 20, 30…
Imported parts can be deleted, but not after doing any part trans. Even just setting the cost makes a part no longer able to be deleted. We accidentally uploaded the whole PartCost and now have 4000+ parts we didn’t want to carry over.
And if for some reason you get an error in an order line it will show the subsequent lines as errors also, but they appear to be added in, but with a different line number.
The other thing I found is that if for some reason your input line numbers don’t match the DMT will ignore and create line numbers for the lines in the order of insertion, which kind of makes sense.
When I was testing to make sure everything worked when I used
ROW_NUMBER() OVER(PARTITION BY t2.num_tran ORDER BY t2.num_line ASC) AS [OrderDtl#OrderLine]
to generate the order lines in the order I wanted for the DMT to ensure that the order of the lines in our old system matched Epicor.
