I am currently doing a migration once more and I am also creating Extended Columns via the Extended User Defined Table Maintenance. I recall, I had a cheat-sheet created with create information from @Bart_Elia - Todd Cash, Erik Johnson and Tony Wilby at that time, when the Epicor Migration Guide had little to no information about it.
Why Not Share it! This comes in handy even if you are not doing a Migration, it still applies in E10.1 atleast when you are creating UD Columns.
The Algorithm
The precision of the column (first part of the definition of the column in SQL) is determined using the following algorithm
- Count every >, <, Z, z, 9, *as a decimal towards the definition of the precision
** If length is less than 5 add 2 to the size
** If length is less than 9 make the precision 10oIf length is less than 15 add 2 to the size- Add the scale of the field to the precision
Cheat Sheet
Applicable During and After Migration
Download: Dropbox - UDDecimals - Simplify your life
Helper Console Utility
Download UD Decimal Test (Basic Quick Console App): Dropbox - UDDecimals - Simplify your life
Migration Insights from Erik Johnson:
“Progress DBs and SQL DBs work differently when it comes to column types. Progress columns have a base type (string, integer, decimal) and then a “format code” that expresses maximum length/digits/etc. But the Progress DB lets you “overstuff” a column –so you can put a 50-character string into a column whose format is defined as having a max length of 10. BTW, I think this is why your decimal issue from the other email is happening.
The Vantage 8 / Epicor 9 client application prevents users from overstuffing columns during data entry, and we have a “business rules engine” (BRE) to prevent overstuffing fields through integrations. But sometimes we can’t catch values imported into the system through other means. The migration tool tries to find these issues before the process begins so the issues can be resolved.”
–Erik Johnson - https://twitter.com/ejtweet