Utility to update records to uppercase


(Calvin Krusen) #1

A utility that would let you select a case-insensitive table field and report back any instances of mixed case.

And give you the option to convert all instances of the field and related fields to all uppercase.

For example, search on BinNum. If any bin num records have lower or mixed case, convert them to uppercase. This would include references to BinNum like in PartTran, RcvDetail, etc…


(Jason Woods) #2

I know it’s a pain to setup the first time, but I would recommend a BPM that does this for you. A Data Directive in the many places for BinNum could be installed once. (This reminds me that I should probably make this for myself :smile:)


(Mason Zeimet) #3

Edit: Don’t run any queries to directly change the existing bin numbers.

That’s what I just did, created two data directives. One for PartBin.BinNum and one for WhseBin.BinNum. Also ran two small queries to uppercase the existing bin numbers.

Now the BinNum fields can be case insensitive while saving as uppercase.


(Calvin Krusen) #4

You can change existing ones via a uBAQ? Even if they’ve had transactions?

So changing bin e01 to E01 is considered a change in the DB? And do prior references in other tables (like PartTran.BinNum having e01) not causes issues with “new” values (like E01)?


(Mason Zeimet) #5

You’re right, there’s potential issue to be had with changing these columns directly. Good thing we have a test server.


(Bruce Ordway) #6

Or if it is possible for Epicor to fix the standard reports that currently break when they try to process the text values that were stored in multiple cases?

[quote=“Jason_Woods, post:2, topic:50897”]
A Data Directive in the many places for BinNum
[/quote] I just needed this too. Discovered that manual entries for Bins in the “wrong” case were breaking an inventory report.


(Tim Shoemaker) #7

Sometimes people want certain fields forced to upper case “just because”… for example, it is probably best to force the Part Number field in the part table to be upper case. Even though SQL is not case sensitive for this field, Epicor will save it in the case it was created in… in all future references, it will be displayed in upper/lower.
But as @Jason_Woods suggested, it is easy enough to force this to upper case with a BPM. For the PART table, you can create an In-Trans Data BPM that forces Part.PartNumber to upper case on all new Added rows… simply make the PartNumber field = Part.PartNumber.ToUpper(). This can be done in a widget (preferable), or in c# code.


(Simon Hall) #8

Hmmm isn’t this an issue with the Collation of the database? and shouldn’t this be an extended property?

or have I just totally misunderstood the X :slight_smile:


(Calvin Krusen) #9

And do all barcodes treat lowercase as uppercase? Otherwise get ready to update every report that displays a barcode …


(Tim Shoemaker) #10

standard 3of9 barcode does not have lower case, but there is extended datasets for 3of9 and other barcodes as well… Yes, you need to be very careful with any data that is barcoded… But this is also why i preach that Part Numbering “systems” need to have rules, disallowing certain characters that are not bar-code friendly. below is an image of all the valid standard code39 barcodes - note that special characters that are missing include @^&|()[]{};:?!`’ " ~<>_and , (comma) all characters that I have seen people TRY to put into their part numbers.
image


(Calvin Krusen) #11

I don’t see 3of9 codes for " or ' either.

Way back in Vista 4 we had Part numbers like XHEYEBOLT3/8"


(Tim Shoemaker) #12

I corrected the OP to include ’ & "… yes, putting a double quote in the part number is problematic on multiple points.


(Mark Damen) #13

I have seen other people on the forum refer to a fix from Epicor to correct case issues. Perhaps somebody has more details on what the fix actually does.

I know that back in 10.0 early versions lots of the screens were really fussy about case when they shouldn’t have been because the DB is case insensitive. Thankfully all those issues were ironed out, I did have to run an update query back in 2015 when upgrading from 9 to 10.0 to correct PartRev, otherwise we couldn’t do Kanban Receipts.