Video How To: Create a Multi Master Relationship with SharePoint 2010 BCS External List using SharePoint Designer 2010
Synopsis: In this video webcast I am doing the following:
- Using multiple (two) disparate LOB Systems
- Two Independent SQL Server Databases
- Separate Security Model
- No Explicit Relationship between data elements
- Using two External Content Types in SPD 2010
- Using two External List in SharePoint
- Creating a Site Page to establish a Multi Master Page which will allow one to key off a data element and see detail information resulting from another LOB System.
Follow this link or click the image below: http://www.screencast.com/t/NzJiOWMzZDQ
Happy New Year… Stay tuned in 2010 for more SharePoint 2010 from Me! Come out and see me at SharePoint Saturday
- Virginia Beach
and at SharePoint Tech Con in San Francisco
Early Christmas gift from Fabian Williams Get $100 off a Full Event Pass at SPTechCon San Francisco 2010
Merry Early Christmas,
If you were thinking of coming out to the SharePoint Tech Conference in San Francisco in February 2010, let me incentify you with a discount of one hundred dollars (US$100.00) this of course is a screen shot, email me for the actual one. I will be presenting on SharePoint 2010 Business Connectivity Services.
Go Deep with Business Connectivity Services (formerly named the Business Data Catalog), with an
in-depth look at the Object Model. We will demonstrate connectivity to multiple LOB systems and
surface multiple data in a variety of user Interfaces, web and Fat Client.
TECHNICAL LEVEL: Advanced
Please see a full catalog of class and speakers here http://www.sptechcon.com/images/SPTechConCA2010cat.pdf and don’t forget to register for the event at http://www.sptechcon.com
Just Added as a speaker for SharePoint Saturday Virginia Beach. I am looking forward to sharing the podium with my distinguished SharePoint community experts and evangelists. I will be presenting on my favorite topic… Business Connectivity Service and I promise… I will not run out of time this go around.
Come on out… details below
And come and learn how “Composites” will save your LIFE!
First I want to thank Dux, Usher, and Gino for a well put together SharePoint Saturday http://www.sharepointsaturday.org/dc today in the DC Metro Area. Despite today being the first Snow Day of the season for the area, I am told we had over one hundred and eighty (180) attendees. The speaker list was awesome and I tried to attend as much session I could (albeit i had my kiddies with me there today) and I was very impressed with everyone i saw.
I was honored to be there and provide my session below
As requested by the attendees in my session, I have posted my deck for download on SlideShare the code may be accessed from the Blog https://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/
Hope you all had fun and learned something!
Video How To: SharePoint Designer 2010 Beta create External Content Type consumed in SharePoint External List
Screen cast on how to create an External Content Type in SharePoint Designer 2010 Beta and surface data in SharePoint External List.
SharePoint 2010 Business Connectivity Services Teaser with Multiple External Content Types surfacing in One External List
This is my first attempt at a webcast with Camtasia bear with me…
Follow up from: Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB System
Part 2… Consuming the External Data List Columns inside Microsoft Word 2010 through Form Fields
In this blog we will demonstrate the following:
- Reading and Updating LOB System Data surfaced as an Outlook Contact in MS Outlook 2010
- Validating the effort through Testing
We begin the process by picking up where we left off in our previous post Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB System Now we will just treat these contact as any other contact in our Outlook System.
In the screen shot above we notice on the middle Left Hand Side that we are attached to a SharePoint External List called “Test BCS Site Alpha – AW Company Contact” and we have 290 items returned to us. We will concentrate our efforts on the same Sir Michael Sullivan III we used earlier
So under closer inspection above we can see that not only do we have our data we mapped in the External Content Type mapping in SharePoint Designer but 2/3rds down you can see a pane called CompanyContactListDetails where the unmapped data is also present works almost like what Outlook Business Contact Manager gave us. So next I tried to get fancy “jiggy” with it by seeing if I could not only change the fields that were in the LOB system but also what was native as Outlook Properties… but no dice.. it said that the System didnt support it.. I will try to figure that out later..
Yes I used @MeetDux Twitter Profile Pic.. LOL Im sure he will get a kick.
In the end I am just going to modify the Fields that are directly related to the External Content Type and LOB System. I made a change to the Email address and made him Michael.Sullivan@FabianWorld.com
As you can see below in Outlook… the changes were persisted…
As well as in the SharePoint UX .. see below and ….
As well as in the LOB System in SQL Server..
So now you have seen how you can make Updates in a Rich Client such as Outlook to affect changes in the LOB System all the way in the Back End.
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
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
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.
Above we are looking at our SharePoint Designer and our test site. Various information may be ascertained from this view above, please review.
In preparation, we will create a table called “FabianAWContacts” from a few tables in the AdventurWorks database as our Target LOB System Data
To verify our data, we do a select statement against our newly created database…
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.
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.
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.
Once we have completed the mappings, click “Finish” and the new methods will be created as seen below.
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.
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
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.
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
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.
Let us go ahead and do the following to M Sullivan
- Knight him
- and make him the third in his generation
Below you see the complete changes we will attempt to commit to the LOB System.
After clicking “Save”, we can already see in the Browser UX that the changes have been adopted
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.
After we click “Connect to Outlook” we will see that the process “Preparing External List for Synchronization with Outlook” begins
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.
Hope you enjoyed this blog, Have fun with your SharePoint 2010 and BCS! I love it, hope you do too….
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…
- Make the list CRUD enabled i.e. create methods for Create, Update and Delete, we already have Read
- 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
Screen clipping taken: 11/25/2009 1:12 AM
Error/Resolution: SharePoint 2010 Office 2010 when using Connect to Outlook from External List in BCS
After creating an external list of contacts in SharePoint from a LOB System and clicking the Link to “Connect to Outlook” you get a dialog box indicating:
“The Microsoft Office 2010 feature Business Connectivity Services are not installed on this computer…” this is in-fact incorrect as it is installed and functioning. I have my Outlook fully configured and it has my Mysite already in SharePoint lists
Screen shot Below
Proof Positive that the Feature is Installed…
I sent an email to Rolando Jimenez from the Microsoft Product Team for BCS and for the first problem we found out that i experienced that error because my browser was in the “Internet Zone” once i moved it into the “Intranet Zone” my ‘initial’ problem subsided
Now I am getting a “Microsoft Office 2010 component has stopped Working” Error tied to the Common Language Runtime on the bcssync.exe… more help needed here
So, thank you Microsoft Product Team for BCS for helping me with this. I wanted to close out our email thread by summarizing how we got here
· All x64 [obvious but just
· Installed WIN 2008 Server
· Installed and Configured Active Directory and DNS, IIS, Server Roles
· Installed SQL Server 2008 SP1 CU2
· Installed SP 2010 Beta 2
· Installed Office 2010, SPD 2010 and Visio 2010 Beta
· Installed Visual Studio 2010 Beta 2
· Installed AdventureWorks Database for testing LOB systems
So after using SPD 2010 Beta to create an External List I was able to consume it in the browser but when I did connect to outlook I got the error outlined here in my blog
So the steps taken with Rolando’s and Alex’s help ( I will show what worked with excepts of what didn’t inline)
1. After Repair Outlook Failed to fix the issue and After Uninstall yielded the same results
2. I followed Alex’s recommendations below
a. Actually I think I know what’s going on. I suspect the issue is because you have dev10 installed on your machine which comes with a newer version of SQLCE. Unfortunatelly the version in dev10 comes with is x86 only. You need to install SQLCE SP2 x64 from downloads. Here is the link:
b. When I did I still had the same issue but I went one step further
3. I Uninstalled the following Programs and Rebooted
a. SQL Compact x86
b. SQL Compact x64
c. SQL Compact SP1 View and Tools (something like that)
4. I installed the x64 from Alex’s link HOWEVER it told me to installed the x86 first, so I did, and I am up and running with BCS “Connect to Outlook”
Thanks again Rolando, thanks Alex
Here is the link to Microsoft Office 2010 and Microsoft Office 2010 Professional Plus