Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

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

clip_image001

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

clip_image002

Click “Create” for the new Content Type

clip_image003

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

clip_image004

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.

clip_image005

clip_image006

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

clip_image007

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…

clip_image008

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

clip_image009

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

image

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.

clip_image010

clip_image011

November 11, 2009 Posted by | SharePoint 2010, SharePoint Development, SharePoint How-To | , , , | 1 Comment

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!

November 10, 2009 Posted by | SharePoint 2010, SharePoint Development, SharePoint How-To | , , , | Leave a comment

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…

image

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…

 

clip_image001

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…

clip_image002

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…

 

clip_image003

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.

clip_image004

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.

clip_image035

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

clip_image005

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”

 

clip_image006

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.

clip_image007

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.

clip_image009

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

clip_image010

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.

clip_image011

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”

clip_image030

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 …

clip_image031

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

clip_image032

clip_image033

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.

clip_image018

clip_image019

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”

clip_image020

  • 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

clip_image021

clip_image022

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!

clip_image023

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

clip_image024

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

clip_image025

3. Click External List

clip_image026

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

clip_image027

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

clip_image028

Select your Entity Here… and Click OK

clip_image029

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

clip_image034

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

clip_image035

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”

clip_image036

Here is the Before below…

clip_image037

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.

clip_image038

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…

clip_image039

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

clip_image040

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.

clip_image041

Hope you enjoyed! More to come.. feedback welcomed

November 9, 2009 Posted by | Office 2010 TP, SharePoint 2010, SharePoint Development, SharePoint How-To, SQL Server | , , , , | 4 Comments

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…

clip_image001[4]

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

clip_image002[4]

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.

clip_image001

Previewing the New Site will yield.

clip_image002

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.

clip_image003

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…

clip_image004

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…

clip_image005

clip_image006

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

 

clip_image010

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.

clip_image011

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!

November 8, 2009 Posted by | SharePoint 2010, SharePoint Administration, SharePoint General, SharePoint How-To | , | 1 Comment

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”

clip_image001

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.

image

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.

 

clip_image004

Security

http://fabianwin2k3r2:33397/security.aspx

clip_image002

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.

clip_image003

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…

clip_image005

clip_image006

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)

image 

 

clip_image007

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

image

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.

November 8, 2009 Posted by | SharePoint 2010, SharePoint Administration, SharePoint General, SharePoint How-To | , | Leave a comment

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.

clip_image001

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

clip_image002

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

clip_image003

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

clip_image004

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

clip_image005

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

clip_image006

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

clip_image007

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

clip_image008

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

clip_image009

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

clip_image010

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

 

image

 

clip_image001[5]

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

clip_image002[5]

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

clip_image003[5]

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

clip_image004[5]

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

New below…

clip_image005[5]

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

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

clip_image006[5]

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

clip_image007[5]

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

clip_image008[5]

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

clip_image009[5]

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

clip_image010[6]

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

clip_image011

Initial Farm Configuration Wizard

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

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

November 7, 2009 Posted by | SharePoint 2010, SharePoint Administration, SharePoint General, SharePoint How-To | , | 2 Comments