.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Create View in an External List that Gets a Field from Another External Content Type via Foreign Key

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :SharePoint

I currently have two tables in a SQL Server 2k8 database: Locations and Customers

The fields in the Locations table are:

  • LocationName
  • StreetAddress
  • City
  • State
  • ZIP
  • CusotmerID (foreign key joined to Customers table)

The fields in the Customers table are:

  • ID (primary key)
  • CustomerName
  • CurrentBalance

Through SharePoint Designer, I have both tables set up as External Content Types of the same names as the tables and have created External Lists for each External Content Type. All CRUD operations are working perfectly. I added the proper Assocaiation and I've set CustomerName to be the Title of the Customers External Content Type. I can currently use a picker for CustomerID in the Locations List and CustomerName is displayed properly. Also, the default DispForm also correctly displays the CustomerName for any single Location record.

However, I would like to modify the default ReadList so the foreign key (CustomerID) will show the Title field of Customers (CustomerName). However, I am running into trouble finding out how to do this.

Any help with this issue would be greatly appreciated!!


View Complete Post

More Related Resource Links

Changing the field type in an External List

I have created an External Content Type to a database containing document titles and the URLs to those documents.  I created the External content type operations for read item and read list with the title and url fields (there's a number identifier too). I next created an external list using my external content type. It's great - I can see the information I need from the external Database. BUT... The URL field appears as text and I'd like a clickable Hyperlink.  I'm not sure how I can do this.  Any ideas? Cheers,

BCS Method Security / External Content Type Permissions / Custom List Security Provider / Security T


I have an external list setup with the usual CRUD methods.  The external SQL table is also being populated by another source.  I want to enable/disable deleting depending on whether the record was created from SharePoint.  I would also like the normal list permissions to work.  So if a user has permissions to delete on the list, they can only delete items created for SharePoint. 

Where should this logic be incorporated?  On the BCS Delete method, somewhere in the External Content Type or on the list instance?  Most examples I find relate to security trimming for search.  I'm only concerned about the delete method.

I'm sure there are multiple ways to accomplish this.  Which is the best?


Custom Action for specific External Content Type List


I have an External Content Type list instance feature in Visual Studio. Deploying the instance works fine. However, I can't create a custom action using RegistrationId="{$ListId:Lists/yourlistname;}" RegistrationType="List"

Even if I specify the list Id as follows RegistrationId="{8F595340-5D08-4287-8BF6-30D50989D2F4}" or RegistrationId="8F595340-5D08-4287-8BF6-30D50989D2F4" I get nothing.

However, RegistrationId="600" works fine, but for all External Content Type lists.

I added the custom action with SharePoint Designer and it works as expected. When I generate a site template and import it into Visual Studio, the custom action uses RegistrationId="{$ListId:Lists/yourlistname;}" RegistrationType="List"

Creating a new site based on the site template creates the custom action correctly. However, deploying just the custom action does not work (feature deploys, but no custom action appears). I also tried RegistrationId="0x0100D67E541CC4BB6A4A9D62D22657ECFF68" RegistrationType="ContentType" with no luck. The content type id was pulled from the list instance using the object model.

I have to tie the custom action to a specific list instance. Because the li

Edit form List for External content type



I've made a list based on an extenal content type and create an InfoPath form just for viewing details on list items (read only)

My problem is that  the page(displayifs.aspx) displaying this form doesn't have a title (it's blank) and I don't know how to change it.



Sorin Sandu

External Content Type - Read List Operation - "Cannot find a collection element of a supported type.


I'm trying to define External Content Type Operations for an External Content Type. I can add a Connection, Type "WCF Service" no problem. And I can define a "New Read Item Operation" using that connection.

But I can't define a "Read List Operation". I get the following error message, in the Return Parameter Configuration: "Cannot find a collection element of a supported type..." it goes on to say - see the help documentation for supported types. I can't find any help documentation on this.

I've tried returning different types in the provider web service but keep getting the same error. What are the supported types the web service can return?

The reading I've done suggests that you can connect to a non-MS web service using a connection type of "WCF Service". Is this correct? If it is what's the best soap format the web service provider should use - Doc/Literal?



Assigning External content type field column value using Client Object Model


I have a problem assinging External column value to ListItem object with client object model-based application I'm developing. To be precise, I am able to retrieve data related to external content type by reading external list created from this content type but I don't know how to properly use it to assign value to this field. By doing some research on my own I concluded that BDC ID column from external list is the way to go since it uniquely defines selected row from external list but that doesn't tell me much since I don't know what to do with it. Currently I ended up with partial solution - to assign plain string value of picker column but that makes this value visible only in "View Properties" option on Sharepoint and not in "Edit Properties" which pritty much makes sence since it isn't properly related to rest of the data in specific row. Does someone have a better solution for this?

Igor S.

How exactly does a SharePoint 2010 External Content type / list work?



I'm trying BCS on an SQL table which contains about 300,000 rows. Following is what I've observed:

1. A "SELECT * FROM <TABLE>" query fired on the SQL Table using SQL Management Studio returns all the result in 12 seconds
2. The same data when accessed by browsing the default view of external list (to which an External Content Type (ECT) built over the same SQL table in SharePOint Designer 2010 is attached) takes about 3:14 minutes

If I edit the ECT (using SharePoint Designer) and set limit as 100,000, it takes about 49 seconds to load, and further reduction in limit reduces the time to load.

My questions are:

1. What is it that causes so much delay in accessing the rows, as SQL delivers them in 12 seconds? Can anyone please help me understand the BCS / ECT architecture- how it accesses data, etc.
2. Can I implement paging in ECTs created using SharePoint Designer 2010?

Any help shall highly be appreciated. Thans in advance!


Workflow for External Content Type (Associated List) Gives Error


Hello Community.

Background: I have created an external content type, and then an external list from the content type.  Next I created a custom list with the same metadata fields as the external list.  All this works as it should with no issues.  Finally  created a simple 1 step workflow that is designed to update the external list with the data provided in the custom list. 

Problem: When I run the workflow I get the error below:

The workflow could not update the item in the external data source. Make sure the user has permissions to access the external data source and update items. Unknown error
 12/3/2010 8:39 AM Error  System Account

Has anyone seen this before and if so how did you resolve it?



Tom Molskow - SharePoint Architect - Linked-In - SharePoint Gypsy

How to add "date Range" filter in a list of External Content Type



  Does anybody can help to let me know how to add a filter in a list that is external content type ? I created a list has readlist & ReadItem in SPD, but I want this list get data by date range like last 2 weeks. how to set this on filter ?


James Liang

when i tried to create new external content type in sharepoint designer in ribbon it show error like

when i tried to create new external content type in sharepoint designer in ribbon it show error like  "  The server was unable to process the request due to an internal error, for more information about the error, either turn on includeexceptiondetailsinfaults (tither from servcebehaviorattribute or from the <servicedebug> configuration behavior) on the server in order to send the exception infromation back to the client, or turn on tracing as per the microsoft .net framework 3.0 sdk documentation and inspect the server trace losgs.  "

BCS Updater Method in External Content type


HI, I have a List (External Content type ) with CRUD methods written.for example. Employee with fields






If want to update only Remarks field and want to display the other fields.(other field should not be editable). How can i do it throught VS2010. If I made those fields readOnly and preupdaterField true. those fields not displays while editing. I want to show those fields but not to edit. any suggestions????

2)while displaying (ReadList )if I want to display only EmpName and EmpID.How can i do it?


or please try to give some links about all the properties for a Type Decriptor and how they are related with CRUD methods.



Creating SharePoint Server 2010 External Content Type Associations with SharePoint Designer 2010

Learn how to use Business Connectivity Services (BCS) to create associations between external content types in SharePoint Designer 2010 without using code.

Create drop-down filter for External List

I hope this is an easy question. I will have an external list of a bunch of people.  There are several ways I want to display this data: All of them, Only the ones that have a value of 1-4 in the status field, Only the ones that have any other value in the status field, and people that have a value of 1-4 in the status field, but are at a certain location. I want to be able to put a drop-down filter above the list where people can select that. Could someone please point me in the right direction? Time is of the essence.  

Need to link multiple External Content List items to a single document metadata column

I have an external content list - for my purposes, the data isn't any fancier than a list of terms provided by a web service (read only - I don't need update/delete functionality). I want to be able to link any subset of those terms to a single document metadata column (in other words, I need it to work like a normal content list that is defined to allow multiple values in a column) but SharePoint doesn't let me do that on an ECL. I need the content list to be externally driven since more values are going to be added to an outside system over time, but I think it's possible that I may not need a full-fledged ECL list to make this work this way in SharePoint. Just to clarify: I don't need the metadata values in my documents to maintain a PERSISTENT link to the web service (if someone changes an underlying value in the web service DB, it doesn't need to be updated in the document table too). I just want SharePoint to offer up an updated set of source values from the web service if somebody goes in and edits the document metadata. Any ideas for how I can make this work?

Insert Image column in External content type

Hi,   I've created an external content type. It pulls in data from SQL Server Database. I need to have the first column as an image based on the value of one of the columns. Could anyone please suggest how to add a column so represent an image in external list.   Regards, Zia

Excel External Table Data can only create new SharePoint List when Exporting from Excel?

I have a SQL Query that I refresh everyday in Excel. I want to be able to export this list over to a SharePoint list right after my refresh in Excel but I can only Export to a SharePoint list only once. If I try to export again to the same list then it will give me the error "The specified list name is already in use on this server. You must rename the list before publishing it to the server." I have web parts that have connections to this list and I do not want to export my Query to a new SharePoint list everyday. Does anyone know a way around this issue?

BCS : How to set permissions to an external content type's method

guys, Environment: sp 2010, BCS using SPD I have created an external content type from a LOB sql server 2008 DB, created list and form pages, created read, update, delete, edit methods( all the default operations that SPD shows), from the list page i am able to read and write data back the LOB DB also successfully. Also i know there are permissinos that i can set on the external content type but the problem that i am facing is that i dont see any place\ option where i can assign\ restrict permissions of the content type's method to a certain SP group from neither CA nor SPD i.e if i want a certain group to have only execute permissions only on READ method. Would be great if anyone can tell me if at all this is possible. thanks sameer
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend