Referencing a field from a foreign entity in an ObjectPage table

[former_member564413]

Hi

Can anyone offer an approach to this.

I am working on a use case for maintaining people using business partner design. In this design a person can take on one of more roles. Below is a diagram of the normalised data model I have created thus far which seems to be working to a point.

I have been able to do the following without having to maintain a service.js file. I.e. the CDS engine supports all crud operations out of the box (which is pretty impressive work from the CDS team). Until now I can:

• create a Person,
• store the draft version of Persons and Person_Role
• create a new role (while in edit mode),
• use the search help to search for a Person_Role.ID value where the search-help is referencing the Role entityset

To make the ObjectPage more meaningful to a user, I would like to add a column for Role Name that will show the corresponding name (Role.roleName) for the selected Role ID.

Is there a way to do this while maintaining a normalised data model?

Thanks in advance:

P.S:

I did attempt the approaches mentioned in https://blogs.sap.com/2019/08/21/computed-field-example-in-cap/.

I found that dj.adams.sap approach resulted in an "SQLITE_ERROR: no such column: roleName" when creating a new Person_Role - Possibly requiring some intervention to remove the computed roleName field and then create the insert manually

(see: https://github.com/scongia/or-assistant/commit/cfa0e096e52b98a3a380140df8fa6d2014d50657).

I then tried pdominique-bio approach but this resulted in a compile error on AdminService saying "AdminService.Person_Role" of autoexposed entity for "com.or.assistant.Person_Role" collides with other definition - possibly because to Persons has a @odata.draft.enabled annotation

(see: https://github.com/scongia/or-assistant/commit/994cb183d29c1bb6c3a03423c7599383056733dc)

[gregorw]

Have you tried DJ's aproach using a HANA database?

[former_member564413]

Hi Gregor.

I the error occurs when I create a new person... which I think is an entirely different issue I should solve after getting the Role Name to display.

Working in vscode, I managed to get the new field to appear in the metadata file under the Person_Role entity. I also implemented the code int he service handler but with no success.

    // Fill Person_Role.roleName from from Role.roleName on the fly
    srv.after (['READ'], 'Person_Role', (person_roles, req) => {
        //const tx = cds.transaction(req), roleID = req.data
        return person_roles.map(async person_role => {
            const roles = await cds.transaction(req).run(
              SELECT.from(Roles).where({ ID: person_role.role })
            )
            person_role.roleName = roles[0].roleName
          })
    })

When I debug the service handler I see that the Select is returning the Role entity and is populating the person_role.rolename with the correct value, but this value is not returning in the response. Am i missing something?

[former_member564413]

As mentioned. I managed to get the Role Name to appear by adding a Promise.all to the handler as shown above.

What I observed though is that the when adding a new field in the Service layer, this causes an SQL error creating new records using oData v2, however there is not error when using oData v4

Regards