Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB System


Part 1… Consuming the External Content Type through SharePoint Designer in Microsoft Outlook 2010

Part 2… Consuming the External Data List Columns inside Microsoft Word 2010 through Form Fields

Teaser…Extending @ToddBaginski blog where he creates a .NET assembly connector and an External Content Type

http://www.toddbaginski.com/blog/archive/2009/11/05/how-to-create-a-searchable-sharepoint-2010-bdc-.net-assembly-connector-which-reads-from-a-flat-file.aspx

  

Synopsis

In this blog we will demonstrate the following:

  • Create a Database Table from a SQL AdventureWorks Database
  • Use SharePoint Designer 2010 to create and configure an External Content Type on the newly created Table
  • Create an External List in SharePoint Browser User Interface (UX)
  • Test – Read, Update External List Item from UX
  • Connect to Outlook from the Browser UX
  • Test – Read, Update from Outlook UX
  • Teaser – Create a Document Library and include Library Columns from the External List LOB Data
  • Pre Teaser – Build out Todd Baginski’s Blog by associating a Flat File External Content Type and SQL LOB System

Process

We begin the process by identifying a Test Site in our SharePoint environment to host our External List and creating a Table within the AdventureWorks Database to surface data from in our LOB System.

clip_image001

Above we are looking at our SharePoint Designer and our test site. Various information may be ascertained from this view above, please review.

clip_image002

In preparation, we will create a table called “FabianAWContacts” from a few tables in the AdventurWorks database as our Target LOB System Data

clip_image003

To verify our data, we do a select statement against our newly created database…

clip_image004

Above we have the initial steps of creating an External Content Type; in this phase we begin by providing a name, the Office Item Type (we will get to this later and is the crux of our work in this blog), and define the Connection to the External System. Below you will see how we make a connection and identify the LOB System Objects we will be surfacing data from.  It is here also where we will be making our CRUD Methods and assigning permissions.

clip_image005

The screen shot above displays the database and the table we created earlier. By right-clicking on the data table we will be selecting the option “Create All Operations” which in essence defines what and how we will interact with the data in the LOB System.  You can see from here also there remains the possibility to do other actions including associating to other LOB systems and establishing relationships with other disparate databases.

clip_image006

Once we choose the operation (Method) we will be taking on our External Content Type (CRUD) if we are undertaking Microsoft Office Outlook Off-lining then we need to make mappings between the LOB system and the Office Client, see below.  You will also notice that the different data types are associated with different mappings.

clip_image007

clip_image008

clip_image009

Once we have completed the mappings, click “Finish” and the new methods will be created as seen below.

clip_image010

As seen above or below, once the process of provisioning is complete you see and Identifier is associated (Primary Key and value that is used to trigger custom actions in the Browser UX. The green arrow below indicates that the association we make by selecting “Contact” in the ‘Office Item Type’ has the requisite mappings to enable it in MS Outlook.

clip_image011

After saving off our External Content Type we will next go to the SharePoint Browser UX and create an External List and point it to the External Content Type recently created. See below

clip_image012

clip_image013

clip_image014

The steps taken in the above three screen shots demonstrates how to create a new External List and associate it to an External Content Type. I have decided to create an External List called Company Contacts [actually AW Company Contacts, I changed the name after my screen shot], and I have associated it to my Company Contact LOB List.  Once the configuration and nomenclature is complete I click Create and the List is displayed below.

clip_image015

So lets begin the verification process.  We will target Contact ID 1003 – Michael Sullivan

Let us first check in SQL what the dataset should look like since it is the Source of Record… see below

clip_image016

So in the External List we will edit the ID 1003 for Michael Sullivan. Now because in our Methods Operation we elected to do Method Updates, when we click on the custom actions we will see an Edit menu, by selecting it we will see the window below.

clip_image017

Let us go ahead and do the following to M Sullivan

  • Knight him
  • and make him the third in his generation

clip_image018

Below you see the complete changes we will attempt to commit to the LOB System.

clip_image019

After clicking “Save”, we can already see in the Browser UX that the changes have been adopted

clip_image020

Next we check the same SQL Statement and we can also see our changes persisted to the database.  Also from this window we will make the connection now to Microsoft Outlook 2010 since we have already identified this External List as a Microsoft Office Contact Type.  By clicking the “Connect to Outlook” in the ribbon above it begins the process as we will see in two screen shots below.

clip_image021

After we click “Connect to Outlook” we will see that the process “Preparing External List for Synchronization with Outlook” begins

clip_image023

clip_image024

clip_image025

clip_image027

clip_image028

Once the above process concludes we can look below and see that the AW Company Contact External List (middle of the screen Left Hand Side) and also demonstrated by the Business Cards that the LOB External List is now available in MS Outlook 2010.

image

Hope you enjoyed this blog, Have fun with your SharePoint 2010 and BCS! I love it, hope you do too….

 

Teaser below

So my good buddy Todd Baginski who I met for the first time at SharePoint Conference 2009 LOL 🙂 he created a great blog entry on HOW TO: Create a Searchable SharePoint 2010 BDC .NET Assembly Connector Which Reads From A Flat File which is very inspiring.  I took his solution and used it as the foundation for what I will be doing in my Part 3/ Teaser to this Blog.  In Todd’s example he took you up to the point of reading the External Content Type in an External List in SharePoint.  this all being done in Visual Studio from scratch so YES finally WE WILL CODE!

I will take it a few steps further…

  1. Make the list CRUD enabled i.e. create methods for Create, Update and Delete, we already have Read
  2. Making an association to a Table in a SQL LOB system and the Flat File so we can demonstrate two disparate systems coming together in SharePoint where they were previously not related

I am well on my way, I have the solution already created in Visual Studio and i will go in detail into the code of  the CRUD Methods, for the lead up please see Todd’s Blog, he does a much better job of explaining

clip_image001[5]

clip_image002[5]

clip_image003[5]

Screen clipping taken: 11/25/2009 1:12 AM

Advertisements

November 25, 2009 - Posted by | Office 2010 Beta, SharePoint 2010, SharePoint Administration, SharePoint Development, SharePoint How-To | , ,

1 Comment »

  1. […] your Sites in SharePoint 2010 – Service Applications and Web Applications Part 1 of 2Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB SystemInstallation Procedure for Base SharePoint 2010 Install – Part 2 of 2Using SharePoint Designer […]

    Pingback by Consuming the External Content Type through SharePoint Designer in Microsoft Outlook 2010 « Fabian Williams's Blog | November 25, 2009 | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: