Parent table columns in BCS external list

Question

Hello,

I have created an association for external content type in sharepoint designer 2010. Using this content type I have created an external list. In this list, the foreign key column is getting displayed, which is an ID column.

Is there any way by which I would be able to see other columns of parent table in the external list?

For example, I have 2 tables -

Department (Id, Name) and Project(Id, DepartmentId, ProjectName)

I have created 2 content types for both tables and also an association for Project table. In my external list created using Project table's content type I want Department's Name column instead of DepartmentId column.

---

shruti

Answer 1

Hi Shruti,

It's not easy to implement it please take a look at this thread Join external data

I would suggest you to create stored procedures for your tables and association and use them. Using stored procedures will allow you to make ReadList operation as you wish. You may join two tables in it.

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 2

I'm having the same issue.  I really don't understand how this simplest of requirements is not supported OOB.  The ReadItem picks up the assoication, the Edit Item picks up the association, but not the default ReadList?  I'm sorry, I just don't understand sometimes the designer thought process.  Why would one want to see meaningless IDs in a data list?  Or at least configuration capabilities would be appropriate.

Is there really now other way, but to do this JQuery customization?  I mean I have about 10 fields of the same nature, and doing customizations for all of them seems like such an overhead :(

Shruti, were you able to get this to work?

Thanks,
Ilya

Answer 3

Hi Ilya,

External list displays the columns described in the returned parameter of ReadList operation. So the best way to implement it is to use stored procedures or views (but you can't use Creator/Updater/Deleter methods for views).

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 4

Dmitry,

Thanks for the answer.  But with that logic, why does ReadItem then, for instance, pick up on the assoications?  It also only has the id described in the returned parameter list.

When you speak of stored procedures, how exactly are those utilized?  What is the flow?  Also, would writing a custom BCS connector in VS solve the issue or that's too complicated?

Yes, creating a view works, but only for "viewing" unfortunately.  I guess a workaround would be to have two lists, one for viewing, one for editing.  But I can already sense dissatisfatcion from that appraoch.

Thank you,
Ilya

Answer 5

Hi Ilya,

Unfortunately ReadList operation can't work with association like ReadItem operation.

Here you may find how to create External Content Type against stored procedure.

• A step by step guide in designing BCS entities by using a SQL stored procedure

You shouldn't create a custom BCS connector. For your needs you may create .net assembly connector. Take a look at these articles:

• How to: Create a .NET Connectivity Assembly
• Creating a .NET Assembly Connector
• BCS - developing a .NET assembly connector presentation and demo

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 6

Thanks, Dmitry.  From reading the stored procedure article, I understand all that is also just for viewing and filtering.  The stored procedure doesn't let you add new or edit existing items, correct?  My main problem is to display the "joined" titles, not Ids, and still be able to add/edit items preferably in ONE list.

Ilya

p.s. yes, i meant the assmebly connector, not a BCS one.  i'd really rather stay away from Visual Studio in this case, if possible.

Answer 7

Hi Ilya,

Using stored procedures you may implement all CRUD operations for the ECT. Please take a look at this example Creator and Updater BCS Methods using Stored Procedures – BCS Meta Man

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 8

Hi Ilya,

I got it working using stored procedures. Unfortunately this functionality is not supported using OOB. I have created all operations - Finder, SpecificFinder, Creator, Updater and Deleter using stored procedures.

---

Regards, Shruti

Answer 9

Thanks, guys! 

Shruti, did you have to use VS for creation of any of your CRUD operations?  I'm guessing Finder and Specific Finder hooks on to Read List and Read Item operations respectively?

I'm wondering if all these stored procedurs can be hooked to a specific operation directly by right clicking on a SP and saying "create a read list operation", for example, just like they do in one of the articles mentioned in this thread. 

Is this possible to do with the rest of the methods without VS?  I see that BCS - Meta Man is not free, so that's why I'm asking.

Thanks,
Ilya

Answer 10

Hi Ilya,

Yes, Finder is ReadList and Specific Finder is ReadItem. Yes, it's possible to create all CRUD operations based on Stored Procedures for the ECT in SharePoint Designer. So you shouldn't use VS. But SPD supports only MS SQL stored procedures. Take a look at this article Auditing with SharePoint 2010 Business Connectivity Services (BCS)

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 11

Hi Ilya,

I created all CRUD operations using SPD and not VS. And also I agree with Dmitry about MS SQL stored procedure support in SPD.

---

Regards, Shruti

Answer 12

Hi,

For the Create, Update and Delete I hooked up to the SQL table.  In your case I believe this would be the Project table.

I then created a view in SQL which showed all the columns I required (for you this would be all the columns in the Project table plus the join to the Department table) and used this for the Read List and Read Item methods.  In the Read Item method I made sure the fields that did not appear in the sql table (Projects) were marked as read-only (Department Name).

Thanks,

Mark