In this example we are going to extend the HcmEmploymentV2 entity by adding the workers department name as a computed column for the worker. The department name is by standard not available in the entity, so we are going to extend the entity by adding a computed column.
HCMEmploymentV2Entity is part of the PersonnelManagement module. Before we can start we need to be sure that all necessary packages are already have a reference with our model we are working from. Apart from the ApplicationFoundation, ApplicationPlatform and ApplicationSuite packages we also need have a reference to the Dimensions,Directory, PersonnelCore and PersonnelManagement packages, which we can see in the Update model parameters menu -> Selected referenced packages.
Now we are ready to extend the HcmEmploymentV2Entity by Create an extension. We add a Unmapped field string to the extension.
Then we need to create a code extension for the HcmEmploymentV2Entity, which we use to the X++ code.
We can see that the system is creating the HcmEmploymentV2Entity_FOX_Extension class automatically.
First thing we need to do is to get a value of the Worker field as an expression, which we need to use in our SQL script, which will be used in all necessary joins in order to get the department name. To do this we will make a call to the method “returnField” from the SysComputedColumn class and put the value it’s returns in a string. This string will be used as expression in a StrFmt function we are going to use for our SQL script.
Explanation parameters used in the SysComputedColum.
In our example we use the returnfield method from SysComputed class to get the value of the Worker field, which we can use as an expression in our SQL script which is incorporated in a strFmt function.
For the SysComputedColumn we enter 3 parameters:
- Table/viewname => dataentityviewstr(HcmEmploymentV2Entity)
- DataSourceName => dataEntityDataSourceStr(HcmEmploymentV2Entity, HcmEmployment)
- Fieldname=> fieldStr(HcmEmployment,Worker)
Fully written out it looks like this:
HcmEmploymentV2Entity:
Second step to do is to write out the SQL script in a strFmt function.
The information we need, namely the department name of the worker, can be found in the OMOperationUnitView. However in this view there is no direct link to the worker, so we have to made a join firstly via the HCMPositionWorkerAssignment table to get a position from a worker HcmPositionWorkerAssignment and the HcmPositionDetail table to link the position to a department. Once we have the department, we can find the department name.
The SQL query we should write into the strFmt function should looks like this:
In SQL the query gives the following result.
If we written out the strFmt clause it should looks like this:
As our method returns a string we simply can put the word return for the clause.Now we can complete the method and a as a whole the extension class looks like this:
To complete the following configuration needs to done in the Data Entity Extension, in the field properties of the added computed field, here we need to configure the properties DataEntityView method and the Extended Data Type as shown in the screenshot. Property “Is computed field” should be set to “Yes”.
Rebuild and synchronize the solution. To test the solution we can call the view in SQL Management studio with a select * statement. We can also see the computed column with it values:
