Walkthrough: Creating an External Item Picker for Foreign Keys

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:

• Creating two external content types.
• Creating an association between the two content types.
• Setting Show In Picker.
• Deploy the BDC Model.
• Create an External List based on your BDC model

Prerequisites

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 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.
   
   
   
6. Drag and drop the Territories table onto the model diagram design area.
   
   
   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.
   
   
   The external content type is displayed on the model diagram design surface.
   
   
   
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.
   
   

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.
   
   
   
3. Drag and drop the connection point to the Territories external content type. A line is drawn between the two external content types.
   
   
   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.
   
   
   
   
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.
   
   

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.
   
   
   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.
   
   
   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.
  
• Click Save.

Go to top →

Deploy the BDC Model

1. On the title bar, click Settings.
   
   
   
2. In the Model deployment URL text box, type the URL of the SharePoint 2013 Central Administration web site.
   
   
   
3. Click Save to close the Settings dialog box.
   
   
   
   
4. Click Deploy.
   
   SharePoint validates your BDC model, and then the Information dialog box is displayed.
   
   
   
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.
   
   
   
3. Click External List, you may need to scroll down the page or page the second page.
   
   The Adding External List page is displayed.
   
   
   
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.
   
   
   The External Content Type Picker page is displayed.
   
   
6. Select the ECT that represents the Territories table, that you generated in this walkthrough.
   
   
   
7. Click OK, to return to the Adding External List page.
   
   
   
8. Click Create.
   
   The external data from your SQL Server database is displayed in the External List.
   
   
   
9. Edit or add a new item.
10. Click the External Item Picker icon .
    
    
    The values from the RegionDescription column are displayed.
    

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