On the first day of ECHO, my admin gave to me, some notes about my SysRowID!
Relating Epicor Extended Tables via SysRowID
As part of an ERP implementation, it’s not uncommon for companies to find the need to store information that does not easily fit into the ERP system’s existing database schema. Fortunately, Epicor ERP offers a robust ability to extend an existing database table and add fields to store custom data, to better address the requirements of an implementing company, and The Estes Group’s Echo hosting platform completely supports these critical capabilities.
On the technical side, adding UD fields to an existing table actually creates a new table in the Epicor database, a table that is structured to relate back to the original “parent table.” While two tables exist at the database level, the data model at the application level presents the two tables as a single unified entity, which allows customers to interact with the standard and custom data through BPMs, BAQs and form customizations, as if they were working against a single table.
While this capability is of great utility, there are times when an ERP administrator may have a need to compare child and parent tables, for administrative purposes. As such, it is helpful to understand the architecture for relating standard Epicor tables to their UD children at the application and database level. The following example intends to serve this purpose.
Adding UD fields to the Epicor ERP application is done though the “User Defined Column Maintenance” form. In the following example, I added a UD field to the standard Epicor ERP Part table:
Once UD fields are added to one or more tables, the administrator needs to regenerate the data model from the Epicor Administration Console on the AppServer. In the example, I regenerated the data model as follows:
Performing the above tasks adds a “Part_UD” table to the Epicor database:
It also synchronizes the application’s data model with the database:
In the following example, I went on to query the Part UD table. Note the “ForeignSysRowID” field—as you can see, the key to a UD table is based on the SysRowID of its parent, since the records of a UD table exist in a 1-to-1 relationship with their parents:
As such, if you join the Part_UD table to its parent Part table where Erp.Part.SysRowID = Erp.Part_UD.ForeignSysRowID, you will receive results similar to the following:
Whether on premise or in a hosted solution such as ECHO by EstesGroup, Epicor’s database extension capabilities are of great use to implementation teams and end users. For ERP admins, an understanding of the underlying structure can serve to assist in managing the database and the application, should the need arise.
If you liked reading the “First Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.
