Creating associations between External Content Types in SharePoint 2013 using Meta Man

In this walkthrough we will show how to create by Meta Man associations between External Content Types. 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 accept all default values 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 Type click 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. Now simply click “Generate” button and your BDC model file will get generated.association has been created
  12. Now we need to import our BDC model file. Open up SharePoint 2013 Central Administration. Navigate to Manage Service Application->Business Data Connectivity Service. Click “Import” button on the ribbon.Import generated BDC model file
  13. Use the browser button to locate our BDC model file, or type in the path and filename yourself. Leave all the other options as they are for the time being, and click “Import” Locate our BDC model file
  14. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success page. Imported BDC model file
  15. Browse to the SharePoint page where you want to add the 2 Business Data web parts. Add a new Business Data List Web Part to this SharePoint page. Add Business Data List Web Part
  16. Click on the “Open the tool pane” link Open the tool pane
  17. Click on the icon to show the available External Content Types Open the picker to select your External Content Type
  18. Select our “Northwind_MetaMan.Region” External Content Type, click “OK”. Click “OK” on the tool pane then.Select External Content Type
  19. The external data from Region table is now displayed in the SharePoint Web Part external data from MS SQL table is now shown within SharePoint 2013
  20. Next, Add a new “Business Data Related List” Web Part to the page Add Business Data Related List Web Part
  21. Configure the Web Part to use the “Northwind_MetaMan.Territories” External Content Type. The Web Part will display a message saying the following Configure the Web Part
  22. Now we should set up the connection using the small arrow from the top right of our Web Part Use a web part menu to create a connection
  23. Once this is done we can click on the arrows next to an item in our “Region” web part and the territories related to this region will display in our “Territories” web part Related List Web Part now shows in SharePoint 2013 the association data from two related MS SQL tables 

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/>