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.

  1. Prerequisites:
    • Microsoft SharePoint Server 2010
    • Microsoft Visual Studio 2010 Professional or higher
    • BCS Meta Man or above
    • Two related (“one-to-one”) tables in the external data source
  2. Open Visual Studio 2010
  3. Add a New Project
  4. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
  5. Give your project a name i.e BCSItemPickerBCSMetaManDemo and click “OK”

    Create BCSItemPickerBCSMetaManDemo project

  6. On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

    Deploy as a farm solution

  7. If prompted, click “Trial” on the licensing dialog
  8. 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


  9. Click the “Add Connection” button to show the “Connection Dialog”
  10. Select “SQL Server” as Data Source type, enter your MS SQL connection settings and click “Connect” button

    Select SQL Server Data Source and enter MS SQL connection settings

  11. The Data Source Explorer will now be populated with your MS SQL data

    See the tables and views available to use

  12. 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”
  13. When you drop the table you will be shown the following dialog, we should choose “Database , click “OK”

    Choose the model type "Database"

  14. Accept the default entity name by clicking “Next”

    Accept the default entity name

  15. Accept the default “Identifiers” by clicking “Next”

    Accept the Identifiers selected

  16. Accept all methods to be created, click “Generate”

    Accept the methods

  17. You should have the following External Content Type on your Diagram

    External Content Type already on the diagram

  18. 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.
  19. You should now have 2 External Content Types on the diagram

    Both External Content Types already on the diagram

  20. Now we are going to add the association. Switch to the “Toolbox” pane in Visual Studio (Ctrl-Alt-X)
  21. Select the “Association” toolbox item from the list

    Select the "Association" toolbox control

  22. With the Association selected, Click and Drag from “SalesLT_ProductModel” to “SalesLT_Product”
  23. 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”

    Configure the association

  24. The diagram will update to show an arrow indicating we have an association between the two External Content Types

    Association has been created

  25. 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.

    Manage external content type

  26. 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.

    Select the column to be displayed in the BCS Item Picker

  27. Now Select menu “Build”->“Deploy Solution” in Visual Studio
  28. Open your SharePoint page and add a new External List by selection on loaded SharePoint page “Site Action”->“More Options”->“External List”

    Add a new external list

  29. Click on the icon to show the available External Content Types

    Select the External Content Type

  30. Select our “ BCSItemPickerBCSMetaManDemo.SalesLT_Product” External Content Type, click “OK”

    Select the address External Content Type

  31. Click “Create”
  32. Your External System data from MS SQL database is now displayed in the SharePoint External List

    The data from MS SQL is now shown within SharePoint 2010

  33. 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

    Open up BCS Item Picker from the Add/Edit dialog

  34. You can click “Select External items” button and see the values of the columns you wanted to be displayed within the BCS Item Picker

    BCS Item Picker

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

Leave a comment