Clayton's SharePoint Madness

All About SharePoint, InfoPath, and SharePoint Designer!

Archive for the ‘SPD 2010’ Category

Things related specifically to SharePoint Designer 2010

Business Connectivity Services (BCS) – Creating an External List from an ECT

Posted by Clayton Cobb on October 26, 2009

In the previous blog post – Business Connectivity Services (BCS) – Creating an External Content Type (ECT) based on a SQL 2008 Table – we created the ECT. Now, we are going to utilize the ECT within the new feature of SharePoint 2010 known as an External List. After we’ve created and viewed our External List, we will begin interacting with the data using InfoPath forms in the next blog post. As a reminder, at the 2009 SharePoint Conference in Las Vegas, Microsoft announced this as #2 on the Top 10 SharePoint 2010 features.  Also, you can keep track of BCS info on the BCS Team Blog.

External Lists are a new feature in SharePoint 2010 that allows us to interact with external data as if it were a normal SharePoint list. As you will see later in this blog post, the External List looks and behaves just like any other SharePoint list on the surface, but beneath the scenes, there is a lot going on. In this blog entry, we will use these tools:

  • SharePoint Server 2010 – Team Site
  • SharePoint Designer 2010
  • Business Connectivity Services (BCS) – External Content Types (ECT) and External Lists

Create External List

Open SharePoint Designer (SPD) 2010 and connect to the same site where you created the ECT. Click on the Lists and Libraries Site Object in the left navigation pane. Upon doing so, you will see a button in the ribbon labeled “External List.”

Fig 1 – Where to Create an External List in SPD 2010

Select External Content Type

After clicking the External List button, you will be prompted to pick from a list of existing ECTs. In the previous blog, we created BizList, so that is what we will select (Fig 2).

Fig 2 – Selecting the ECT

Verify Settings

After SPD 2010 does its work, you will be taken to a new page that represents your External List (Fig 3). You can set the name, description, permissions, and general settings. Note the web address of the list, the list ID, and the fact that it is External. I will delve more into the permissions settings later when I get the chance.

Fig 3 – Successfully Created External List

View Results in Browser

Now that the External List exists, go browse to your site and view the data. You will see that the list name is displayed in the Quick Launch, and then when you click on the link, you will see a very normal list that is displaying all the data in SQL Server (Fig 4). For now, ignore the colors and formatting, because that is related to another blog post coming shortly after this. Upon first glance with no customization, the view would be plain with no formatting. The beauty is that in just a few minutes, you can not only create the connection to the external DB, but you can then display it in a very normal SharePoint list that has a direct tie to the DB, not just a copy of the data.

Fig 4 – Viewing External SQL Data in an External List

One thing that is NOT exactly like a SharePoint list is that you cannot add workflows to the list, because SharePoint doesn’t know how to trigger the workflows. The data resides and changes in SQL Server, so SharePoint is unable to know when to fire a workflow. There may be ways with code to do this or with shadow lists, but workflows cannot be added out of the box. However, workflows on other lists and libraries can be made to interact with an External List through reference.

In the next blog entry, I will reveal another huge improvement with SharePoint 2010, and that is the ability to modify list forms using InfoPath. Not only can you do it with regular list forms, but I will show how to do it with External List forms, which was a big deal to me. I will also do a quick side blog entry showing how SPD 2010 can be used to easily customize views for lists now – another big one for me.

Posted in BCS, SharePoint 2010, SPD 2010 | 2 Comments »

Business Connectivity Services (BCS) – Creating an External Content Type (ECT) based on a SQL 2008 Table

Posted by Clayton Cobb on October 25, 2009

Let the SharePoint 2010 Madness begin! My first post-NDA blog entry will be about the new Business Connectivity Services (BCS), which replaces the Business Data Catalog (BDC). The purpose of the BCS (and formerly BDC) is to expose business data from non-SharePoint data sources within your SharePoint 2010 environment. Sure, we could do that with the BDC, but it required a very complicated application definition file (ADF) that made it very difficult to successfully create a connection. Due to this complexity and the fact that I’m not a developer at all, I never used the BDC myself without developer help. Also, we could only read from those data sources. At the 2009 SharePoint Conference in Las Vegas, Microsoft announced this as #2 on the Top 10 SharePoint 2010 features.  Please bear in mind that the BCS is a huge topic that can go very deep.  Todd Baginski (MVP) presented a deep dive on the BCS that goes far beyond what I’m talking about, so I will link to that presentation when it becomes available. Also, you can keep track of BCS info on the BCS Team Blog.

Well, things have changed drastically now. Not only can we build BCS connections in SharePoint Designer with a built-in GUI, we can also perform write actions back to the data source. This blog will be fairly simple, but that is by design, because I want to show that a non-developer can easily create a BCS connection and start interacting with external data within minutes. In this scenario, this is what we’ll be using:

  • SQL 2008 Database with 1 Table
  • SharePoint Designer 2010 connected to a SharePoint 2010 Team Site

Build (or locate) your SQL Server database

For this scenario, I built a very simple database with a single table containing 5 columns (Fig 1). Basically, I’ve created a Business Contacts DB for storing the business name, city, state, value, and partner status. After creating the DB, I added 9 rows from within the SQL Server Management Studio (SSMS) interface (Fig 2). For later use, remember that the DB Server is named “MOSS,” and the DB is named “ECT.”

Fig 1 – SQL 2008 Database with a simple table

Fig 1 – SQL 2008 Database with business contact data

Create External Content Type in SharePoint Designer 2010

After creating the DB and noting the DB/Server name, it’s time to open up SharePoint Designer (SPD) 2010. SPD 2010 can only be used with SharePoint 2010. It cannot be used with MOSS 2007, SPD 2007 can’t be used with SharePoint 2010, and SPD 2010 cannot be used for editing other non-SharePoint websites. However, you can have both SPD 2010 64-bit and SPD 2007 (32-bit only) installed on the same machine (I have them both on my Win7 64-bit machine). It is now a dedicated design tool for SharePoint use. So, to use it, you have to have a SharePoint 2010 site to connect to, otherwise there are no available functions. Once you connect to your site where the External Content Type (ECT) should reside, click on the Site Object named “External Content Types” in the left navigation pane. After that, you should see a button on the top left of the ribbon labeled “New External Content Type.” Click that, and you will get to a page where you can create your new content type (Fig 3).

Fig 3 – New External Content Type view in SPD 2010

Name the External Content Type

First, you’ll want to name the ECT (Fig 4). The display name is what will show up in the External Content Types Site Object in the SPD 2010 left navigation pane.

Also, I forgot to show it on this screenshot, but the Office Item Type is important. You can leave it as generic, but if you choose the Contact type, then it will be treated like an Outlook contact, which means that if you later connect to this data from Outlook, all the data will show up in nifty Contact cards. I did choose that option when making my real ECT but forgot to show it on this screenshot.

Fig 4 – ECT named BizList

Discover Your External Data Source

Now, you’ll want to click the link next to External System that says, “Click here to discover external data sources and define operations.” This will take you to a new page where you click the Add Connection button, which causes a pop-up prompt to appear for selecting your data source type. The available options are .NET Type, SQL Server, and WCF Service (Fig 5). For this example, I chose SQL Server.

Fig 5 – External Data Source Type Selection

After choosing the SQL Server data source type, you get another prompt. This is where you define the connection to your database (Fig 6):

  • Database Server: MOSS (noted above when showing the screenshot of SSMS)
  • Database Name: ECT (same as above)
  • Name (optional): What I learned with this field is that if you make the same ECT on another site collection, then you get a warning when trying to recreate an ECT in a different site collection with the same name. I had left this field blank the first time I tried it, so when I tried it again in the same farm with a blank name, I got the error. That’s when I decided to give it a name – the name of the site where I was using it. This is important to remember for the future
  • Identity: I chose to connect with MY user identity – the user creating the ECT – but I of course have access to the SQL DB. If I didn’t, then I wouldn’t be able to make this. The 3rd option is the one that enables the Secure Store Application ID field at the bottom. This is the new Single Sign-On for those of you who used that in MOSS 2007.

Once you put in the correct information and click Ok, SPD will connect to the DB and return info about it. You will see the DB icon with the name you gave in the Name field above. You will also see folders that represent tables, views, and other items. You should see your relevant table within the Tables folder.

Fig 6 – Defining the connection to your SQL Server DB

Define Your Operations

This step is where the magic happens. After finding the table you want to use, right-click on it, and you will see some operations to choose (Fig 7). This part is beautiful, because it does it all for you without any code. In this case, I chose to Create All Operations. This options creates the well-known CRUD operations – Create, Read (Item), Update, and Delete – but it also creates an enumerate operation (Read List), so I like to call it CRUDE. 😛

Fig 7 – Creating your CRUD operations

Again, SPD 2010 will do its thing, and when it finishes, you will see a report of the operations that were successfully created (Fig 8).

Fig 8 – A successfully created External Content Type with full CRUD operations

This leads us straight into the next blog, which will discuss what you do with an ECT once it’s created:

Posted in BCS, SharePoint 2010, SPD 2010 | Leave a Comment »

InfoPath 2010 Rules!

Posted by Clayton Cobb on July 16, 2009

I am EXTREMELY excited by some of the new features.  The Infopath Team just announced some new features on their blog as you can see here:

What’s New in InfoPath 2010?

I do 95% of my InfoPath work with SharePoint, so I am big-time stoked about the new SP integration features.  Look at some of these things that we will be able to do that plagued us previously:

  • Modify list and library forms (new, edit, display) from IP instead of having to use the cumbersome SPD interface!! I’ve done this already for both regular list forms AND for InfoPath 2010 – Designing External List Forms
  • Rules manager for copying and pasting rules.  You can actually re-use your rules, and you can use the same conditions for a data validation, conditional formatting, or rule all on one field at the same time (not separate buttons to push anymore). You can also copy/paste entire rule sets at once. A bonus point for everyone here is that you can use the InfoPath 2010 Designer to design your 2003 and 2007 forms!!  I’m doing this exclusively now, because the interface improvements make it much faster to build forms.
  • We will now be able to use these previously-missing features in our browser forms: FILTERING!!!!, Mult-select list box, combo box, choice groups/sections.  Sweet!!  I’ve already built a browser form with cascading dropdowns (filtering) and will have a blost entry posted on the topic soon.
  • The contact Selector is now included by default in the Controls gallery (nice!).  This is nice, but be sure NOT to use it if you’re developing a 2003 or 2007 form.  The new Contact Selector does not use ActiveX, and the one in 2007 does.  If you add the 2010 Contact Selector to your 2007 canvas, it will not work.
  • There is now an InfoPath form web part so we don’t have to use page viewers nor the XMLFormView add-on that required some customization.  It also can be connected to other web parts – awesome!  This feature was a part of almost every demo/session done by the InfoPath product team at SharePoint Conference 2009.  It will be a major player in how we use InfoPath to visually display data right within web part pages along with several other connected web parts.  Also, I have learned that when you modify the list forms of a list/library, the new/edit/display pages all get auto-generated, and they each include an InfoPath form web part.  That’s how it works!

I’ve already started testing IP10, and it is freakin BAD!!!  The good “bad,” of course.  Here are some observations so far:

  • With my existing IP07 forms, the experience is seamless.  There is no upgrading that must be performed, and IP10 immediately knows to keep my IP07 browser-compatible form from getting messed up with unsupported functions.  It also auto-defaults to being saved as an IP07 form.  Everything works the same, yet I get the ease of using the new interface
  • Learning a lot so far, and I love it. It’s difficult finding everything, but I know that once I get it memorized, the ribbon will make it way better than before. It’s similar to when I first moved to Office 2007 and struggled with Word/Excel, but now I won’t even touch 2003.
  • The install gives you both a designer and editor. My presumption is that the editor may behave like Adobe Reader? I hope that it can become a free download for anyone so that only form designers/developers need the full version (licensed) while regular users would get just the Editor. If MS were to do it this way, it would have seriously positive ramifications on clients, because I am _always_ having to explain how either everyone needs IP07, or we have to do limited browser forms. I foresee great strides in the market if Editor is made free.  The IP team has not confirmed nor denied my commentary, but for now it doesn’t appear to be what I was hoping.
  • One-click publish is nice!! However, you still have to find the regular publish so that you can promote properties and such. It took me a while, but I found it, and that led me to being able to configure my Quick Access buttons. Once my publish is setup nicely, it’s very convenient to be able to just do the one-click publish for constant editing for a form template.
  • I have all my favorite buttons in the quick access at the top (save, publish, preview, rules, form load, design mode).  Putting them there makes everything very fast and easy as you get used to the interface.
  • Speaking of Form Load, it was a pain to find, since it’s no longer in the Advanced Form Options under the Open and Save tab. That menu is changed to just Save, and there is a Form Load button in the Data ribbon. I moved that to my quick access toolbar…
  • The rules management is a huge breath of fresh air. Not only can you copy and paste rules, but you can set conditions in one place to then be used by data validation, conditional formatting, and actions all in one place instead of having to always re-do conditions for each of those separately
  • In terms of Editor, I noticed a toggle option in view properties saying, “Design view for the InfoPath Editor only.” Hmmm, must find out what that’s all about.  I found out that this is just the same option that was in IP07, but it has been re-labeled to something more obscure.  Basically, if you click this box, it means that view will be allowed to have browser INCOMPATIBLE features and will thus not show up in a browser form.  Handy if you want a form to be dual-use, but not what I was hoping it meant.


  • I get this message very time I open IP Designer, “Some of the custom controls cannot be used: You can continue working without these custom controls, or you can modify them. Details: The following ID for the custom control is already being used by another control: {61e40d31-993d-4777-8fa0-9ca59b6d0bb}. All controls must have a unique ID.” I found out from the IP team that this is a known bug that wasn’t fixed before Technical Preview, but that means it should be taken care of before the release.

So, I’ve already created a client form (prototype) using IP 2010 as of tonight. It was previously a 2007 form, but 2010 preserved everything about it and automatically saved it as a 2007 template when I first saved. I published it to MOSS Enterprise, and it renders in the browser as expected.

Posted in InfoPath 2010, Office 2010, SPD 2010 | Tagged: , , , | 74 Comments »