SharePoint Designer 2013 External Content Type + same foreign identifier = no go

Question

I have multiple external MS SQL tables that are being used in SharePoint 2016 as External Lists.

There is a database table called Contractthat has two foreign keys: FirstLineManager, SecondLineManager. Both these fields reference the same Staff table.

When using Sharepoint Designer 2013 to I create an association between the FirstLineManager and the Staff. Once I create a new association for SecondLineManager I get an error message that it's not possible to use the same foreign identifier twice.

I can't change the database, so is there any other way to create a second association to the Staff list? The final result should be that the contract edit/new form has two item pickers pointing to the same external Staff list.

Answer 1

As a workaround I created two database views for FirstLineManager and SecondLineManager on the same Staff table. These views I use for the associations.