By default the BCS external tem picker only displays the column that is the identifier of the external content type. This is not applicable in all scenarios. This walkthrough demonstrates how to create a BCS external item picker that displays values from a foreign keys column, when editing or creating a new item in an External List.

The walkthrough uses BCS Meta Man for Windows with Microsoft® SharePoint® 2013 and a Microsoft® SQL Server® data source, however you could use any other data source supported by BCS Meta Man for Windows. If you have any questions please submit them on our web site page: Support Request.

This walkthrough demonstrates the following tasks:


You need the following components to complete this walkthrough:

  • SharePoint Server 2013
  • BCS Meta Man – it doesn’t necessarily need to be installed on the SharePoint server but it requires .NET Framework 4.5 installed on the machine where you are using BCS Meta Man.
  • Microsoft® SQL Server® with any database. This walkthrough uses the NorthWind sample database, and the two tables Region and Territories.
  • Two related (“one-to-many”) tables in the external data source. In the Region table, the column named, RegionID is a primary key and the column named, RegionID in the Territories table is a foreign key.

Create two External Content Types

This walkthrough requires two external content types, one for each of the tables: Region and Territories. Create the two external content types (ECTs), by completing the following tasks:

  1. Start BCS Meta Man.

  2. Under data source, click Microsoft SQL Server.

  3. Enter the necessary details. More information can be found in the Connecting to SQL Server section in this documentation.
  4. Click the blue arrow Next to move to the next step.

    BCS Meta Man connects to the SQL server, and then displays the model diagram page.

  5. Click Tables to display all the tables in your SQL Server database.
    List of tables in the SQL Server database

  6. Drag and drop the Territories table onto the model diagram design area.
    Drag a table onto the design surface

    BCS Meta Man again connects to the data source and displays a preview of the external content type. You may modify the external content type name, change identifier and add or delete methods to the external content type.

  7. Click Create.
    Configure the external content type and then click Create

    The external content type is displayed on the model diagram design surface.
    Created External Content Type

  8. Drag and drop the Region table onto the model design area, and then click Create on the preview page.

    Two external content types are displayed on the model diagram design area.
    Both External Content Types

Go to top →

Create an Association between the two External Content Types

To define an association method to identify the relationship between two ECTs, complete the following tasks:

  1. Move the mouse pointer over the Region external content type, and four connection points appear (small squares).

  2. Click one of the connection points.
    Click on any of the squares that appear on the External Content Type

  3. Drag and drop the connection point to the Territories external content type. A line is drawn between the two external content types.
    Drag to second ECT

    The association properties page is displayed.

  4. In the source entity list, select RegionID and in the destination entity list, select RegionID.

  5. Select the use BCS picker for foreign keys check box.

    Configure the association

  6. Click Create.

    The BDC model design area. The line between the two content types has an arrow indicating that there is an association between the two external content types.
    Association created

Go to top →

Set Show In Picker

To select the columns to be displayed in the External Item Picker dialog, complete the following tasks:

  1. Within the BDC model design area, right-click the Region external content type, and then click Configure Entity.
    RIght click the Region ECT and then click Configure Entity

    Tip: More information on modifying an external content type can be found earlier in the documentation.

    The configuration screen is displayed.

  2. Under configuration, click methods.
    Click Methods

    The methods screen is displayed.

  3. On the methods screen:
  • Under methods, select the Finder Method, if not already selected.
  • Under fields, select the column you want to be displayed in the BCS External Item Picker. This walkthrough has selected RegionDescription.
  • Select the Show In Picker check box.
    Select Finder Method, Field, Show in Picker and then click Save
  • Click Save.

Click Update.
Click Update

Go to top →

Deploy the BDC Model

  1. On the title bar, click Settings.
    Meta Man setting

  2. In the Model deployment URL text box, type the URL of the SharePoint 2013 Central Administration web site.
    Set BDC model deployment URL

  3. Click Save to close the Settings dialog box.

    Deploy BDC model file

  4. Click Deploy.

    SharePoint validates your BDC model, and then the Information dialog box is displayed.
    BDC model was successful imported

  5. Click OK.

Go to top →

Create an External List based on your BDC model

  1. Navigate to a SharePoint site where you want to test that you have correcting configured the External Item Picker.
  2. Click Settings, Site Contents and then on the Site Contents page, click add an app.
    Add an app to a SharePoint site

  3. Click External List, you may need to scroll down the page or page the second page.

    The Adding External List page is displayed.
    Select 'External List'

  4. In the Name text box, type the name for your list.

  5. To the right of the External Content Type text box, click the External Content Type picker.
    Click the External Content Type picker icon

    The External Content Type Picker page is displayed.

  6. Select the ECT that represents the Territories table, that you generated in this walkthrough.
    Select Territories external content type

  7. Click OK, to return to the Adding External List page.
    Territories external list displayed on the Adding External List dialog

  8. Click Create.

    The external data from your SQL Server database is displayed in the External List.
    Territories external list

  9. Edit or add a new item.
  10. Click the External Item Picker icon External Item Picker icon .
    Click External Item Picker icon

    The values from the RegionDescription column are displayed.
    relative values

Go to top →
Walkthrough: Mapping Office Item Type Properties to External Content Types Fields →


Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Please do not use this for support questions.
For customer support, please contact us here.

Post Comment