I have a scenario simmilar to this:
SQL Server tables
Partner - ID (primary key) - Name Project - ID (primary key) - Name - TechnicalPartnerID (foreign key to Partner) - BusinessPartnerID (foreign key to Partner)
I have created ECT's for these tables in SharePoint Designer and then manually changed the resulting XML to support two references to "the same foreign identifier".
Then I've made an external list based on
When editing the list with the default SharePoint form, everything went fine. The entity pickers showed the correct data and writing to the databese works as expected.
Then I created a custom edit InfoPath form for this list and things started to go sour.
When opened, the two entity pickers show the same value (the value of the first picker). If I change the values and save the form, the first picker's value is saved into both
Note, this happens without any customization to the InfoPath form (i.e. I just clicked "Design Forms in InfoPath" button in SharePoint Designer and published the created form.
Right now I'm thinking there has to be something on "External Item Picker Properties" dialog that I'm missing, but have no idea what.
The "ECT Namespace", "ECT Name", "System Instance Name" and "Display Field Name" from "General" tab are set. Also the "Association Name" and "Identifier 1" from "Other Settings" are set.