I’m much better at DMT and UD tables than I am SQL. I create a BAQ in E9 for the PartTran table just going back for 1 year to start. I’ve settled on 15 columns That have what I think anyone will want. I’ve added a screenshot:
What I’d like to do is load this data into a UD table and create a dashboard to search for a part number and possible a transaction type then view the corresponding data.
I’m beginning to have some doubts now looking at my data. I’m not sure where to load it for one and I don’t know what the effect of identical part numbers will be. Will my part 655001 in my UD table affect my part 665001 in my part master file?
At least I know I can get the data. Maybe I’ll just give them a large excel file of data.
The thing about UD tables is the KeyX fields (Key1, Key2, Key3, Key4, and Key5); they are the index fields for the table. Every record needs to have a unique combination of those fields. I know nothing about your actual data OR what you want to do with it, so I’d probably start by making Key1 your PartNum and Key2 the TranType. You’ll also need the SysRevID and SysRowID fields from your original database… make them Key3 and Key4 (so that each record is unique).
Now go into your new database (TEST or PILOT environment), go into UD Column Maintenance, and create those 15 fields as UD fields in the UD01 table (with the same field names as in the old database… so the old PartTran.TranDate field becomes UD01.TranDate_c). That will make the upload easier, and also make searching through it more consistent.
Do not fear about part 655001 in your UD table messing with your Part master file… it won’t. I promise.
Don’t upload anything yet… make the UD fields in the UD table and let me know how it goes.
I’ve got my UD table done (it’s UD01_UD now) with 15 columns matching my data plus 2 for SysRowID and SysRevID. I’ve regenerated the data model and recycled the app pools. My UD table and Data Model are in sync.
I have copied my BAQ data to Excel and formatted the cells accordingly. I haven’t attempted to upload anything.
It’s still the UD01 table. Epicor has created the UD01_UD table in the SQL database, but you’ll only refer to it through the UD01 when you create queries and dashboards.
Remember, your PartNum will be uploaded to Key1, TranType will be uploaded to Key2, SysRowID will be uploaded to Key3, SysRevID will be uploaded to Key4, and you’ll have an Excel column for Key5 that will have no data. The rest of the fields will be uploaded to the matching “_c” field.
Try uploading your first FIVE records and see how it goes… if they’re successful create a BAQ to look at the UD01 table for a test.
It worked but I might have deviated from your plan a bit. I wasn’t sure how to upload the data so I added the UD01 table as a menu item so I could open it. Once opened I copy and pasted the data, saved it and its there. I don’t know of another way to upload the data. Is there an easier way or did I miss a step somewhere?
Thanks a ton for all this by the way. You rock!
That upload piece is what the DMT is for… in essence, it’s a reverse BAQ. Open the DMT program, and scroll down to the bottom of the “home” page to see a tutorial.
If it all is starting to make some sense, search for the table you’re uploading into (in your case, UD01), and open it. At the lower right of the screen is a button labeled Template Builder… click that. It opens a list of all the fields in the table, INCLUDING (at the bottom) the new UD fields you added to it. Note that the Company field and all the KeyX fields are “selected”… this means they are required fields for the upload. Scroll down to your UD fields and check them all as well, then click “Create Template” and (hopefully you have Excel on your computer) it will create a spreadsheet template file (it’s actually a CSV file) with those “selected” columns as headers. (I recommend you leave the Company and KeyX fields as the left-most fields on the sheet.) Put your data in the appropriate columns and save the file. Now in the DMT program, click on the “UD01 Source” button and find and select that template file. You’ll get error messages if the column headers aren’t EXACTLY the field names, which is why creating the template file is a good idea.
Cross your fingers and click the Process button. Let us know what happens next!
Duh! I didn’t think about using DMT. I will use that to propagate the table. When I have enough data in I will build a dashboard to search and access it. I can assume if it works for PartTran then the same process can work for many other data tables where we may want to access historical data. The CFO has asked about histories by vendor or customer.
Thanks a lot for this help. You’ve made my week.
One more suggestion then… in order to NOT use up all your UD tables, make one of the other Key fields (Key4 perhaps) a reference to what table this data is from. That way you can use just a single UD table for ALL this historical data… which IMHO will rarely be used after the first few months of going live on the new version.
And just one more friendly reminder, your database will be larger adding to backup and storage cost by adding history to the database vs. linking to a database that you wouldn’t back up. Just want to bring it up before you get too far down the road. Unless, as @Ernie’s suggests, you come to the day where you do delete it and reclaim that space. In which case, you’d still archive it…in another database.
With us being on prem i did migrated all of our data from E9 to a vanilla E10 environment and kept everyone with trackers only. The only reason we are using it to my knowledge is looking at historical parts. So they now have all data in a format that is consistent.
