In my last article on creating SharePoint 2010 referenced external lists you’ve seen how you can create an external list which was connecting to 2 related tables in SQL and display the related field as a picker UI where users can choose the referenced object.
As a result we were able to pick a related object but in a parent list we would get an ID of the item and not a friendly name or for that matter any other column we chose.
In our example with Products as a parent list and Product Model as a list being looked up from, here is the result in the object picker once we pick a value:
Not too friendly, is it? Well, we can choose any column in the Product Model to show up in the picker. In our case, I assume you have already 2 models created as external content types Products and Model. This steps I will go through here will let you upgrade your existing models.
1. Assuming your models were provisioned with SharePoint Designer 2010, open SharePoint Designer and connect to the site where your external lists are provisioned.
2. From Site Objects -> External Content Types, select the Products content type and Export the model as shown below:
Ensure, as you export, you have given it the same name as the the existing model
3. Perform the same (step 2) for the Model external content type and ensure the name of the export is Model
4. Open the Model.dbcm file from where you have saved it in step 4using a notepad or another text editor and locate the node Entities -> Entity
5. Add the following as a child node to Entity node:
<Properties> <Property Name="Title" Type="System.String">Name</Property> </Properties>
As a result your Entity node will look something like this:
<Entities>
<Entity Namespace="http://www.contoso.com" Version="1.1.0.0"
EstimatedInstanceCount="10000" Name="Model" DefaultDisplayName="Model">
<Properties>
<Property Name="Title" Type="System.String">Name</Property>
</Properties>
<AccessControlList>
....
6. Note that the property value in this case has a name of the column I would like to display in a picker, in my case it’s the Name, in your case it can be any column as long as you specify correctly it’s type in the Type property.
7. Save the model file and open your Central Administration -> Business Connectivity Services service application.
8. From the View, ribbon grup, select External Content Types view:
9. Then from the list below delete both (Products and Model) of your BCS models (which you saved and we’re going to import them in a moment)
10. Switch the View to External Systems and delete the external system representing two content types from step 9.
11. Switch the View to BCS Models and delete the model representing two content types from step 9.
12. From the ribbon, click the Import option and import both of the files you have exported in steps 2 and 3 (the *.bdcm files).
13. Now, navigate to your BCS external list or create a new one if you haven’t already. When you edit or create new item you will now have a selected value of the column in your BCS pick lick and shown below:
Enjoy!
