Documenting UD table usage

Kinetic ERP
1

Hopefully I’m using the UD tables correctly (System Setup → System Maintenance → Extended Property).

I’m finally able to spend some time figuring out how to implement custom records via UD tables. So far I’ve only modified UD01, but I don’t see any way to track that this table has been customized and is off limits for other uses. Aside from keeping a loose list somewhere it appears the only way to know for sure if the table is used is to go into each table one-by-one and scroll through the predefined columns to see if I’ve entered any custom UD labels.

Please tell me there’s a better way…

image
image928×682 32.1 KB

2

There are probably other ways to do this, but you could do a SQL query to check if Ice.UDXX tables have at least one row of data. Then you’ll know if its been used or in use.

Or this guy:

3 Likes
3

Thanks Jeff. I thought about that approach and at this point it seems like it’s the simplest. Still have to query each table though.

Would be really nice to start migrating custom data into Epicor but with the tables being pre-defined with pre-defined columns it just seems really messy.

Did I read somewhere that the latest versions of Epicor make this simpler and more intuitive? Even changing the default menu name to something other than “Extended Property Maintenance” would be an improvement.

4

Hey Jason,

I do not know what kind of custom data you are importing to Epicor, but you can always add UD columns to UD tables if you want specific results.

3 Likes
5

I second this. Yes, the UD tables have a pre-built set of columns (ShortChar this and Number that)… but you can add as many UD columns to them as you want with better names if you so desire.

You can also use Extended Properties to update the labels on the standard UD Columns to help when looking at them in BAQs, etc.

Yes, takes longer to set this all up, but sure beats having to figure out a couple years from now what UD27.ShortChar07 was used for.

3 Likes
6

… and you can do this anytime you want. Just copy the menu into your company and give it any name you want.

But, just remember, if you change it you have to remember that fact down the road.

For example, our legacy system had a report called “Job Cost”. In epicor, it’s called “Production Detail”. I could rename the report, the context menu, etc. But 10 yrs from now… when most of our users are newer and less even remember our old system… it would be better for them to know Epicor lingo, not lingo we used years ago in a dead system.

They can’t look up help or reference document for the “Job Cost” report… they would need to know what it’s called in Epicor.

So, yes, you can change menu names, edit the forms to rename what shows in the headers… but it could have long term consequence. You’re better of accepting the change and forcing users to learn the epicor naming schemes.

4 Likes
7

Adding my own fields would be great. I can’t figure out where to go to do that. The only view I’ve found that displays UD information is Extended Property Maintenance. Do I have to be in the Classic version to add fields?

In the screenshot I don’t have an ADD button and the overflow doesn’t have the option either.

image
image1410×885 49.9 KB

8

David/Travis,
I think I found it. I have to add the columns like I would any other table, with the UD Column Maintenance, then Regenerate the Data Model. I’ll have to think about this option.

What I want to avoid is a bunch of hidden customizations that no one knows exist or how to find unless you have an intimate understanding of how customizations work in Epicor. Can you imagine the nightmare you’d have if you were hired to work on another company’s epicor solution? I hope Epicor makes it a priority in the future to implement practical ways to reference custom logic. There are little things they can do now from a design standpoint that would go a long way.

I’ll have to read more about the Systems Customizations Tracker by Doug Kros. If that solution provides a dynamic list of customizations then it could be a promising option for now.

1 Like
9

Another gripe then I guess I’ll move on :slight_smile: … I’m using an updatable BAQ to modify the values in the UD table. I can get past the fact that I have to create the UBAQ in Classic version, but that UBAQ does not show up in the BAQ list in Kinetic version. Once again you have to just know that the UBAQ is out there and the only way to manage it is by logging into Classic (which I’m trying to avoid as much as possible).

Anyone know if it’s possible to include the UBAQs in the BAQ list in the Kinetic version?

10

Even worse, if you do like me and never define any labels. Then you have to look at the data in the tables to see what you might have been using it for. I keep a hard-copy list of my UD tables uses. I hate it.

11

Quick Tip if it helps… I DMT a “Definition” row to every UD table I use to provide details.

Example:
Key1 = TABLEDEFINITION
Key2 =
Key3 =
Key4 =
Key5 =
Character01 = {I have 1000 characters to describe what this table is being used for}

I haven’t gone as far as creating a BAQ report to spit this out. But I can query it at any time. You could also spit out a BAQ query like this onto a dashboard and with a click of a button, see what each table is being used for.

image

5 Likes
12

You can make them show up, but since the designer for those isn’t ready, you wouldn’t want to risk opening them.

13

I see. To clarify, you’re storing the value “TABLEDEFINITION” in key 1 in a row of this table?

I wonder if I could use Key1 for this row only. Then use Key2 for the primary key values of my records and the rest of the rows. Am I going to run into key constraints when key1 is null/empty for all the other rows?

14

The Keys can be whatever you want for any of the rows… they don’t have to be the same for ALL rows… in fact, they can’t. The combination of the 5 Keys have to be unique for each row.

For example, I have a UD table for part cross references.

Yes, my Key1 is always PartNum… but my Key2 can be DRAWING, PART, KEYWORD, SPECIFICATION

You just change your query depending on what records you want to access later.

If I want to query against a specific customer supplied drawing number, my query would be where:
Key2 = DRAWING
Key3 = {blank}
Key4 = OWEI (we use a source coding system, this particular code is for a particular customer)
Key5 = the externally supplied Drawing number

This would come back with any rows that had the right combination of the other keys (2 - 5). I don’t even have to include Key1 in my query.

If I wanted a list of ALL externally supplied Drawings… I would just query my UD table where Key2 = DRAWING. Done… many many results.

If I query my table where Key1 = TABLEDEFINITION… I’ll only ever get one row, because I only ever create one row with that Key1 value.

You could have 50 different values for Key1. The table doesn’t care what values are stored there. Its the combination of the keys that are used later when you’re querying the data.

So, for example, you could use (1) UD Table to store both historical PO’s AND historical Sales Order records.

I would just use Key1 values of “PO” and “SO”. So if I wanted to query the table later, I would just need to know that if I want PO records returned, I set my Key1 value to “PO” in my query. If I want SO records returned, I set Key1 value to “SO” in my query.

I would recommend working out your key structure in Excel first. Make sure you can get each row of data to align with any unique combination of the 5 keys.

But having one row with only one key called TABLEDEFINITION won’t hurt anything, because you wouldn’t include that Key1 value in any of your later queries unless you were specifically looking to do so.

2 Likes
15

I thought I had a pretty good approach that would fit my immediate needs. I decided to add a custom UD Column to extended properties (something like recordname_c). I got all the way through this approach to adding the new textbox via application studio.

image
image1017×480 51.1 KB

Now the problem is that I’m unable to modify the value in my new Record Name textbox.

image
image1258×713 39.7 KB

I’m assuming there’s something on the view in application studio that’s causing these textboxes to be readonly?

In the interim, since this is a sandbox company, I just went ahead and modified the values directly on the ZDataTable_UD table. The updated values appear in the detail form for the UD01 table but they don’t appear in the list view (Extended Property Maintenance).

Anyone know why I can’t modify the values in these textboxes?

16

You added your new column to the zDataTable table… but did you add that column to the zDataTable dataview in the Extended Properties app?

I’m assuming you added in the EpBinding to the textbox, but if the dataview doesn’t include that column, it won’t know where to store a value you enter into that textbox. Could be why its read-only.

This would also explain why your grid isn’t displaying values. The DataView doesn’t include that column… so there’s nothing there to reference.

17

Adding to the dataview is a new thing for me. I haven’t had to do that in the past for other custon UD fields, but maybe Extended Properties is different? Typically I simply load whatever app to view the list of records, go to “personalize columns”, and add the new UD fields.

Am I in the right place in this screenshot to do what you’re suggesting?

image
image1748×808 45.3 KB

18

Season 19 Episode 3 GIF by The Simpsons

I’m having a hard time deciphering what’s actually going on here, but it looks like danger.

What is your goal?

19

It feels like danger.

All I want to do is add a field as a “description” so I can document what the UD table is used for and make it more obvious on the Extended Properties list which UD tables are being utilized.

20

I could be wrong (and will accept the public flogging if I am) but I don’t believe there is harm in doing what you’re attempting. You’re not changing any functionality, you’re just adding a reference column to the ZDataTable.

That being said, the ZDataTable has functionality within the system that I don’t fully understand, so, perhaps a more seasoned vet could chime in on any potential impact of taking this approach. (@josecgomez, @Mark_Wonsil, @Banderson)

~~ * ~~

My further assumptions:

The form loads, and at some point (via event) that DataView is populated by a rest call. If that column doesn’t exist in the DataView, then the rest call would have nowhere to store that column value.

I didn’t think about the fact that you were dealing with a Landing Page. I know adding columns there can be finicky, but I think most people who struggle with that may be trying to add columns from different tables.

In this case, the LandingPage DataView is based on the ZDataTable. So, I may have been wrong about adding the column to the ZDataTable dataview… you may have to add it to the LandingPage dataview for your values to appear there.

Further assumption is that your textbox was greyed out because the EpBinding you were using didn’t exist in the ZDataTable DataView, which is why I assumed you would need to add it there.

You could always set the EpBinding of your field to TransView.recordname_c.
image

You then would need an event with a row-update to take the value of TransView.recordname_c and pass it to ZDataTable.recordname_c (its final resting place).

~~ * ~~

As an alternative approach, if this IS too risky. There is mention in the below post of using a UBAQ to update the existing table description column on the ZDataTable. So, you could explore that route.