Imagine we have two tables, Employees and Departments. There is a foreign key relationship where each Employee has a DepartmentId column value set. We are going to create External Content Types for each of our tables.
But when we go to add a new Employee item through the external list, the department column shows as just a textbox and expects us to remember the correct DepartmentId and type it in. How do we get this to be a lookup column so we can select the Department we wish to assign from a list? Here is how…we’ll setup our External Content Types to use an item picker control in the add/update dialogs.
1) Open up your SharePoint designer and go to External Content Types tab.
2) Create two external content types which are connected by a foreign key(e.g. Employee and Department tables). Also, please make sure you have selected Create all Operations for each external content type.
3) When the two external content types have been created, we need to create an association between them. Double click on the Employee external content type and navigate to “Operations Design View”. Right click on the Employee table in the data source explorer and choose “New Association”
4) In the Association configuration window click on Browse button and select the Department external content type.
5) Click on the next button. In the “Input Parameters Configuration” window choose DepartmentId and tick the “Map to Identifier” checkbox.
6) Click Finish button and then press CTRL+S to save the modifications.
7) Open up your SharePoint site and create new external list on employee type.
8) Now if you edit any record or try to add new item you’ll see Item Picker in the add/edit dialog.
9) You can click the browse button and see the values.
<hrayr/>
Nice article. I don’t suppose you have one to do the same from VS 2010?In VS I have 2 entities. A table that hold links plus a foreign key to a Case Study table. And then the Case Study table.I created an association and implemented the generated method to return a collection of CaseStudy entities. But when I create a new Link entry in SP, I still see the CaseStudyID field as a text box. As far as I can tell, no errors are generated.Just as a test I did try using your above example for SP Designer using the same tables and I get the item picker. So I know this can work. There is just something missing or out of alignment so to speak with my VS implementation. Any ideas?Thank you in advance!Gary
Hello,is it possible to show in the employee-list the DepartmentName instead of the DepartmentIDf.ex.“company x” not “1234″
I follow what you given i am able to see list biy department id was displaying in list rather than name and when i click on add item it was giving me errorUnable to find the default new form for list ECT_13. where ECT_13 is list name.please help me in this
Thanx. I was looking exactly for that.
Is there a way to show the description instead of the id?
Hi.I followed this ho-to and I succeeded. Thank you very much.But I have one question: How can I change the description of the picker field? It always has the same name as the associated external content type.Is there an easy way to edit the description (I mean the description in the “New” and “Edit” dialogues).Chris
Hi,Thanks in advance.Your solution working perfect in view/edit/new page. Can we able to show the user friendly name in External list (ReadList) page.Regards,Siva