What are the pros and cons of adding UD columns to tables.
We are looking to add about 17 fields to the DMR Corrective Action table.
Would it be possible / better to use a UD table and link it to the DMRCorAct table by Company / Action ID?
Userlevel 1
I use both depending on the application.
UD columns will have a one to one relation with the table you add them to. This can be useful to add additional information about a specific record. We use them for instance to add a UL column to our part table to indicate that a part is UL recognized.
EPICOR creates an added table in the data base for these columns it does not modify the structure of the original table but for the purposes of BAQ’s and the like it is transparent to the user. This is very handy. you never see the added table.
UD tables on the other hand are a separate table which can be related to the parent table meaning you can have multiple records relating to each parent. For instance if you had multiple addresses for each parent record you would want a UD table with several records linked to the parent key.
Hope this helps.
Userlevel 3
There are two different ways to add fields. You can use a true UD table (like UD100), or you can extend an existing table (DMRCorAct_UD). Both get additional columns added by going to UD Column Maintenance.
Extending the tables makes sense if the fields have a 1:1 relationship with the rows in the existing table. Using the UD Table (UD100) makes sense if you will potentially have multiple rows in the UD Table that relate back to one row in an existing table. UD Tables also work well if you need to record your own data that doesn’t relate back to something in Epicor. That is a huge benefit of Epicor.
Pros for extending tables: Added fields can be used in BAQs/BPMs/Screens without extra effort. This means you can write rules around the fields as well (Return Reason must be within a list of choices), you can name the columns intelligently so you know what the column represents (previously you were stuck with ShortChar01, Date10, etc)
Pros for UD Tables: You can add your own columns; you can customize the screens to show your columns, if you need parent/child relationships with data, you can use the UD1XX tables which have child tables as well.
Cons: You have to rebuild the database. This can take a little bit. But overall well worth the efforts because you keep all your data together.
Cons for UD Tables; you don’t control the indexes and your key fields are all strings. This can make things odd when trying to join and when trying to speed up the tables. You are limited to 5 key fields. This could be more or less than what you need. These key fields are Key1 - Key5, so they don’t have the intelligent column names. The rest of the columns can be named properly.
The big task is figuring out what table to extend and evaluate if you need to extend an existing table or use the UDXXX tables.
Yes, these helped me move forward - Thank you
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.