Supporters of End User
Web

SharePoint 2010 - External Lists

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 

Guest Author:Brett Lonsdale

SharePoint 2010 has introduced a new type of List called the External List.  The External List is used for displaying content that comes from Business Connectivity Services (BCS) Enterprise content types.  Business Connectivity Services is the replacement in SharePoint 2010 for The Business Data Catalog (BDC) in SharePoint 2007. 

The BDC provided a read only view of your line of business data allowing you to display the data in Web Parts, Search Line of Business data, and use it within lists and libraries via the Business Data Column.  It was possible to right back to the data source using BDC via your own custom web parts or by using BDC Meta Man.

Business Connectivity Services allows you to work with your Line of Business Data within SharePoint as though it was a SharePoint List.  External Lists provide you with full CRUD functionality meaning you can Create, Read, Update and Delete data all the way back to the data source.  If you are familiar with using SharePoint Lists, you will be at home with an External List.

As well as providing you with the CRUD functionality on your Line of Business Data, you also receive all of the other List advantages such as:  Ability to create Views, additional Columns, Filters, and Security.  The External List can also be added to a web part page as a List View Web Part which now offers Ajax functionality meaning better performance from your web part pages and fewer page refreshes.

As you have probably read from previous BCS articles on our site, the External Content Type is created using either SharePoint Designer 2010 or Visual Studio 2010.  Once the External Content Type is created, you can create the external list either from the Create page in SharePoint or via SharePoint Designer. Below is a screenshot of an External List (Default View).

Every View is manipulated as though it was a SharePoint View, and therefore you can create additional views selecting which columns you would like to display, Categorization, and Sorting options.  The only view type that is unavailable is the Datasheet view.

From each view within the External List, you will find options on the ribbon to make the data available offline which is another new feature of BCS.  Options include connecting to Outlook which is useful for Contacts or Tasks from your Line of Business System (Updates also work from Outlook), or you can use SharePoint Workspace.  SharePoint Workspace is a tool that allows you to work with the List offline and then synchronize it at a later date when you are online.

To Insert, and Update items, a form is used which is selected from the External List ribbon.  The below form is an example of the out-of-the-box New Item form which was created at the time of creating my External List. SharePoint does a good job of recognizing some of the validation options such as Data Type and Required fields.  However, for more complex validation, and for the creation of lookups etc, you may decide to create a InfoPath form from within SharePoint Designer as a replacement.  

To modify one of the forms using InfoPath 2010, you will need to open up your site using SharePoint Designer 2010.  Navigate to Lists and Libraries using the Navigation section, and then click on the external list.  You will see the forms available to edit from within SharePoint Designer.  However, you will have little control over them there! To edit them in InfoPath 2010, click the button on the ribbon to ‘Design Forms in InfoPath 2010’ as shown below.

Your Form will then be shown with all of the available fields on the right hand side in the Fields section.  You now have full control including the ability to format, re-order, rename, add validation rules, add formatting, default values, control types etc.  The only thing that I found frustrating was I don’t have the ability to do an external data lookup.  E.g. lookup a list of Suppliers.

In my example, I am going to add a validation rule.  Each time my stock level is below 5, it will become clearer as the text will be formatted in Red and Bold.

Once you have made all of your required changes, you can submit the form back to the External List by choosing File, Publish, External List.  That is it! Below you can see the form in use within SharePoint with the stock level of 3 highlighted in red.

Guest Author:Brett Lonsdale

Brett Lonsdale is a SharePoint developer who specializes within the Business Data Catalog, co-owner of Lightning Tools Ltd, Co-host on The SharePoint Pod Show www.sharepointpodshow.com..  Strategically (kind of) Brett has based himself in Florida where he lives with his wife and daughter.  You can read Brett’s blog on www.brettlonsdale.com, and also follow him on twitter @brettlonsdale

[tweet]

Comments

Mahak Bhalla

SharePoint 2010 – External Lists

Hi, I have an issue. I have created an external list that also inserts new items into SQL server database. In my sql table there are two columns of “datetime” data type that take value in the following format: 2010-08-14 00:00:00.000 when an entry is made directly or data import is done from excel to SQL server table. but when I use SharePoint external list to insert data to SQL server, it makes a wrong entry for the date. eg. If i select 12-Aug-2010 from date time picker, it submits date as following in SQL table: 2010-08-11 18:30:00.000. It always save sdate of one day before the date selected in date picker of infopath form. How can I save the exact date selecetd from external list infopath form to SQL server. ANy help is much appreciated.

date selecetd in sharepoint external list new item form: 12- Aug- 2010
Saved in SQL table as: 2010-08-11 18:30:00.000

Posted 10-Aug-2010 by Mahak Bhalla
JE

SharePoint 2010 – External Lists

I have the same issue Mahak.... Anyone have any suggestions?

Posted 26-Aug-2010 by JE
Karsten

Same problem...

Hello,
 
do you you have any solution for this problem in the meantime. I have exactly the same issue and I´m still looking for the solution.
 
Thanks for any help..

Posted 25-Nov-2011 by Karsten
Mehrabi

Same Problem

Thank you.
 

Posted 11-Jul-2012 by Mehrabi
Katrine

User environment time zone differences

For anyone still having this issue, this is due to the timezone settings in your sql database versus the timezone settings of the user entering the data. SharePoint automatically uses your windows environment regional settings, but you can change this through your user settings in SharePoint, to match that of the database. I am at +1:00, and the database has 0, so if I enter 1:00 am in my time field, it registers as 0:00 in the database.

Posted 25-Feb-2013 by Katrine
R Tomlinson

Create a Workflow on External List

Do you know if we are able to create a workflow for an external list?
 
The option is greyed out.
 
Any advice is appreciated!
 
Thanks

Posted 22-May-2013 by R Tomlinson
Mimi Wong

My data didn't show up.

Brett,
 
Thanks for your introduction to the useful Content External List.  I was successful to create SQL server database, with 29K records. But I kept getting error messages and the data were not displayed. 
I've primary key from the tables.  Is there any restrictions against the data's description? special characters not allowed (like file attachments naming convention?)
Thanks in advance for your time, I've tried many different approaches but the data still not displayed.
 
Best,
Mimi Wong

Posted 15-Jun-2013 by Mimi Wong
James Nguyen

RE: My data didn't show up

Mimi - with 29K records, you may be limited by the threshhold of the SharePoint list. Try limiting the number of items in your view to 50 or 100 at a time. This may be done through modifying the list view.
 
Regards,
James

Posted 25-Jun-2013 by James Nguyen
Jan Van Belle

update database view used in BCS

Hello, We're struggling with following problem: * the original view contains columns a, b and c * we now require column d How do we refresh the external content type so it uses the new definition of the view? Kind regards, Jan

Posted 25-Oct-2013 by Jan Van Belle

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: