We have been asked several times how you can use the BCS Picker for foreign keys when editing or creating a new item in an External List.
This is what I mean, Imagine you have created the following model
We have an association here between the ProductCategoryID which is a column in both tables.
If we deploy this model straight away and create a new External List from the Product External Content Type, when we go to edit the item look how the ProductCategoryID is displayed
It is being represented as an Int value, which does make sense but does not give our end users an easy experience when they need to update this to a new category id because they need to look up which id to use.
Luckily there is a way we can get SharePoint to use the BCS Picker here so that our end users can pick from a handy list of available categories.
We need to add some attributes to the ‘Products’ ProductCategoryID typedescriptor to tell SharePoint that it relates to our other External Content Type (ProductCategory) the following is that changes we need to make to the ‘Specific Finder Method’ typedescriptor
Original
<TypeDescriptor
Name="ProductCategoryID"
TypeName="System.Nullable`1[[System.Int32]]">
<Properties>
<Property Name="RequiredInForms" Type="System.Boolean">false</Property>
</Properties>
</TypeDescriptor>
Updated
<TypeDescriptor
Name="ProductCategoryID"
TypeName="System.Nullable`1[[System.Int32]]"
IdentifierName="ProductCategoryID"
IdentifierEntityName="ProductCategory"
IdentifierEntityNamespace="BCSMetaMan1"
ForeignIdentifierAssociationName="GetAllProductsByProductCategoryId">
<Properties>
<Property Name="RequiredInForms" Type="System.Boolean">false</Property>
</Properties>
</TypeDescriptor>
You can find the values for the 4 additional properties by looking at the ‘AssociationNavigator’ Method, they are all self-explanatory
names so it should be easy to find.
You should see here we have added the attributes telling SharePoint that this Products TypeDescriptor maps to the identifier
in the ProductCategory (just like as we do in SharePoint Designer here Lookup Foreign Key Values in External Lists
Deploy the model and you will now be able to use the Picker when editing or creating an item
