BCS item picker for Foreign Key in SharePoint External List

In this walkthrough we will show how to create by BCS Meta Man BCS item picker for foreign keys when editing or creating a new item in an External List. We will consider an example for MS SQL but the same way you may work with any other data source supported by BCS Meta Man.

Prerequisites:
- Microsoft SharePoint Server 2010
- Microsoft Visual Studio 2010 Professional or higher
- BCS Meta Man 2.7.0.0 or above
- Two related (“one-to-one”) tables in the external data source
Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e BCSItemPickerBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “SQL Server” as Data Source type, enter your MS SQL connection settings and click “Connect” button
The Data Source Explorer will now be populated with your MS SQL data
We will be working with two related tables [SalesLT].[Product] and [SalesLT].[ProductModel], to add them to the Model, at first just drag [SalesLT].[Product] from the “Data Source Explorer” onto the “Diagram”
When you drop the table you will be shown the following dialog, we should choose “Database , click “OK”
Accept the default entity name by clicking “Next”
Accept the default “Identifiers” by clicking “Next”
Accept all methods to be created, click “Generate”
You should have the following External Content Type on your Diagram
From the “BCS Meta Man Data Source Explorer” drag and drop another table [SalesLT].[ProductModel] onto the diagram. And the same way add it to the Model.
You should now have 2 External Content Types on the diagram
Now we are going to add the association. Switch to the “Toolbox” pane in Visual Studio (Ctrl-Alt-X)
Select the “Association” toolbox item from the list
With the Association selected, Click and Drag from “SalesLT_ProductModel” to “SalesLT_Product”
When you release the “Association Configuration” dialog will show. Select the fields from combo boxes that provide Primary Key->Foreign Key functionality (“ProductModelID”), tick “Use BCS Picker for foreign keys” check box and click “OK”
The diagram will update to show an arrow indicating we have an association between the two External Content Types
By default BCS item picker is only displaying the column that is the identifier of the External content type. This isn’t so convenient. To select which columns you want to be displayed you need to set the “ShowInPicker” property for relevant columns being returned in finder method of “SalesLT_ProductModel” External content type. So in order to set the ShowInPicker property right click on “SalesLT_ProductModel” External content type on the design surface and choose “Manage External Content Type” menu item.
When the Management screen opens up, click the Methods tab. Ensure you have the Finder method selected, pick the column you want to be displayed in the BCS Item Picker and tick “Show In Picker” checkbox. Click “Update” button.
Now Select menu “Build”->“Deploy Solution” in Visual Studio
Open your SharePoint page and add a new External List by selection on loaded SharePoint page “Site Action”->“More Options”->“External List”
Click on the icon to show the available External Content Types
Select our “ BCSItemPickerBCSMetaManDemo.SalesLT_Product” External Content Type, click “OK”
Click “Create”
Your External System data from MS SQL database is now displayed in the SharePoint External List
Now if you edit any record or try to add new item you’ll able to open up Item Picker in the add/edit dialog
You can click “Select External items” button and see the values of the columns you wanted to be displayed within the BCS Item Picker