I am looking to create some additional fields in a UD table for Budget Goals by Month and Year for specific categories versus GL accounts. I will then use this data in a BAQ to report planned vs actual invoice data. I noticed that a lot of current customizations by my partner go in a ICE.UDxx_UD table rather than the ICE.UDxx table. Is there any advantage / disadvantage to using either? Thanks Tony
This is automatic. When you add your own custom UD fields, it creates the ICE.UDXX_UD table to go along with the ICE.UDXX table. Then the system creates a view that combines them into one result DBO.UDXX.
When you rebuild the schema as part of the UD Column Maintenance, you will see this view/table being added.
Same goes for ERP tables … e.g., ERP.Customer → ERP.Customer_UD and DBO.Customer is the view.
If you are accessing the tables in Epicor they use the views you should never access the _UD tables directly. If you are accessing the tables through direct SQL you should be able to use the views stored in dbo that already have the tables combined.
erp.ShipHead and erp.ShipHead_UD for example are pre-combined in dbo.ShipHead to look like one table.
1 Like
Thanks Doug. Can I just use the Key1 - Key5 fields for criteria data and Number01 field for a budget goal value? I would insert the records using DMT. Key1 = FacilityType, Key2 = FiscalYear, Key3 = FiscalMonth, Number01 would be the budget goal. I would leave Key4 and Key5 blank, but all records would be unique based on the 5 keys. The flip side would be creating add’l fields, but then I don’t have anything to put in the key fields. I could also keep the key fields as above; and create a BudgetGoal_c field. Your thoughts… Thanks Tony
I have seen this done before when you want to be able to name your own columns instead of having everything character01, checkbox01, etc.
You will ALWAYS have to have keys that will make the records unique, but remember that the Key1-Key5 are strings (varchar). To use for joins you may want to put Number01=FiscalYear and Number02=FiscalMonth for joins and easier programming, even if you use them in the Key1-Key3 fields. Then you can use Number03=BudgetGoal.
The rule of thumb I always use, and it could be different based on your business and requirements. For Multi-use UD tables, I use the predefined fields and use Key1 as what it’s pertaining to (not unlike the TranGLC/RelatedTo field). For Single use tables I create UD fields.