Clayton's SharePoint Madness

All About SharePoint, InfoPath, and SharePoint Designer!

Archive for the ‘SharePoint 2010’ Category

Things related specifically to the brand new Microsoft SharePoint Server 2010

InfoPath 2010 – Designing External List Forms

Posted by Clayton Cobb on October 28, 2009


This blog entry is a direct follow-up to Business Connectivity Services (BCS) – Creating an External List from an ECT. Through this blog and the rest in the series, we will be interacting with the External List that I created based on the External Content Type from the first blog: Business Connectivity Services (BCS) – Creating an External Content Type (ECT) based on a SQL 2008 Table.

I enjoyed learning about the new BCS features and how to quickly create both an ECT and External List. However, the area I’m MOST excited about is InfoPath 2010 and all of its many improvements. One major – and I mean MAJOR – change is the ability to now modify SharePoint 2010 (SP2010) list forms with InfoPath. Being that I love to use InfoPath forms for conditional formatting purposes so that my users only see what they’re supposed to see when they see it, this is a massive improvement for me. I have not been able to do that with my lists, so it has always been a struggle. To do anything remotely close to this, you had to use SharePoint Designer (SPD) 2007 to modify your ASPX pages and their web parts. This was very cumbersome, ineffective, and even had issues like the well-known Attachments Bug (fixed in SP2, I believe, but annoying for years). Using SPD 2007, you also didn’t have the full fidelity of a forms builder like you do with InfoPath. Well, that has all changed now, and although I wish I had have had time to do a regular list form for you, we are going to focus on modifying the list forms of an External List. I was very curious as to whether or not this would be doable, because I had proven the concept with normal lists in Tech Beta build, but I couldn’t get ECTs to work. So, when I got one day to work on a newer build, I focused 100% on ECTs and anything I could associate with it. I do have a customized list form in my personal SP2010 build from which I will get screenshots and do a separate blog later. For now, we’re taking the InfoPath list form concept and combining it with the awesome External List feature.

Here are the tools used in this blog entry:

  • SharePoint Server 2010
  • InfoPath 2010 Beta
  • SharePoint Designer 2010 Beta
  • SQL Server 2008
  • BCS – External Content Type
  • BCS – External List

Here are the steps of this blog entry:

  • View existing data in SQL Server 2008
  • View existing SQL data in an SP2010 External List
  • Choose to customize list forms with InfoPath from within SPD 2010
  • Customize list form in InfoPath 2010
  • Create new list item
  • View resulting data in the SP2010 External List
  • View resulting data in SQL Server 2008

View Existing Data in SQL Server 2008

First off, we just take a quick jaunt over to SQL Server Management Studio (SSMS) to take a look at the Biz table in our ECT database, because that is the table feeding our External List. As you can see, it has 10 records (Fig 1).

Fig 1 – Existing data in SQL 2008

View existing SQL data in an SP2010 External List

Next, we mosey on over to SP2010 and browse to our External List that looks just like a normal SharePoint list except that we see the exact same data here as we did in our SQL table.

Fig 2 – Existing SQL data in an SP2010 External List

Choose to customize list forms with InfoPath from within SPD 2010

Unfortunately, in my haste to get screenshots before the machine got shutdown, I forgot to take a screenshot of this button in SPD 2010. This part is actually very important, because the behavior is different than a regular list form. With a regular list, you just browse to the list in SharePoint, click on the List tab in the ribbon (like you see in Fig 2), and you click the InfoPath icon in order to immediately customize your list forms. However, in an External List, it doesn’t work like that. In my early SP2010 build, the icon was there, but when it opened InfoPath, I couldn’t do anything. What I learned at the conference is that you have to go open the External List in SPD 2010, go to the Design tab in the ribbon, and choose “Customize List Forms in InfoPath” or something of that nature. This is the screenshot I need to get for you and add to this blog entry later. For now, though, if you get a chance to try it, you will be able to follow those instructions easily.

Once you click that button, it immediately invokes InfoPath 2010 and shows you the whole data structure for the list along with a pre-defined Main Submit data connection so that you don’t have to configure that. Your customization options within an External List form are much more limited than in a regular list form. I don’t yet have a list of what those are, but I will catalog them and add them here:

  • External list forms do not support additional data connections – I learned this one right away, because it’s one of the first things I tried. In my regular list form, I was able to do this but not on the External List form. I verified with the InfoPath product team that this is a definite limitation that will not go away. Bummer, because I love to add the UserProfileService as a data connection to 99% of my forms for user validation. My earlier blog posts explain one very good reason for this: InfoPath – User Roles in Browser-Enabled Forms Using AD Groups. To be more clear, the bolded text in this bullet is the exact error I got when trying to add a data connection.
  • Cannot change the schema – This is not an exact error, but with regular list forms, you can add a field in design time, and it will add that as a column to the list. With an External List, the schema is set and can’t be changed, so you can’t add nodes to the schema in InfoPath. I will get more verification and improved wording on this.
  • More to come…

Customize list form in InfoPath 2010

So, again, I don’t have any snaps of my form during design time, but you will see it at run time below. In this scenario, I didn’t do anything fancy at all, because I just wanted to prove the concept. However, fancy or not, one thing I could not figure out was how to change all three forms – new, edit, and display. With regular list forms, all three can be selected and changed separately. With an External List, there is just one form that gets used for each instance. No, you can’t edit the display form, but it’s the same design. The main issue I had was that the Edit form was the same as the New form, and I love to change those up as well as my Display form. All I could find in SPD 2010 were threw new PAGES that got created after I chose to customize my list forms with InfoPath. The three new pages were named newifs.aspx, editifs.aspx, and dispifs.aspx if my memory is correct. When I went to edit those directly in SPD 2010, I didn’t ever get prompted with InfoPath. What I didn’t realize was that these new pages were using the brand new InfoPath Form Web Part, which is another awesome addition. Each of those pages had a single IP Form Web Part, and in the settings of that web part, you can choose which view to display as well as which action to perform upon submit. I didn’t look closely enough to notice this, but the InfoPath team explained it to me.

So, here is how you would go about setting up three separate “forms” for an External List:

  • It’s just one form
  • Create 2 new views (3 total) and design them according to their purpose (new, edit, display)
  • Go to the editifs.aspx and dispifs.aspx pages in SPD 2010, edit them, modify the form web part, and set their view to the corresponding view of that page (editifs.aspx = Edit view).
  • Also, a trick for the display view is to set your display view as the PRINT VIEW in the properties of the default view. This will apparently cause that particular view to always show up when a display page is opened

That’s pretty simple to me.

Create new list item

After publishing your form, browse to the External List again and simply click New Item (Fig 3).

Fig 3 – Click New Item in External List

As I mentioned before, this particular form has no bells and whistles, but I did make a few changes (Fig 4). I changed the Partner field, which is not a Boolean data type, to a checkbox. In the properties of that control, I set the checked and cleared values to match the exact syntax of what is in SQL so that there would be no conflicts. I also disabled the toolbars that are built-in and added a custom Submit button instead. I like to do this, because it allows me to put conditional formatting on my buttons to only show certain buttons to certain people at certain times and to do multiple sets of rules on each one. With the built-in submit button, you can’t do that.

As you can see, I added some info for a new business, and then I clicked Submit.

Fig 4 – InfoPath-based External List form

View resulting data in the SP2010 External List

After submitting, the form returned me to the External List where I immediately could see the new business along with all of its associated data (Fig 5).

Fig 5 – Resulting data in External List

View resulting data in SQL Server 2008

A quick job back over to SSMS (Fig 6) reveals that the data was in fact written directly to SQL Server 2008 without a hitch…

Fig 6 – Resulting data in SQL 2008

Some points to consider:

  • This is just a simple, flat table structure, not a complex data structure with depth and relationships. If this is not quite complex enough for what you need to do, then please consider the DBXL tool created by Qdabra
  • I have no middle layer between SharePoint and SQL, which is not a best practice. It would be best to use a web service
  • I used the permissions of my administrator account to create the ECT and was using that same account when interacting with the form. You have the ability to connection permissions at the point you create the ECT and in the list itself
  • Not being able to add additional data connections limits the options you have for doing advanced customization and logic
  • Despite all of this, it is still awesome!

Next in line for this series:

  • InfoPath 2010 – Connecting a Form Library template to an External List

Posted in BCS, InfoPath 2010, Office 2010, SharePoint 2010, SPD 2010 | 51 Comments »

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 »

SharePoint 2010 Update 1 – Infrastructure Setup: Single Server Farm

Posted by Clayton Cobb on August 17, 2009


Here are my puzzle pieces:

  • ASUS G2S-A1 Laptop with 4GB RAM and dual 2.4 Ghz processors (Fig 1).
  • Windows Server 2008 R2 Enterprise RTM (Fig 1)

Fig 1 – System Info

  • SQL Server 2008 Standard SP1 (Fig 2)

Fig 2 – SQL 2008

  • Office 2010 64-bit (entire suite – Fig 3)

Fig 3 – Office 2010

  • SharePoint Server 2010 (Fig 4)

Fig 4 – SharePoint 2010

Fig 5 – MailEnable

  • Client Computers
    • Windows 7 Enterprise 64-Bit RTM desktop with IE8 and Office 2010
    • Windows Vista Home Edition 32-bit laptop with IE7, Firefox 3.0, and Office 2007

ASUS G2S-A1 Laptop with 4GB RAM and dual 2.4 Ghz processors (Fig 1):

  • The entire single server farm is built on one physical laptop with no virtualization
  • Originally, with only 3GB RAM (due to previous 32-bit OS), my setup was a dog.  It was getting killed with RAM usage sometimes maxing out
  • Thanks to 64-bit, I was able to bump to 4GB and fully-utilize it.  This made a huge difference as my peak RAM to date has been 3.1 GB and only when SQL had ballooned a bit.  Even then, performance was good.  Most of the time, even with 4-5 W3WP IIS worker processes running, I’m only eating up 2.5-2.8GB RAM.  This seems to leave plenty of room for the operations I’m performing, including viewing browser-enabled forms and running workflows.
  • My dual processors are constantly sitting in the 40-60% utilizatin range.  Performance Monitor shows me that the bulk of it is being used by the Timer Service (OWSTIMER), but remember that I’m running all of the server and client software you see in this blog post plus multiple browsers and Outlook 2010 at all times.
  • Interestingly, at this very moment, I’m only seeing <15% CPU but 3.1 GB RAM usage.  That’s with everything running and open that I have mentioned and while just running a workflow that sent an email to Outlook 2010.
  • Overall, I can see why the recommendations for RAM are much higher than in 2007.  The WFEs will be more powerful but will require more power to sustain…
  • I definitely plan to go to 6 or 8 GB, but my laptop can only handle 4, so my next build will be on an actual server. In fact, if things work out, I’ll do a server with 16GB RAM and then use Hyper-V to create a multi-server farm, although that is not technicaly supported in this version of Beta.

Windows Server 2008 R2 Enterprise RTM (W2K8R2)

After my 3rd time building my laptop into a complete, single server SharePoint farm, I finally feel comfortable with how W2K8R2 works in terms of roles and features. I’m not a sys admin by trade, so this is not my sweet spot, but I do feel pretty good after install #3. After playing with various roles/features and trying both a Workgroup with Local Users/Groups and a Domain with Directory Services, I think I have a decent setup to share with others. I realize there are many ways to skin this cat, but these are my experiences.

Roles and Role Services (Fig 6):

  • Active Directory Domain Services
    • Active Directory Domain Controller
  • Application Server – .NET Framework 3.5.1
    • All Role Services running
  • DNS
  • File Services
    • File Server
    • Distributed File System (Optional)
      • DFS Namespaces
      • DFS Replication
  • Web Server (IIS)
    • All Role Services (there are a lot)

Fig 6 – W2K8R2 Roles

Features (Fig 7):

Most features get installed automatically as pre-requisites for the roles above or during installation of SharePoint 2010. This is a great thing about the new products – things don’t just fail because you forgot to activate a random feature:

  • Desktop Experience (absolutely need this one for the cool Aero effects, hah – Fig 8)
    • This also enables many desktop features that are turned off by default on W2K8R2. You may not do this in a production environment, but for my single server testing, I want everything at my disposal
    • For the Aero themes to work, you must download and update your graphics drivers
    • After enabling this feature, you still must start the Themes service for desktop themes to work (not SharePoint themes)
  • Group Policy Management
  • Ink and Handwriting Services
  • Message Queuing
  • Remote Server Administration Tools
  • SMTP Server
  • Telnet Client
  • Telnet Server
  • Windows Process Activation Service
  • .NET Framework 3.5.1 Features
  • Wireless LAN Service (required if you want to use your wireless card)
  • XPS Viewer

Fig 7 – W2K8R2 Features

Fig 8 – W2K8R2 Aero Theme

SQL Server 2008 Standard SP1 (SQL08)

  • Upon starting the setup wizard, you are immediately notified that SP1 must be installed for SQL to work on this version of Windows. I followed suit.
  • Install ran straight through without issue and without
  • Didn’t bother with collation settings, but I normally choose Latin1_General_CI_AS_KS_WS. Collation settings are no longer part of the normal installation wizard.
  • All of my checks passed at each stage
  • Used a domain account specifically for SQL as the service account for all SQL services
  • Used a domain account as the SharePoint farm account and setup account in one, which required setting dbcreator and securityadmin rights on the DB instance (Fig 10)
  • I modified the Protocols in the Configuration Manager to enable Named Pipes (Fig 11)


Fig 9 – SQL 2008 Management Studio

Fig 10 – SQL 2008 permissions for SharePoint Farm Account

Fig 11 – SQL 2008 Configuration Manager

Office 2010 64-bit

  • Very simple install of Office Professional Plus 2010 64-bit plus Visio 2010, SharePoint Designer 2010, and SharePoint Workspace 2010
  • InfoPath and SharePoint Designer are my bread and butter. I also leverage Outlook heavily for workflow email routing.
  • A new tool to the mix is Visio, which can now allow business analyst to construct process diagrams into workflow templates that then get imported to SharePoint Designer. I’ll be showing that in a later update
  • Although InfoPath has two separate products listed, it is still just one license and no chance of providing the Editor as a free download like Adobe PDF – bummer.

SharePoint Server 2010 (SP2010)

Wow, very easy install. The install wizard has a Preparation Tool that checks all your pre-requisites and installs them as necessary (Fig 12&13).

Fig 12 – SP2010 Installation Wizard Home Screen


Fig 13 – Preparation Tool Wizard

  • Performed a complete install
  • Ran the Products and Technologies wizard where I specified my DB instance and used sp-farm from above as the Database Access Account.
  • Completed without error, and then Central Admin fired right up!!

MailEnable Standard

As of Windows Server 2008, Microsoft is no longer supporting POP3 as that has shifted completely to the Exchange team. So, we no longer have the ability to create mailboxes and such. You can still enable SMTP for relaying, but I need the ability to create mailboxes for my isolated domain users so that I could start doing workflows. After reading and learning why I couldn’t do this natively with W2K8R2, I found MailEnable, which is free for what I needed to do. This allowed me to create mailboxes for all of my users, assign email addresses, and associate those email addresses with each user’s Active Directory profile. MailEnable actually made it very easy by allowing me to import all of my AD users while simultaneously creating email addresses on the fly – simple and quick. Due to this, I was able to configure Outlook to use those mail accounts via POP3 and even got the address book to sync with AD for auto-resolution of names. This is now allowing me to do the end-to-end solution testing with SP2010, InfoPath 2010, SharePoint Designer 2010, Visio 2010, and Outlook 2010. Times are good… =)

**Here in Denver, my house is being pelted by a torrential hailstorm in the middle of August…mmmmk. Now I know why my insurance company just raised the hail deductible a few weeks ago…

**Blog created in and published by Word 2010 with help from OneNote 2010

Posted in Office 2010, SharePoint 2010, Windows Server 2008 R2 | Tagged: , , , , , , , , , , , , , , | 25 Comments »