Hi Fellow Epicor users! I created a part number in our system, for example alc222hg50z1000. I need to change this number to our standard numbering system, all caps, ALC222HG50Z1000. how can I do that and maintain the history of the part? something this simple there must be a way…
Thank you in advance!
Jesse
1 Like
I believe you can do this by recreating the part number. To delete a part you have to remove all inventory of it, and all jobs(demand) for it, at least temporarily. Try it in Pilot first to see if it works. Since the characters are the same, the linking should be maintained for your historical data. Adding the new part in with capitals should work. Good luck!
2 Likes
That’s the hard part. I’m not sure you’ll be able to “delete” the part but you can --at least-- mark it inactive after following Nate’s advice. IIRC part numbers are case sensitive so you can then make a new part with the full caps. The history will remain with the ‘old’ part but it’ll still be available in the database where a deletion may remove the history.
2 Likes
I didn’t think you could delete a part once there were inventory transactions against it.
4 Likes
Yes! You and Randy are right. You have to mark it as inactive. 
1 Like
I agree, once you have transactions you won’t be able to delete it. If you’re on premise, you might consider a “back-door” option. Use SQL to update the part number.
Update erp.Part set PartNum = 'ALC222HG50Z1000' where PartNum = 'alc222hg50z1000';
You might also want to consider the other tables that are part of the part master: PartPlant, PartWhse, PlantWhse, PartRev, PartMtl, PartOpr, and so on.
Normally, this type of update is a big no-no. And, I’m not usually in favor of using SQL to update the database. I’d definitely test this in a pilot environment first to make sure there are no issues. And by “test”, a very thorough test. Check out a part, engineer, check-in, do quantity adjustments, run reports, etc.
This message will self-destruct in 5 minutes, and there will be no record of me advising going the SQL route.
5 Likes
Inactivating and replacing parts fixes stored data but detaches history and is mined with opportunities for error.
If you’re self hosted and your SQL Server collation is configured case insensitive and if you have a database person skilled enough to look over the lay of the land and say lol no, SQL would be an option.
But the stored data isn’t the problem, it’s the presentation.
Depending on where and who needs to see ALL CAPS when you spell the part’s name, the safest option would be to update reports and fields to convert to upper case regardless of what’s stored on the server. It’s the least fragile approach. Conversion can’t break data if data isn’t converted. Future users can’t create a casing problem to fix if their input is displayed upper-cased. Customizations enforcing upper case can’t break if they don’t exist.
6 Likes
Good option!
This is probably the best way to go. Good call!
Thanks to all who responded! Our IT dept. is going to add rules to change everything to ALL CAPS with the next upgrade.
Appreciate everyone’s input, help, and support! Great group of people!
Prevent special characters while you are at it. That comes up on this site like once a week, it seems.
1 Like
One additional option… (maybe not the best, but safest). you can create a BPM that automatically flips the case on the Part.GetbyID and other methods that retrieve parts… this would make it LOOK like it is in upper case, even if the case is lower in the data. Lets face it… the system doesnt care about case in our database. We ignore the case when doing the lookup of the part.
You could also enhance any outbound documents (Orders, Packslips, Invoices) to flip the case while printing so that customers dont see your lower case data.
I see nothing! I never saw anyone say that they should modify SQL directly.
2 Likes
Mostly… (not always).
We have run into scenarios where mismatched case bit us. C# code is case sensitive so anything in a BPM is running on C# so it will be case sensitive. Those little queries that you make in the BPM conditions? Yeah, those are case sensitive. Linq queries? Yeah, those are case sensitive unless you explicitly tell it not to be.
And there are certain BO’s that don’t ignore case when it should.
1 Like
This is a great point! SQL Server’s collation applies no control over the rest of the world that consumes it, so it’s always best (and extremely low effort) to assume case matters.
3 Likes
You are reminding me of some EFx testing I was doing recently.
(Tulip is our MES.)
Emoji are letters, too.
I do not tell my users this.
1 Like
If you put a Pre-Processing BPM on Part.Update with the code below, it will convert any part number that someone tries to create in the future to all caps:
var newPart = Db.Part.FirstOrDefault( x => x.Added() );
if ( newPart != null )
{
newPart.PartNum = newPart.PartNum.ToUpper();
}
2 Likes