How does Epicor generate new sequence numbers, in particular for erp.HDCase.HDCaseNum

I’ve been exploring with SSMS how the tables fit together.

I’d like to do something rather… unsanctioned… and run an UPDATE statement to change all my HDCaseNum values on helpdesk cases.

We are migrating 23,000 odd helpdesk cases from a SugarCRM system, bringing them in via DMT. Epicor wants to generate its own HDCasenumber sequence, but the cases are identified in our email history subjecject lines by [CASE:xxxx] where xxxx is the sugarCRM case number.

I have added a UD field to store the old sugar case number, its going to be difficult for our users to cope with these cases having two conflicting numbers in two systems.

My (possibly naive) plan was a SQL Update statement to sync the HDCasenums immediately after DMT:

UPDATE erp.HDCase c set c.HDCaseNum =  (select cc.SugarNum_c from erp.HDCase_UD cc where cc.ForeignSysRowID=c.SysRowID) 

I thought then I would use ALTER SEQUENCE to start future HDCaseNums off from a new epoch, say 30,000. Then case numbers are aligned, and you can tell at a glance by case number being>30000 if it was entered in Epicor or came from Sugar.

Looks like its not using sequences though, and in fact the HDCasenum has separate sequences for each company. I’m assuming now there’s another table somewhere recording the “head” values? Or some other mechanism?

PS I know SQL UPDATE is naughty, and this is strictly a one-off, if it happens at all. I am backing up and messing with a sandbox database so noone gets hurt!

On a tangent: Is this something official Epicor tech support people could or would do for me?

Hmm thought I’d found it here, Erp.CompanySequence, but no its not there. Surely it should be…
from:

Also tried incrementing my biggest HDCasenum in one company eg 1851=>1852, refresh in client, then tried adding a new case, got the duplicate record error because its trying to save to company/1852. Fair enough; its definitely storing the next index somewhere, not just doing a MAX+1. (I reset that HDCasenum 1852 back to 1851 of course)

Yeah, I honestly would not go down this road if it were me.

I’d create a SugarCRM case user defined field and make an advanced search to be able to find the record either way.

But that’s just me.

3 Likes

I agree, but it sounds like the concern is that a previously used number in the CRM could be reused in Epicor? I would ask support how to bump up that starting sequence number so the ranges don’t overlap.

I think if you get nifty with the quick searches or search functionality, your UD field is probably your best route. It will take a second for any user to get used to switching to “new” numbers, but eventually you’ll be using Epicor’s numbers so the old ones wont matter.

But I am with Mark, I would not try to do what you are trying to do even though you are only trying to help your users.

Could you set the HDCase number with a BPM, and effectively override Epicor? Not sure if it’d be with a method directive on GetNew or a data directive. Do a trace and re-create what Epicor did.

I’d very strongly recommend NOT doing SQL updates. You could worst case FUBAR the structure and void your support maintenance agreement/warranty. Use the Business Objects to do it. Lots of documentation in the Epicor Learning Center, Help files, and from EpicWeb documentation - user guides and tech refs.

Welcome Chris!

2 Likes

DO NOT DO THIS.

There are numerous tables that relate to the case number, you will end up in a right old mess. How do I know…a short story ensues.

A short story about a naïve Epicor Administrator I once knew (some may even say that they still are, but you never stop learning). :slight_smile:

We had situation with Epicor 9 that CRMs during the quote process would, instead of searching for a customer contact, they would enter in the contacts name and then just blindly accept the “Do you wish to create a new contact with this name” prompt. Epicor allows contacts with the same name, it just creates a new contact number behind the scenes…

Several months go on and Support start contacting me…er the administrator, about weird behaviour in case management. When they go to select a contact there are duplicates, and that some cases are against one contact with the same name and other cases being with the other contact… People are not happy, all related case should relate to the one contact, quotes too. This shouldn’t be too hard one says. Needless to say many hours later they have gone through all the tables and work out how the contacts related to the Cases, and get it cleaned up. It was a serious pain. In hindsight they should have reached out to Epicor for a data fix. Sadly the problem kept on happening and kept on having to be fixed, until one day, They happened to be helping a CRM with a problem creating a quote and watching their process…Like a lightning bolt, there it was, the exact situation that was causing the duplicate name, but different contact num.

What did they learn. Simply updating what seems to be a master transaction table sounds kind of ok on the face of it, but it’s all the foreign keys you need to be wary off. The way case management in Epicor from a technical stand point is not that well documented.

Using the UD field is a sensible approach and using a quick search to add in the sugar CRM case number is the simplest.

For your information the next sequence number for the HDCase is the erp.KeyLast table. So it theoretically could be done by changing the BlockLastValue to a number greater than your last Sugar CRM HDCase, then in DMT put in your SugarCRM case number in the DMT HDCaseNum field. This would be the maximum I would do if I were to want to attempt to import the Sugar CRM case numbers as Epicor Case numbers. This suggestion comes with no warranty, so test it in your test system if you are game.

Just an additional point on @aosemwengie1 comment, if you import case numbers that have the potential to conflict with the current Case sequence, you could run into a Primary key error as Epicor tries to increase the sequence automatically when you add a new case. This is because a case with the same number as the newly generated case number already exists in the table. I have seen this happen on sales orders when people forget to set the next order number in company maintenance after performing an open order load.

I hope all this information helps. Please don’t just update in the unsanctioned way you suggested. In the end you will cry.

Coincidently the Sugar CRM CEO used to be an Epicor VP for Australia, how ironic.

EDIT… Quick test on updating the LastBlockValue to 100 from 7. Creating a new case the new case number turned out to be 101 on save.

4 Likes

I opened a Support Case with Epicor about this asking if we could set a seed for the HDCaseNum similar to how we can with Order or Quote num. They said we cant and it was designed that way, but they wouldnt/couldnt tell me why.

1 Like

|read| :slight_smile:

1 Like

Yes, I’ve got the don’t do SQL updates vibe!

It sounds like it may be possible to request a “Data Fix” from epicor and have them run the updates, if I can explain persuasively enough the why and the how.

The “why”:
In this instance I’m importing data into an empty database( no existing cases), and nothing linked to the cases afyter import, and the old sugar system will be replaced by epicor; but there’s 10+ years of email history with [CASE:xxxx] in the subject line that just won’t relate to epicor’s new case numbering.

Is this on-prem? If so, you could also add a UD field in the case to have the link (URI) to your SugarCRM system - no matter how you handle the case number. Just a thought.

Wouldn’t necessarily matter, depending.

Did you mean you changed it from 7 to 100? Yes, it worked for me too, set it to 30000, next case was 30001.

So your story put me off the update, but then the erp.KeyLast nugget showed me exactly how to do it. Now I am conflicted! :slight_smile:

I will do it on my sandbox database and see what happens. Again, even when we go to our production data there will be no pre-existing cases in the system- its empty. The only cases will be those I’ve just DMT’ed from sugarCRM. Nothing will be linked to the cases. The cases will be linked themselves to customers and projects (also DMT’ed in). The update is a one-off alignment to the existing data.

on-prem, and yes I already have the ud field to hold the sugar case number. Still annoys the crap out of me that our historic cases now need two ID numbers though

Good point, so long as I bump up the starting Epicor case number (via erp.KeyLast), there should be less confusion with existing sugar case numbers; even if I don’t “align” them with a naughty sql update.

1 Like

My story was really about how many tenticals HDcase has and doing a post update of the case number would be a bad thing… It was late at night and I was having a moment of nostalgia… Probably only three real “Oops moments I can recall now” That being one, the others being

  • thinking I logged of the server at the end off day and I really shut it down, then walked out the door.
  • Using SQL 6.5 and expanding the database that one to many times and spending till the early hours of the morning trying to work out how I can recover it after the restoring the database failed.
1 Like

On-Prem is better if you’re leaving the server running. And I mean the Case URI, so you can view the page in Kinetic in a browser window.

If it was the cloud service, then I assume you would have stopped paying for it after the upgrade and the history would no longer be accessible.

Why not DMT the Sugar case files into Epicor? That’ll increment the starting number too, and you could associate them with customers too, and gradually build the rest of the case details.

Thats what i am doing, but epicor ignores hdcasenum and replaces it with its own sequence.

Regards,
Chris Were

1 Like

So…

Add a UD field for the sugar number, set up some advanced searches.

Change that erp.keylast to skip 10,000

Let everyone know.

1 Like