Using the BDC in SharePoint

Once you’ve got the application definition file properly imported into your MOSS 2007 Shared Services Provider, using it within SharePoint is pretty easy. (again, this assumes you have some familiarity with SharePoint, know how to edit pages and use Web Parts! Apologies in advance if you don’t, but to keep the article to a reasonable length, I’ll assume that kind of knowledge). First I’ll go through a basic exercise of using two Business Data Web Parts to create a custom SharePoint page with a list of CRM Account records, and a related list of CRM Contact records. After that, I’ll show you how to create a custom “Action” so that a SharePoint user can select an Account and pop open a Google map window based on that Account record’s address information. I like this example since it’s actually a little easier to do that using this approach than it is using the pure-CRM approach where you need to write Jscript code for similar functionality.

Using the BDC Web Parts to Display Accounts and Related Contacts

If you work through this example you should be able to easily generalize this to other situations, but I’ll start with the “least common denominator” of Accounts and Contacts.

1. Create a new page within SharePoint. Make sure it’s a page layout that supports Web Parts!
2. On one of the Web Part zones, click Add a Web Part.
3. From the Add a Web Part dialog, scroll down until you see the Business Data Web Parts section, and select the Business Data List. You can actually add more than one web part at the same time, so to save a little of that here, I’ll add both the “List” and “Related List” Web Parts at the same time:

4. After you add them to your page you’ll see something like this:

 
5.  Click Open the tool pane to configure them, one at a time. The first thing you’ll need to do is identify which CRM entity you’re going to point each web part to – the first thing in the tool pane is the “Business Data Type Picker” you use for this. I usually browse out to see the entities that are exposed and you can see familiar CRM entities in the following figures as I go through this process:

6. First I’ll select Account, then Contact. Once I’ve got them selected and on the page, notice there’s an Edit view button available. Use this to deselect some of the fields (attributes) so that you don’t have to scroll all the way across the SharePoint page. I won’t go through all the details of that, but here’s what it looks like after I’ve deselected a lot of the fields, and customized the properties to only display ten records at a time:

7. Remember I said I wanted to select an Account record, and then be able to see all of the related Contact records. The web parts do most of the work for us, since they are defined as “Connected” web parts, and I can use the web part UI to pass a selected item from the Account List Web Part to the Contact Related List Web Part, as this figure shows:

8. Finally, what does it look like to a user? After locking those configurations in and taking our page out of edit mode, it might look something like this:

You can see I’ve got an Account record selected, and the three Contacts associated with that account are displayed in the Contact Related List web part. (I’d probably drop the ID columns before declaring this to be “in production”.) Notice the pull-down Edit menu in SharePoint, exposing commands like Create Account in MSCRM, and Edit Account in MSCRM. Those are so-called Actions. In the next section I’ll show you a cool and promising extension of the basic functionality: creating custom Actions. The View Map command you can see above is one of these and as I said…we’ll look at that next!

Creating Custom Actions

In the previous figure, everything except “View Map” is an out of the box Action pre-defined within the BDC application definition. One of the coolest things about the BDC is that you can define your own custom actions, and for actions that involve navigating to a URL and passing CRM data into the URL, you can do it entirely within the SharePoint UI, once again with no code required.

To illustrate this, I’ll go back to my Shared Services Provider, and navigate into the BDC application definition for my Account entity. I’ll navigate to the View Entity page for Account, and scrolling all the way to the bottom I’ll see something like this:

 
Look at that maps.google.com URL. If all you do is open a browser and navigate to google maps and enter an address in the Google search box, you get the same results as if you directly enter an address like this in the browser address box:
http://maps.google.com/?q=1415 west 22nd street oak brook il

This means that the google maps functionality is easy to construct a URL for, since it’s pretty forgiving, and doesn’t care much about spaces or quotes or things like that. So all you need to do to construct the custom Action so that users can do this without having to worry about typing anything is click the Add Action button, and specify the parameters you want to pass in to the URL, and how they correspond to Dynamics CRM fields:

The Dynamics CRM 4.0 Enterprise Search Accelerator allows you to integrate Dynamics CRM 4.0 into a SharePoint deployment, in two main ways: by extending SharePoint search to include Dynamics CRM as a searchable content source, and by allowing you to use a set of special web parts to expose lists of Dynamics CRM entities on SharePoint web pages. This is a big topic, so I’ll do a 3-part series of articles on it:

In this article (part 1), I’ll show you how to set up the CRM 4.0 Enterprise Search Accelerator.

In part 2, I’ll show you how to use the Business Data Web Parts to expose CRM data to SharePoint users, and how to create custom Actions for CRM records.

In part 3, I’ll show you how to configure SharePoint search to allow users to search CRM accounts, contacts and other entities directly from within SharePoint.

Background

The Business Data Catalog (BDC) is a very nice piece of functionality that ships as part of MOSS 2007. What it lets you do is to import an “application definition” into a MOSS Shared Services Provider. This application definition is in the form of a complicated XML file that points to a SQL data source and defines all of the entities it contains in a way that lets SharePoint understand them and expose them through search and web parts.

If you have some experience with SharePoint and the BDC you will find this an excellent way to surface highly formatted CRM 4.0 data through the more ad-hoc SharePoint UI. If you haven’t worked with SharePoint much it might take a little getting up to speed, but if you need the functionality it’s worth it!

The BDC has been a great tool since it was first released, and once you’ve got the application definition (XML) file defined properly and imported into SharePoint, it’s a nice no-code way for a power-user to integrate SQL and “line of business” data into the SharePoint experience. However…that “once you’ve got the application definition (XML) file defined properly and imported into SharePoint” is a big caveat! Creating those things is well beyond the skills of most of the power-users who end up using the BDC in SharePoint, and since there really weren’t any application definitions or tools to create them until fairly recently, the BDC hasn’t gotten as much use as it might have.

What does this have to do with the Enterprise Search Accelerator? Well, everything, since all it is is a reference BDC application definition file that you can import into SharePoint and expose standard out of the box Dynamics CRM 4.0 entities! Since many organizations get a lot of mileage out of the standard out of the box CRM entities, to have a BDC definition that creates all of those entities in SharePoint and allows you to expose them to your SharePoint users is a huge time saver!

The Search Accelerator was released in January 2009, and is one of the eight currently available Accelerators, all of which you can see here: http://crmaccelerators.codeplex.com/

Setting up the Enterprise Search Accelerator

There’s a little bit of setup involved, but it’s not too difficult. It definitely helps if you’ve had at least a little experience working with SharePoint search. Logically, I break this section out into two main sub-sections. First, you need to import and configure the so-called “Application Definition”; this is what allows SharePoint to understand and expose your CRM database. Then, if you want SharePoint users to be able to search CRM data the same way they would search SharePoint content, you need to configure SharePoint search. In order to keep this article reasonably short, I’ll only discuss the first of those two setup requirements here, and I’ll save Search configuration for a separate article. 

The fact that this accelerator is referred to as “Enterprise Search” confuses the point a little, at least for me. What it really does is to give you an application definition file so you can access a Dynamics CRM organization using the BDC functionality. So all of the search functionality is a come-along with the BDC, and the search functionality is optional at that.  The documentation that comes with the download is very good and quite complete, but it’s almost too complete, in the sense that for a lot of people many of the steps will be unnecessary. For example, on the VPC I installed it on, I only had to perform the 5^th step of the 5-step setup process, since my VPC already had all the SharePoint-specific stuff done. The instructions are written for the out of the box public VPC image I referred to above, so just keep in mind that for your SharePoint environment, it may be quite different (and possibly a lot quicker!)

Importing and Configuring the Application Definition

As I mentioned above, creating these application definitions from scratch is tricky at best. For example, the application definition file included in the Enterprise Search Accelerator consists of 15,000+ lines of dense XML code!  So I was glad to have the heavy lifting already done for me.

There were really only two changes I needed to make to the xml file that defines the BDC for the Enterprise Search Accelerator (file name: MSA_MSCRM_BDC_V1.0.xml, included in the “source code” folder)

Updating the SQL Database the BDC Points to

First, I had to change the name of the SQL database within the CRM organization that it points to. The file that ships with the accelerator is set up for the public VPC for Dynamics CRM (you can download it from here:  http://www.microsoft.com/downloads/details.aspx?FamilyID=DD939ED9-87A5-4C13-B212-A922CC02B469&displaylang=en ). I was using a different VPC image with different CRM organizations, so I had to make one change. Towards the top of the file, there’s a line that specifies the “RdbConnection Initial Catalog”, and I needed to change the value to “AdventureWorksCycle_MSCRM”. This is the name of the SQL Server database for the organization I wanted to point to. As far as I could tell this wasn’t documented anywhere in the Accelerator, so I guessed and got lucky. Here’s what that part of the file looks like:

Updating URLs for Navigating to Dynamics CRM Records

The only other change I needed to make was to fix the CRM URLs within the BDC file. As you will see in Part 2, the BDC lets you define “Actions” that can be performed by a user when (for example) selecting a CRM record exposed through SharePoint. Many of these Actions use the “URL-Addressable Forms” capability of CRM. Since these are defined within the BDC application definition file for a specific CRM deployment, you will generally need to update these URLs.

You can import the BDC successfully without doing this, but when you try to navigate to any of the Dynamics CRM records exposed through SharePoint, you’ll get a 404 URL not found error, essentially. You can see the change I made in the next figure. This actually is easy, since you can do a global find and replace. In my scenario I needed to replace the CRM URLs on the VPC the file was written for – http://moss:5555/microsoftcrm — with the ones on my VPC – http://crm. There were about 25 references to that URL, and after I changed them all, I was good to go. Here’s an example of making these changes:

 
Importing the Application Definition

Once the XML file is all configured, importing it is easy:
1. Navigate to the Central Administration web site for your SharePoint (remember: MOSS 2007!) server.
2. Click underneath “Shared Services Administration”, on the Shared Service Provider for your SharePoint server.
3. In the Business Data Catalog section, click “Import application definition”. You will see something like the following figure:

 
4. Browse out to the XML file that contains the definition of the BDC (MSA_MSCRM_BDC_V1.0.xml), and then click “Import”. After you do this it will take a while and you’ll see all the lines of the file getting counted through as the import whirs along.
5. After it’s done, you will have a sweet new BDC application imported; with any luck you’ll see something like the following:

 
6. You can click on the name of the application to see all of the entities it has now exposed through SharePoint. If you’re familiar with Dynamics CRM you’ll be happy to see something like this:

 
7. You can imagine that if you click on Account or Contact from this page, you will see all of the attributes that the BDC has defined for that entity. But at this point, we’re ready to expose our CRM data to our SharePoint users, which is the topic for Part 2 of this three-part series.

If you tried to attend our last meeting and got mad at me when I had to cancel at the last minute, I’ll offer up another apology here! But our next meeting — April 30 at 6:00 PM CDT — is virtually guaranteed to happen, since we’re holding it at the Microsoft Downers Grove office, and they’ve got great connectivity. Plus, lots of room for in-person attendance if you happen to be in the neighborhood.

The keynote topic — the Dynamics CRM 4.0 Accelerators — is one that a lot of our members will be interested in, and I hope you can join us. As always, attendance is free, either in-person or online. For more details and to register, please visit the brand-new home of the Dynamics CRM User Group: www.DynamicsCRMUserGroup.com  

I hop you can join us on April 30!