Using a Regular Content Type with an External List Column as part of its properties then Consuming in MS Word

So I love it when my fellow Twitter Colleagues come up with Ideas and I Proof of Concept it in SharePoint 2010, in-fact, I think we should come up with a “Stump the SharePoint 2010 Guys with Beta Bits” gig…

Nick Hadlee (hope you don’t mind me using your name) @nickhadlee had a question “Can #SharePoint External Data columns be included in Normal Content Types or only List Columns”

So we put it to the test…

Activities

• Create a Content Type as you would do in SharePoint 2007
• Type Document
• Create a “New” Site Column in the recently created Content Type
• Site Column is of Type “Lookup Column” – to an External List which contains LOB System Data
• Create a Document Library which is defaulted to MS Word Document
• Allow for management of Content Types
• Add the Content Type we created
• Open a Word Document from that Template
• Consume the External List Data from the Lookup Column

So we need to create a new “Content Type” by clicking on Site Actions, View All Site Settings…

Click “Create” for the new Content Type

Name new New Content Type as you would in SharePoint 2007 as above

So.. we cheat here by creating our new “Site Column” and we will do a ‘Lookup’ to an External List…See below for the walkthrough for that.  See the second screenshot below for the link to the External Data List.

Once complete you will notice the Newly Created Column called “NickEmployeeCol” and of type “Business data”

Next we create a new Document Library that is defaulted to hold MS Word Documents, we click ‘Advanced Settings’ under General Settings category so we can “Manage Content Types”… nothing new here…

And we go ahead and click “New Document” below to get a Word template

So in the above and below we have a test document library and we are going to use the new content type we created… see below and above

As we did in previous post, we have both the DIP and the ability to consume the External List data from the Quick Parts menu in MS Word.

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!

Using SharePoint Designer 2010 to surface data in External Lists through Business Connectivity Services SharePoint 2010

 

Part 1: Surfacing Data through SharePoint Designer – No CODE! yes, i said it, NO CODE!

Part 2: Surfacing the same Data through Visual Studio – Yes we will crack the seal and Code Away

Part 3: Consuming the Surfaced Data in Outlook Applications and other User Interfaces

 

In this blog I will demonstrate in my opinion “one of” if not “THE MOST” value added component in SharePoint 2010; accessing and surfacing data in Line of Business (LOB) systems with the ability to Read, Write, and Delete in Real-Time. 

So to accomplish this the first thing I will do is create a new Site using the Empty Site Template, Ill dub it BCS Example Alpha. Dont fault me for that name, fault the Military…

Once I have completed that process, which is almost the same as you do in MOSS 2007. See my previous post on how to create sites at Creating and Configuring your Sites in SharePoint 2010 – Service Applications and Web Applications Part 1 of 2 for a step by step process on how to accomplish that.  Anywho… once you have completed that process you will then use SharePoint Designer 2010 to open your new site. <aside> so I used to be the one ranting about how SPD 2007 was just FrontPage on steroids and I would only use it to design pages and nothing else, now I am eating crow </aside>

There is sooooo much you can do now in SPD 2010 especially extending solutions build in Visual Studio and writing back to the same .WSP file yep, same DLL. Isnt that freakn awesome. So back to where were were…

 

Above you will notice the Site “BCS Example Alpha” is now opened in SPD2010 and you have details on the site to include:

• Basic Site Information
• Site Permissions
• Any Customizations
• Any Sub-Sites
• Basic Settings

Next, we need to create an External Content Type, we do this by clicking on the “Entities” link in the Navigation Pane which will give you an option in the Ribbon called “External Content Type” under the ‘New’ heading. Go ahead and click that bad boy…

Once you have clicked new “External Content Type”, you will be presented with the window below.  Basically, what you are doing here is defining the arms around what was previously your Application Definition File… thank GOD, no more XML [for now], but you are defining your:

• Entity Nomenclature
• Data Source
• Version of your Content Type (app def file)
• Permissions
• etc…

 

Here is a good time for us to look a little closer to our Data Source, after all that is what we are going against right. So we are jumping to SQL Management Studio 2008 and looking at the AdventureWorks2008 Database for x64bit systems; you can get it from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34032 and install it.  So… what I am showing you here is the Table that i am going after and also pulling some data so you can see it isnt SharePoint magic. NB. Pay attention to Item 3, we are gonna steal his identity in a bit.

I am also going to create a View on this guy also because more than likely you are not going to want to have direct access to your underlying table [right, right..] so I created a view aptly called “PersonViewforBDC’ and again you can see the output in my Query Window.

So now we are back to SharePoint Designer 2010 and we are going to name our Entity and create our External Content Type [previously our App Def File] with NO … say it.. No Code… No XML

Above we are calling naming the Display Name, Programmatic Name, and leaving the rest to the default; which in and of itself is no short order, we are saying here we want:

1. Offline Sync for the List

Which is awesome! Take you data go off to the client, manipulate it and come back and sync.. hmmm Windows Mobile, Laptop, etc.. sky is the limit here

In the next few steps here is what we are doing

1. Creating a new Data Connection
1. click “Add Connection”

 

2. Define the type of Connection, in this instance I am electing to choose a “SQL Server” connection but the sky is the limit here also.

3. Provide the Server Name, Database Name, and an Optional Name, which is what shows up in the “Data Source Configuration”, “Entity Type” section when you are creating your External List in your Consuming Site.

4. After you click Ok, you go through a validation step as your Entity connects to the Database and Creates your Data Source for SPD 2010

5. Once that process has complete, you will see the window below which shows you your Database Connection, All Tables and Views that you have permission to see and manipulate.

6. As I am a Database person at heart also, and although it may seem weird, yes, I created a View for this demo, and it is that view that we are going to target or data to surface. So go ahead and Click on your Table or View. In my example I am using my view called “PersonViewforBDC”

7. Now here is where I earned new respect for SharePoint Designer 2010; you can just now “Right Click” on the Table or View and select from a myriad of options [Methods] to create. In this example I am going for the fences. So, I clicked on “Create All Basic Operations” and …

8.  Next you will follow a 3 dialog Wizard which allows you to again exercise some control over the data you are exposing. You may at this point

• Define the Properties – Nomenclature
• Apply Parameters – Define what will be the Picker i.e. the item in your SharePoint List that will trigger your custom actions or jump you to the details of the row
• Apply Filter Parameters – Narrow your Result Set

9. Once you are finished, Click “Finish” and after a few seconds, you will see the below Status Message and Eventually your Entity Created. Make sure to Save your Content Type by Clicking the Save Icon at the top LH corner of SPD 2010, regardless if you don’t, you will be prompted to save your work anyway.

10. Yep, you are done… that easy..

Although I believe it worked, you may be a bit skeptical. So for this demo, I will go to Central Administration and investigate if my BDC/ BCS entity was created. So first thing you have to do is:

• Under Application Management
• Click “Manage Service Applications”

• Because this is a Business Connectivity Services (still called BDC in this release but when RTM comes out I will bet you $1 it will change)
• Click “Business Data Catalog” and click “Manage” in the Ribbon

Once you click on the Ribbon you will be taken to the browser below where you can see all the External Content Types that are hosted on that Server Farm. Below you have the hard work we completed in SharePoint Designer 2010 with … say it… No Code!

The Moment of truth, what you paid your Money For!

Now let us consume that bad boy; so now we go back to our Site we created and we are going to do some really simple steps. 

1. Click Site Action

2. Click “More Create Options” because what we want to do here is create an “External List” which is under “Custom Lists”

3. Click External List

4. Define the Nomenclature for your list and here is where you will also select the External Content Type created earlier.  Easy Peasy huh..

To get the Window Below just Click the far right Icon under the “Data Source Configuration” section by Entity Type…

Select your Entity Here… and Click OK

Once you have Clicked “OK”, Go ahead and take a deeeeep breath, Click Create and Voila! there you have it.

Your External List is now created in SharePoint and it is a “Dynamic” representation of your LOB system Data in Real time… but wait… I did say we can perform Live Updates didn’t I?

Lets get our sanity check first….”Trust, but Verify”! So here is my SQL Query where I am looking at my data source… pay close attention to List Item 3 Mr. Roberto Tamburello

In SharePoint, in my newly created External List under my Picker I choose from when I defined my Content Type, I clicked on my down arrow and select “Edit”

Here is the Before below…

I am modding this guy to the following:

• Making him a Knight
• Giving him my name as his Middle name
• and making him a Junior

Poor guy, just cant get a break.

Once I click “OK” notice that in the SharePoint External List, it is already represented as a changed item…. but what about good old LOB System SQL…

If i do my Select Statement… what do we have… YES! he is changed there also!

So in summary, without a stitch of code. We were able to surface data from a LOB System, bring it into SharePoint, manipulate it and Persist it back to the Original Source.

Hope you enjoyed! More to come.. feedback welcomed

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

 

So far we have installed SharePoint 2010 and we have configured a basic install and configured Enterprise Applications in Installation Procedure for Base SharePoint 2010 Install – Part 1 of 2 and also in this post Installation Procedure for Base SharePoint 2010 Install – Part 2 of 2.  Now we will look at how to create a Collaboration Site and basic configuration techniques as well as pulling back the cover on our Deprecated Shared Service Provider now called Service Applications.

So we will do two things in this post and step into high gear in the second post.

1. Create a new Collaboration Site
2. Overview of the new Service Applications (previously called Shared Services Provider [SSP])

In Part II we will

1. Configure the SSP
1. Look at User Profiles
2. Search
3. Business Connectivity Services (I will be turning this mutha out)

 

So even though you are in SharePoint 2010 the process to create a new Site Collection is relatively the same with minor exceptions to the types of templates now available to you and the process to get to creating new Web Apps and Site Collections. So first “Click” Application Management in the quick launch section as seen below…

After clicking on Site Collection we will be creating a new site under the Managed Path of /sites…

Notice the new Templates above… yeah.. lots of cool stuff in 2010.  Anywho….the screen shot below demonstrates the creation of the new site along with the services that are running on this server which may be consumed by this Web Application.

Previewing the New Site will yield.

Certainly on item of interest here that I would like to point out is the nuggets now in the Collaboration Site in terms of Document Libraries and Lists now available. One innocuous feature which warrants a blurb here is the fact you can from the web interface ‘easily’ add/modify images local to your computer against a MOSS 2010 site.  The Site Assets Library is where these images will be stored once you upload it to the SharePoint 2010 site. See a complete list of the new Lists and Libraries that are a part of this new Web below.

Looking at you Site Settings is still accomplished by clicking “Site Actions”, “Site Settings” and you will see the new interface and notice the new configuration options under Site Collection Administration, Galleries, etc. below…

For a full gist of what you can do under Application Management see the below two screen shots below. What is impressive is that you can manage what was previously separate in one area by managing both Web Applications, Site Collections, Service Applications  and managing your Content Databases. See Below…

I will do a Web Cast where my actions will be more fluid for the management of sites. Screen Shots here probably wont help as much with the depth of where you can take this animal. So I will leave off with one of the best additions I have seen for the health of your SharePoint Site. Monitoring and Reporting… see below

 

So looking at the screen shot above and below you can clearly see that there are alot of tools in the bag. I will touch on one aspect in this post because the ability to auto-correct is really impressive.  So to make sure I had stuff to demo, I purposely used fewer Service/Managed accounts for my services and one thing I cant help but it still shows up is also denoted [My database is on the same box as my SharePoint Farm] so take a look below and we will see how to handle some of these issues in my webcast.

Ill end here, but my Part 2 will go in depth into Service Applications (what is new, what is better, what is different) and how to manage your new Web and Service Applications

Hope you enjoyed!

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

In this Blog we will look at some of the basics to Configuring SharePoint 2010 in the newly designed Central Administration

Below you will see a screen shot where we left off in Part 1 of 2.  You have the option of configuring your farm using a wizard or managing the process yourself.  I opted to use the wizard but before do and you can certainly get back to this window by clicking the link “Configuration Wizards” in the quick link menu Left Hand Pane, but before doing so, we need to create and associate our Service Accounts as we did in MOSS 2007. In SharePoint 2010 you click on Security and then “Register Managed Account”

Create Managed Accounts in order to associate your Service Accounts. Below is an important step in that process. In SharePoint 2007 Best Practice we were instructed to create up to seven (7) accounts such as denoted below. I have included the same accounts however in the configuration of SharePoint 2010, I have decided to employ the same strategy however with the new services that are in MOSS 2010, and the fact you can have your services specialized and targeted, one can employ a nomenclature and provisioning process reflective of that.

So… because this is a single server install and I will be doing it with new bits in 2 weeks, i have decided to use only two (2) service accounts. What is noteworthy is that you can ALWAYS come back and modify the services and service accounts that are tied to your farm. In fact, you can click “Monitoring” and have a health check ran against your model which will pop up in a “Security” category. This will tell you if you are in line with Microsoft vision or adrift. It will also allow you to auto-correct some of your configs.

 

Security

http://fabianwin2k3r2:33397/security.aspx

To create a new Managed Account which in turn will be a Service Account on your Farm you

1. Click on Security in the Quick Launch Menu
2. Then Click “Register Managed Account” from the top of the second frame. You will see a window as shown below. Fill it out and click OK. This should be a domain account.

Register Managed Account

http://fabianwin2k3r2:33397/_admin/registeraccount.aspx?Source=http%3A%2F%2Ffabianwin2k3r2%3A33397%2F%5Fadmin%2FManagedAccounts%2Easpx

Once you have created all the Managed Accounts you will need for the associated services you will activate in your farm. You may elect to run the “Configuration Wizards” from the quick launch menu or manage individually.  I have included below the services that are available; you may check or uncheck the services as you need them in your farm and moreover you may select the accounts you previously configured above as you cherry pick which service want to start under that service account. See below…

After clicking next, you wait till all the new Databases are created. Which is a good point to show the databases were created BEFORE you configured you services (below)

 

 

and the databases that are now created after the services are started below…

So up to this point we have configured the Service Accounts, Instantiated Farm Services and in review of the new Databases created as a result. Next blog we will get into managing the New Web Applications that are now present as well as create our first MOSS 2010 Collaboration Site.

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

 

The Screen Shots below represents the installation steps to install the Base SharePoint 2010 up to the Central Admin. Part 2 of 2 will delve into configuring the Central Administration.

Screen clipping taken: 8/15/2009 5:41 AM

Screen clipping taken: 8/15/2009 5:41 AM

Screen clipping taken: 8/15/2009 5:43 AM

Screen clipping taken: 8/15/2009 5:43 AM

Screen clipping taken: 8/15/2009 5:44 AM

Screen clipping taken: 8/15/2009 5:44 AM

Screen clipping taken: 8/15/2009 5:45 AM

Screen clipping taken: 8/15/2009 5:45 AM

Screen clipping taken: 8/15/2009 5:48 AM

Screen clipping taken: 8/15/2009 5:51 AM

 

 

Screen clipping taken: 8/15/2009 5:52 AM

Screen clipping taken: 8/15/2009 5:53 AM

Screen clipping taken: 8/15/2009 5:53 AM

Screen clipping taken: 8/15/2009 5:54 AM

New below…

NOTE WELL: New step in the SharePoint Technology Wizard above!

Screen clipping taken: 8/15/2009 5:54 AM

Screen clipping taken: 8/15/2009 5:55 AM

Screen clipping taken: 8/15/2009 5:55 AM

NOTE WELL:  Now 13 steps rather than 9 steps in the SharePoint Products and Technology Wizard

Screen clipping taken: 8/15/2009 5:55 AM

Screen clipping taken: 8/15/2009 6:01 AM

Screen clipping taken: 8/15/2009 6:02 AM

Initial Farm Configuration Wizard

http://fabianwin2k3r2:13397/_admin/adminconfigintro.aspx?scenarioid=adminconfig&welcomestringid=farmconfigurationwizard_welcome

Screen clipping taken: 8/15/2009 6:04 AM