Clayton's SharePoint Madness

In the previous blog post – Business Connectivity Services (BCS) – Creating an External Content Type (ECT) based on a SQL 2008 Table – we created the ECT. Now, we are going to utilize the ECT within the new feature of SharePoint 2010 known as an External List. After we’ve created and viewed our External List, we will begin interacting with the data using InfoPath forms in the next blog post. As a reminder, at the 2009 SharePoint Conference in Las Vegas, Microsoft announced this as #2 on the Top 10 SharePoint 2010 features.  Also, you can keep track of BCS info on the BCS Team Blog.

External Lists are a new feature in SharePoint 2010 that allows us to interact with external data as if it were a normal SharePoint list. As you will see later in this blog post, the External List looks and behaves just like any other SharePoint list on the surface, but beneath the scenes, there is a lot going on. In this blog entry, we will use these tools:

• SharePoint Server 2010 – Team Site
• SharePoint Designer 2010
• Business Connectivity Services (BCS) – External Content Types (ECT) and External Lists

Create External List

Open SharePoint Designer (SPD) 2010 and connect to the same site where you created the ECT. Click on the Lists and Libraries Site Object in the left navigation pane. Upon doing so, you will see a button in the ribbon labeled “External List.”

Fig 1 – Where to Create an External List in SPD 2010

Select External Content Type

After clicking the External List button, you will be prompted to pick from a list of existing ECTs. In the previous blog, we created BizList, so that is what we will select (Fig 2).

Fig 2 – Selecting the ECT

Verify Settings

After SPD 2010 does its work, you will be taken to a new page that represents your External List (Fig 3). You can set the name, description, permissions, and general settings. Note the web address of the list, the list ID, and the fact that it is External. I will delve more into the permissions settings later when I get the chance.

Fig 3 – Successfully Created External List

View Results in Browser

Now that the External List exists, go browse to your site and view the data. You will see that the list name is displayed in the Quick Launch, and then when you click on the link, you will see a very normal list that is displaying all the data in SQL Server (Fig 4). For now, ignore the colors and formatting, because that is related to another blog post coming shortly after this. Upon first glance with no customization, the view would be plain with no formatting. The beauty is that in just a few minutes, you can not only create the connection to the external DB, but you can then display it in a very normal SharePoint list that has a direct tie to the DB, not just a copy of the data.

Fig 4 – Viewing External SQL Data in an External List

One thing that is NOT exactly like a SharePoint list is that you cannot add workflows to the list, because SharePoint doesn’t know how to trigger the workflows. The data resides and changes in SQL Server, so SharePoint is unable to know when to fire a workflow. There may be ways with code to do this or with shadow lists, but workflows cannot be added out of the box. However, workflows on other lists and libraries can be made to interact with an External List through reference.

In the next blog entry, I will reveal another huge improvement with SharePoint 2010, and that is the ability to modify list forms using InfoPath. Not only can you do it with regular list forms, but I will show how to do it with External List forms, which was a big deal to me. I will also do a quick side blog entry showing how SPD 2010 can be used to easily customize views for lists now – another big one for me.

• InfoPath 2010 – Designing External List Forms
• SharePoint Designer (SPD) 2010 – Customizing list views with conditional formatting