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.

clip_image001

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

clip_image002

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.

clip_image003

 

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

clip_image004

See below for what I completed for my External Content Type

clip_image005

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.

clip_image006

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

clip_image007

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.

clip_image008

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.

clip_image009

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.

clip_image010

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

clip_image011

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. 

clip_image014

Below is our Library that is created…

clip_image015

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.

clip_image016

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

clip_image017

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.

clip_image018

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

clip_image019

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

clip_image020

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.

clip_image021

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…

clip_image022

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)

clip_image023

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…

clip_image024

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

clip_image025

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

clip_image026

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..

clip_image027

clip_image028

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 *