Features of a Custom List with External Data

Just like we looked at the limitations of a 100% External List, let’s look at the limitations of a custom list that uses as one of its columns an external data element.  Note that these observations apply no matter if you only have the one external data element or if you include additional fields from the external content type to include in your new list.  First, let’s get out of the way some things that are missing in a custom list when it includes external data.

  1. Folders are still not allowed.  I’m sticking with the theory that when beginning with an external flat file (table) that there is no easy way to map the hierarchy concept of folders.
  2. No version history.  Since SharePoint has no way to track changes to data in the backend database, version history does not make sense, no?
  3. No workflows.  I suppose this limitation also relates to the inability to track changes to data in the external data source.
  4. As with an External List, you cannot connect this custom list to Outlook.

Now let’s look at some of the things you can do with a custom list that you cannot do with the External list version of the same external content type.

  1. You can open the custom list in Datasheet mode.  True, you cannot change any of the external data fields by just typing in new values, but you can update any of the custom fields created through SharePoint.
  2. You can add more custom columns to the list and if you want, you can even edit the column that connects your custom list to the external content type to add or remove additional columns from the external data source.
  3. You can export the custom list to Excel which will include not only the custom columns, but all of the selected columns from the External Content Type.  In fact, you could link your External Content Type to a minimal custom list with just the Title column to transfer all of the data to Excel.  You can also copy the custom list as a table in Access and it too will include the external data.  However, if you link the SharePoint list to the Access table you will not be able to edit the external data fields, but you can edit the other custom list field columns.
  4. The custom list also lets you define an RSS feed for the list.  However the feed only contains the custom fields and the column used to link to the external content type.
  5. You can attach a file to each custom list item.  This is true probably because the custom list has a physical presence in SharePoint while the External List which did not support attachments did not have a physical presence in which to store that link.
  6. Item Permissions are allowed again due to a physical list presence from the custom columns of the custom list.
  7. Finally, you can modify the list item edit form by using the InfoPath button on the List ribbon.  Interestingly, when InfoPath opens, it tells you that the connecting field from the external content type is not supported and will not be available.  If you click OK, the resulting form includes all of the custom fields you added to the list and the additional columns included from the external content type even though you will not be able to edit the external content type fields.  If you decide you no longer want to use the custom InfoPath form, you must go through List Settings à Form Settings (in the General Settings group) and select the radio button: Use the default SharePoint form.  If you do not click the Delete the InfoPath Form from the server, you will start from the existing form and will need to update all of the changed fields.

7 comments on “Features of a Custom List with External Data

    • Johan,
      Sharepoint 2007 had the BDC (Business Data Catalog) with allowed you work with external data but quite frankly was not as easy to use the the capabilities in SharePoint 2010. A resource I used to learn a little about the BDC (before deciding I had too many other things I needed to do and could wait for 2010) was the book: SharePoint 2007 Developer’s Guide to Business Data Catalog by Brett Longsdale which you can find at: http://www.manning.com/lonsdale/ (or go check the used book section at Amazon for a deep discount.)

  1. I’ve create a custom list with a column that has an external content type. Everything works fine except the changes in the SQL table or not shown in my list. Is there a means to refresh the data in an external column from the external conent type?

    • When using external data, if you display the entire external data file as an external list, you should be able to modify, update, delete, and add records as long as you set up the external content data type to support all of those functions. However, when you use a custom list with just an external column added to the custom list, you cannot edit the that column or any of the associated display columns (although you can change which record you want to link to in the external data content type). When used in this way, think of the external data as a lookup in which the displayed field is the referential link column.

  2. How can I export a custom list that contains some external data from one server to another, and still maintain the connection to the original external content type that I developed in SharePoint Designer 2010? Do I need to also export the External Content Type and import it into the new server?

    • External data as defined in an external content type does not physically exist in SharePoint. It still exists back on the original source. With that said, you should be able to export the External Content Type and import to the second server and as long as that server can connect to the data source, it will be able to reconnect and pull the data.

  3. I am finding in SP2010 that If I check more than 26 fields of an external ect the the picker is blank; 26 or less the picker populates. Anyone have the same expereince or guess why this happens?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.