Accessing and Utilizing External List Data in Microsoft Word 2010 TP from LOB Systems in SharePoint 2010 BCS

So in this blog entry we will be building on our past efforts [below] and investigate how we can use this LOB system data in some meaningful way. In this scenario what we will do is consume External List data in the Microsoft Office 2010 Technical Preview (TP) Suite of Applications. In this example we will use Microsoft Word.

disclaimer: I am using Beta 1 of SharePoint 2010 and Microsoft Office 2010 Technical Preview… For some un-Godly reason, I cannot get the “Connect to Outlook” link in the SharePoint Site Document Library to “NOT” be Grayed out. But regardless, I was able to get MS Word 2010 to work, and I will get a numerical List so i can get Excel 2010 to work also

This Post builds on my previous work so see here…

Installation Procedure for Base SharePoint 2010 Install – Part 1 of 2

Installation Procedure for Base SharePoint 2010 Install – Part 2 of 2

Creating and Configuring your Sites in SharePoint 2010 – Service Applications and Web Applications Part 1 of 2

Using SharePoint Designer 2010 to surface data in External Lists through Business Connectivity Services SharePoint 2010 (2 of 2)

Prep Work

  • Using the AdventureWorks2008 Database we will create a new View and do a Join so we can get some meaningful information back.


Based on the above SQL Join, I have the new View below called dbo.vBCS_AdventureWorksEmployeeDetail


Lets go ahead and test out the View by doing a Select All on the view and so we can see what kind of result sets to expect in our External List [in case you don’t trust me] and you need to verify.



Rubber meets the Road…

So what we are going to do here is create a new External Content Type, sure we could just change on of our previous External Content Type by just changing “Office Item Type” to something other than ‘None’, but where is the fun in that.  But what we will do is use the previous Data Connection and just consume the View we created.  Below we give the External Content Type a name and we have to do TWO (2) very important steps if  you want to use this External Content Type in Microsoft Office Applications.

  1. Change ‘Office Item Type’ to either Appointment, Contact, Item or Post
  2. Ensure that ‘Office Sync for External List’ is set to “Enabled” which is the default anyway


See below for what I completed for my External Content Type


Now where is where the fun begins. In order for your External Content Type to be usable in Microsoft Office 2010, we need to Map External Lists to Office Properties in order to have the data elements bound to the application fields. We also need to select a Picker again in order to build a Profile Page, select individual items from the list and trigger Custom Actions from the Document Library.


Different Data Types yield different property mappings and the example above demonstrates String data type, and below demonstrates DateTime data type.


Once we save off the External Content Type, go to Central Admin, Click Application Management, then Application Services… you will be at the screen below. you should see as you did in previous post the External Content Type that we just created.


Once you complete that verification process, next we need to proceed to the Sample/Example Collaboration Site we created and create another Custom List from an External Source by clicking on “External List” under the Custom List Category. Nothing new here, we did this in my last post.


So after you select External List, you will need to name the List, and identify the Entity Type which will populate the newly created list. See Below.


So… below is our new List with all our data from our External Content Type.


Ok, now that we have our External List created here is where we will make use of it in a Microsoft Office Application; now the implications here are:

  1. We can use Smart Parts in Microsoft Applications to consume the External Data
  2. We can have the Document Information Panel (DIP) auto-populate as we enter External Data into the body of the Document or Spreadsheet
  3. This data is now searchable inside the Document Library and Farm as a whole

Below we have our new Document Library that we will create called “TestingEmployeeinBCS” and we are choosing a “Microsoft Document Template” as the default for all new files created in that document library. 


Below is our Library that is created…


So, in our new library, that will have as its default Microsoft Word documents, we will include a new column and this Column will be of type “Business Data” as denoted below.


Here as we did in SharePoint 2007 we select the Entity out of our list to bind to our data column.  See below…


We will refine our selection a bit here also. along with selecting the First Name, let us also select Last name, there is no limit to what you can select here as an accompanying column.


Notice below that you will see the new Business Data Type along with the name “BCSEmployeeName”


A closer look at the document library will show you the two columns that we created see below…


Next, we fire up our ‘Handy Dandy Word 2010 Technical Preview’ so we can consume the Business Data External List. Note here is where i said that we can put the Business Data into the body of the document and the DIP will auto-populate.


The way we do that is to:

  1. After you found the spot where you want the Business Data
    1. Click Insert in the Ribbon
    2. Click Quick Parts
    3. Go to Document Property
    4. Select the Column Data you need which will…


Allow you to:

  1. Either Type the data you want to call you or
  2. Browse for the data as you would in the Entity List Picker (far right icon)


Now… here is where I had my first Mea Culpa… not dramatic but just an oversight. I should have chose as my picker something more descriptive so when i search for Robert (that guy I’ve been picking on) I get the “ID”; what i should have done is choose a better picker when I created my External Content Type…


SO after you select the number 3 from the above list [just cause i know Robert is #3] I get his resultant dataset in my Quick Part below


You can do the same thing for Last Name and it will automatically populate the last name…


Now we have a document in the making below and when we save it it goes all the way back to the document library see the next two screen shots..



So, in this example we consumed External Data from a Line of Business (LOB) system via surfacing it in an External List and using Microsoft Word in a Document Library to consume the data.  Have Fun!

Leave a comment

Your email address will not be published. Required fields are marked *