Avoid synthetic key for relationship between generic object property and hierarchical structure

[flashfabrixx]

The structure of the data set contains two different structures:
- User -> Job -> Report
- Department -> Machine

The report has a property "objectId" which can be a department (departmentId) or a machine (machineId). The user should be able to filter the jobs by user, department and machine. 

How can I connect the objectId with the "Department->Machine" structure (e.g. to the departmentId/machineId) without getting a synthetic key error?

See the attached file for a sample application with the given structure. The reproduce the error, open the data load editor, access the section "Assets" and uncomment the given blocks.

[flashfabrixx · Accepted solution]

Problem has been solved using the Hierarchy keyword and transforming the input data.

1. Joined departments and machines into one table to prepare mapping (Table: Hierarchy_Tmp)
2. Build hierarchy table based on departments and machines (Table: Hierarchy_Mapping)
3. Update field names into new table (Table: Hierarchy) and drop old tables

By using a separate field "objectId", the selected option from the Reports table can be connected with the full object. 

See the attached file for the working solution and updated script.