BCS External Item Picker for Foreign Key in SharePoint 2013

By

In this walkthrough we will show how to create by Meta Man BCS external 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 Meta Man.

 

  1. Prerequisites:
    • SharePoint Server 2013
    • 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 going to run Meta Man from)
    • MS SQL server with any sample database (for this walk through we are going to use the NorthWind sample database )
    • Two related (“one-to-many”) tables in the external data source

     

  2. Open Meta Man and choose to connect to a SQL Server data source. Select or enter the database name you wish to work with and press arrow in order to connect to selected database.Connect to MS SQL server
  3. Upon successfully connecting to SQL Server you’ll see another window. In order to get a list of tables click “Tables” item in the list on the left pane. Now we can see listed all the tables for this particular database.List of tables
  4. We will be working with two related tables “Territories” and “Region”, to add them to the Model, at first just drag “Territories” onto the “Diagram” Drag a table on the design surface
  5. When you drop the table you will be shown the following dialog, which allows to configure the external content type before creation.  In this example we’ll select all methods and click “Create” button.Configure the external content type
  6. You should have the following External Content Type on your Diagram Created External Content Type
  7. Now drag and drop another table “Region” onto the diagram. And the same way add it to the Model. You should now have 2 External Content Types on the diagramBoth External Content Types on the diagram
  8. Now we are going to add the association. Move mouse over “Region” External Content Type and click on any square point appeared around External Content Typeclick on any square point appeared around External Content Type
  9. and drag to the square point appeared around “Territories” External Content Type drag association to the square point appeared around second External Content Type
  10. When you release the “Association Properties” dialog will show. Select the fields from combo boxes that provide Primary Key->Foreign Key functionality, tick “Use BCS Picker for foreign keys” check box and click “Create” Configure the association
  11. The diagram will update to show an arrow indicating we have an association between the two External Content Types.association has been created
  12. By default BCS external tem 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 “Region” External Content Type. So in order to set the ShowInPicker property right click on “Region” External Content Type on the design surface and choose “Configure Entity” menu item. Manage External Content Type
  13. When the Management screen opens up, click the Methods tab. Methods tab
  14. Ensure you have the Finder method selected, pick the column you want to be displayed in the BCS External Item Picker and tick “Show In Picker” checkbox. Click “Save” button.select the column you want to be displayed in the BCS External Item Picker
  15. Click “Update” buttonUpdate External Content Type
  16. Now all we need to do is to deploy our BDC model. First we need to set URL of where you want to deploy your model. We set this URL by going “Settings” from the title bar.Meta Man settings 
  17. In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.Set BDC model deployment URL
  18. Once you have saved the URL to deploy your BDC model file to simply click “Deploy” button and your BDC model file will get deployed.Deploy BDC model file
  19. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog BDC Model was imported
  20. Browse to the site where you want to add the External List. Click “Site Contents”->”add an app”.Add an app to SharePoint site
  21. Select “External List”Select “External List”
  22. Enter a name for External list and click on the icon to show the available External Content Types External list name
  23. Select our generated Northwind_MetaMan.Territories External Content Type, click “OK” Select External Content Type
  24. Click “Create”Create External List
  25. Your External System data from MS SQL database is now displayed in the SharePoint External List External data from MS SQL database is displayed on SharePoint 2013 page
  26. Now if you edit any record or try to add new item you’ll able to open up BCS External Item Picker in the add/edit dialog open up BCS External Item Picker from the add/edit form
  27. 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 External 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

<Dmitry Kaloshin/>