In part 1 of this 2-part series, I demonstrated how to use the CRM 4.0 Report Writer to create charts that illustrate how your data are distributed across categories. It’s relatively straightforward when you’ve got numeric data that can be “summarized”, but a little less obvious when you don’t.

For example, what if you want a count rather than a sum? Here are some of the more frequent examples I see of this requirement:

• Contacts by sales rep
• Cases by case type or category
• Accounts by industry or territory

The built-in Report Writer doesn’t have a “count” function, so you need something you can sum up. I do this with a custom attribute. Here’s a heuristic step-by-step approach (using the Account by Territory example), that I demonstrate in the video below:

1. Create a custom attribute on Account, call it “counter”.
2. Give it a type of “int”, max and min values of 1.
3. Create an automatic workflow on the create trigger to set the value to 1 for new account records going forward.
4. Figure out the best way to populate that value into already existing records (I use a workflow in the video)
5. Then create the report, using the same steps I showed in part 1. This figure shows the most important step of the Report Wizard, the “Lay Out Fields” dialog:

Here’s the video. Enjoy!

Dynamics CRM does not have a built-in “calculated” field type, but you can use workflows and the Multiply by and Set to operators to get something close to it. For example, the Opportunity entity has built in attributes “Est. Revenue” (attribute name “estimatedvalue”) and “Probability” (attribute name “closeprobability”). Suppose you wanted a “probability-weighted” revenue forecast. You can create a custom attribute, and then use a workflow to multiply the built-in fields by each other to update the custom attribute. Here are the three attributes I will use to illustrate, all on the Opportunity entity:

• Est. Revenue (entity name estimatedvalue, type money)
• Probability (entity name closeprobability, type int)
• Weighted Revenue. This is the custom attribute. Name it what you like and give it a type of money. 

After adding the custom attribute, we need to build a workflow to update its value with the product of the “Est. Revenue” and “Probability” attributes. The Multiply by operator works by multiplying the current value of a field by a single numeric value. What this means is that you cannot enter a formula as you might think. My inclination was to have a workflow update the weighted revenue value with something like this:
   =(est. revenue)*(probability)

Unfortunately it’s not quite that easy! Since the Multiply by operator only allows you to update the current value by multiplying it by something else, the workflow needs to perform three consecutive Update Record actions:

1. Update weighted revenue with the current value of Est. Revenue.
2. Update its new value by multiplying it by Probability.
3. Update its new value by multiplying it by .01 (since Probability is an integer value!)

The next figure shows a simple example of this, with three Update actions generously commented to make it somewhat self-documenting.
 

The next figure illustrates how to configure the Update Record action for step 2. In step 1, the Weighted Revenue field was updated with the value of Est. Revenue, so in step 2 we need to multiply it by Probability:

 
After that, the Update Record action in step 3 will account for the fact that Probability is an integer (e.g., 50% is represented by the integer value 50). To do that, multiply by .01, as we illustrate in the next figure.
 
Here are a few comments and suggestions in case you need to implement functionality like this:

• The reason it takes three Update Record actions to perform the calculation is that you’re always updating the current value of the field, and you can only perform one operation at a time. It would be better if you could use formulas, but you can’t. 
• You can imagine a more complicated calculation might take many more Update Record actions. This might become clunky or impractical in the workflow approach I discussed here. If it does, it might turn out that using client-side (Jscript behind form events) is a better approach. While some people might argue that it’s ALWAYS better to use the client-side script approach, I don’t agree with that. For a problem like the one we’re trying to solve here, you can use either, and which one is better depends on your requirements…as well as your skill-set!
• Another disadvantage of the workflow approach to calculating fields is the latency problem: Dynamics CRM workflows run asynchronously, one implication of which is that calculated fields will take some time to update on the form, generally from 15 to 30 seconds. If you use the workflow approach, you will probably want to take the calculated field off the form, or maybe “hide it” on another tab.
• How should a workflow like this one be triggered? You might need to run it manually for records that were created before you implemented it. On an ongoing basis you might want to run it when a new record is created, or when the Est. Revenue or Probability attributes change. Again, the specifics will be determined by your business requirements. This figure shows what would be a reasonable configuration of an automatic workflow to do this:

Charting CRM Data Across Categories

You can use the CRM 4.0 Report Writer to easily build graphical summaries of your Dynamics CRM data across categories: accounts by owner or territory, sales by product or pipeline stage, etc.

In my case, however, this only seemed “easy” once I understood how the report writer works! (funny how often it works like that)

In this new “Ten-Minute Tips” format, I’m going to depart from my usual detailed (a.k.a. long!) step by step explanations and try to boil it down to a single key step of the process.

So, assuming you understand the basics of how to create and save reports, if you want to build charts, you need to understand the innocently-named “Lay Out Fields” step of the Report Wizard. I’ll illustrate with a report that displays a bar chart of estimated revenue (using the Opportunity entity). I want to see a “sales pipeline”, and in the example I use the built-in “Status Reason” attribute for the stages of a sales process.  

The notes in this figure summarize this as pithily as I’m capable of:

For more detail…

Here’s the Video

My son thinks this ten-minute video is the third-geekiest ever posted to YouTube, but if you need to create charts like this, you might find it useful:

I’ve written a few times about integrating Internet marketing into your overall Dynamics CRM marketing efforts. In particular, one of the big gaps in the traditional feature set was the lack of a web-to-lead function so real “marketing types” could capture leads from a web form directly into Dynamics CRM…without having to write code to do it! So, let’s start with

A Review of Internet Lead Capture for Dynamics CRM

This problem now has some good solutions, and one that I particularly like — Web2CRM from CRM Innovation at http://www.crminnovation.com — is the starting point for this article.

To quickly review, what Web2CRM lets you do is create an Internet page with a form on it, into which a visitor can enter information, and from which you can pop the information directly into your CRM. One big advantage of the Web2CRM product, compared to (for example) the Microsoft implementation in CRM Online, is that Web2CRM lets you create a web form for ANY entity in your Dynamics CRM, including custom ones! So while the CRM Online approach only lets you accept Internet Leads into the new “Internet Lead” entity, Web2CRM gives you a sweet drag and drop form designer you can use to create a web form to populate any entity.

For example, here’s a look at the Web2CRM form design environment. In the next figure, I’ve already selected the entity I’m designing a form for, and I’ve got a nice drag and drop environment I can use to select any of its fields to place on the form. You get a nice design-ish view of your CRM form on the left hand side, from which you can drag and drop fields onto the Web2CRM form-in-progress in the middle. At the right, you can see which entity the form’s being designed for, and you get some other form-level settings I won’t go into here.

If you click the Publish button you get a very generic form that is hosted on the CRM Innovation servers — another example of the hosted “software+services” model. But if you click the cryptically named “Carry Code” button you open up a window with a bunch of script code you can simply copy and pasted onto one of your very own pages, and create a page on your site that looks like this one (or however you want yours to look!):

To review, the form is created for the “Info Requester” custom entity I created in my CRM organiztion. So if you fill out the form you will find here —  www.DynamicsCRMTrickBag.com/request-content — a new record gets created in my CRM with the information you provide.

So…on to the main point of this article…

What to do with those Internet Leads?

One good thing to do is to send an acknowledgment email — often referred to as an “auto-responder”. In Dynamics CRM, one of the seven actions a workflow can take (can you name the other six?) is to send an email, so I can create a workflow to run automatically when a new Info Requester record is created and send a nice auto-responder. But…you can’t email directly to a custom entity (yet!) in CRM 4, so the first thing my workflow has to do is create a Lead record based on the Info Request record. So here’s what my simple workflow looks like:

The “Create Lead” action simply takes the fields entered onto the web form, and pops them into the corresponding fields on a new lead record.

You can use dynamic values to configure the “Send e-mail” action, crafting a somewhat customized and oh-so-responsive response! Like this:

And last but not least, notice the Attachments tab in that figure. You can attach one or more items to a workflow-generated email in Dynamics CRM 4, so depending on what kind of information people might be interested in receiving — a white paper, say, or even a Dynamics CRM workflow all zipped up and ready to go — you can include that in the response as well.

If you want to see how it works, feel free to fill out this form, and I’ll get immediately back to you with a responsive auto-responder, attached workflow and all!

Richard

 In a recent article on the Dynamics CRM Team Blog, Ben Vollmer outlined an approach for assigning Leads on a “round-robin” basis, first one to rep a, second to rep b, etc. I’d been asked about this a few times so I was glad to see Ben’s excellent solution. When I tried to implement it, however, I found it a little complicated and realized I didn’t fully understand it. And when I don’t fully understand something, I always flatter myself by concluding that it’s a complex thing and that other people might not understand it either.

So my contribution is a slightly more detailed explanation of a slightly different approach. Judging from some of the comments and questions re. the original article, plenty of people are interested in this approach to record assignment, and not just for Lead records. And in Case you’re wondering, it works just as well for Case as it does for Lead!  I’ll cover how to do the Lead assignment version first, and at the end of the article show you how it would work for the Case entity.

First, Create a Custom “Counter” Entity

Follow these steps to create and configure the custom entity:

1. Create a new entity, called Lead Assignment Counter. You can accept all the defaults when you create this custom entity, although as you’ll see there’s no real reason to turn on support for Notes and Activities.

2. Create one custom attribute for this entity and name it “Counter”. It should have a Type of “int”, for integer. You can give it a minimum value of 0 or 1 if you like but that’s not required.

3. Create a new 1:N relationship from Lead Assignment Counter to Lead, by clicking the 1:N Relationships button, then New. Configure the relationship as I show in the following figure, by selecting Lead in the Related Entity pull-down list.

 
4. Save and close the Relationship window, then click on the Information tab for the custom entity and select “Settings” in the “Areas that display this entity” section.

5. Save and close the custom entity window, and then publish the Lead Assignment Counter entity.

6. Finally, refresh the browser window if necessary, and navigate to the Settings area, where you should see the new entity. Click the New button to add one record. In the example here, I kept the default settings for the “Name” attribute. This is obviously not required, but to make a point I entered a short note in that field, and “seeded” the Counter field with a starting value:

Now, Build the Workflow to Assign Lead Records

Once the Lead Assignment Counter entity is created, I’m ready to create the Lead Assignment workflow.

1. Create a new workflow on the Lead entity with an appropriate name, give it a Scope of Organization, and have it run automatically when a new record is created:

2. The workflow itself will consist of two parts. The first part is a single Update Action that will use the 1:N relationship I created from Lead Assignment Counter  to Lead. Effectively, every Lead record will be a child record of the single Lead Assignment Counter record. Remember the primary entity for this workflow is Lead, but the Lead entity is a child entity in this particular 1:N relationship. This way we will have access to the Counter field in the parent entity, and can use it to keep track of whose turn it is to get the Lead assignment! The first part updates the Lead record, so pull down the Add Step menu and select Update Record, to make it look like this:

3.Then click Set Properties, and navigate to the Additional Fields tab. The “Assignment Counter” lookup field should be there.  This is the Display Name of the 1:N relationship created previously. And in this case since there’s only going to be one record on the parent entity, you can simply use the lookup field to associate the Lead record with the record from the parent entity, as the next figure shows. Click Save and Close after selecting this.

 
4.The second part of the workflow is a series of conditional branches, one for each of the sales reps who will be getting the lead assignments. Each branch uses a Check Condition to see what the current value of the Counter field is. If it’s current “1”, the assignment in this example goes to Amy; if it’s “2”, it goes to Andreas, and so forth, as you see here:

5. After each Assign action, the next action will be an Update Record. We need to reach back up into the parent record we use for counting and increment the Counter field, as you see in the next figure. An alternative approach would be to “hardwire” each of these steps, with the first one setting the value to 2, the next step setting it to 3 and so forth. On the whole I prefer the approach we show here, since it makes it a little easier to keep track of things if you need to add or remove sales reps or make other changes.
 

6. The only conditional branch that’s different is the last one. On the last one you need to reset the counter value to 1, so the next record that gets created essentially starts the process over again. 

After saving and publishing the workflow, you can test it by adding a few Lead records. In the next figure you can see some test data that illustrate how it might work.

Generalize the Approach to other Entities

As I mentioned above, you can use this for assigning other record types also. Basically, anything that can assigned in a workflow, and that can be on the child side of a 1:N relationship, can take advantage of this round-robin assignment technique. Here’s what it might look like for Case:

If you’re a frequent reader of the Trick Bag you may have noticed a recent spate of articles about Dynamics CRM workflows. Most of these and many more to come are examples taken from a book I published on www.Lulu.com (If you aren’t familiar with lulu, it’s a “self-publishing” site where you can publish pretty much anything you want. It’s pretty cool, and takes a lot of the work out of publishing. Except for the part about creating the content!)

Dynamics CRM 4.0 workflows are a big topic for me, so much so that I published the aforementioned book. Here’s a link to my Lulu Storefront, which I should warn you is totally not fancy. Anyway, I’m hard at work on a new and improved version of the book, with more topics covered, more examples, and real book stuff like a TOC and an index.

Of course I don’t want to discourage you from buying the current version, so if you do, I’ll email you the new one when I finish it. I’m shooting for early May.   

So, if you ever see the following Lulu widget on these pages in reference to “Workflow in Microsoft Dynamics CRM 4.0″, feel free to …

Assigning Records Using Queues?

As all well educated CRMers know, only a limited number of record types in Dynamics CRM (Cases and Activity records, to be precise) can be assigned to  a Queue. When a user “accepts” a record from a queue, the record is assigned to that user. We can refer to this as assigning on a “first-come first-served” basis, since whoever accepts the record first gets it. Using an approach I will outline here, we can use queues to assign Lead records in this way…even though Lead records themselves — along with every other record in CRM other than Activity and Case records — cannot be assigned to a queue!

Setting Up and Building the Workflow

I wrote this workflow using the downloadable public CRM 4.0 VPC image. To build it by following the steps provided, you need to do a little setup first:

1. Create a business unit called “Sales” under the root business unit, adding Amy Alberts to that business unit and assigning her the security role of Sales Manager.
2. Add a few more users to the Sales business unit you can use for testing, assigning them to the Salesperson security role.
3. In order to get this to work right you’ll need to slightly modify the Salesperson security role, but I’ll tell you how to do that at the end of the article.   

We will use an automatic workflow that runs when a new Lead record is created. It will create a Task record and assign it to a queue. A user will be able to see the new Task record in the queue, and will be instructed to accept and complete the task in order to “claim” the new Lead. The workflow will wait until the task is completed, and then assign the Lead record to whichever user completed the task. This is actually a general technique and can be used to assign ANY record in Dynamics CRM, but we will use the Lead entity to illustrate. Here’s how to do it:

Start by creating a queue appropriate for this process. Navigate to the Business Management tab in Settings, click Queues, and click New from the Queues grid. We will call our new queue “Open Leads”, and assign it to the root business unit.

Once the queue is created, follow these steps to create the workflow:

1. Create a new workflow on the Lead entity with an appropriate name, scope it at the Organization level, and have it run automatically on the “Record is created” trigger. You can see these in the Workflow Properties section here:

2. The first action in the workflow will be Create Task. Click Set Properties on the Create: Task line to open the Create Task dialog, which you can see in the next figure.  The Regarding field will already be filled in with the Lead. We used Dynamic Values to pop the Lead Topic onto the end of the Subject line, but that’s optional.

Notice that this workflow makes “Amy Alberts” the Owner of the task. In the CRM organization this was created for, she is assigned to the Sales business unit, and has a security role of Sales Manager. We could leave the Owner field empty here, but that would cause other problems we will discuss below. The reason the Owner field is not required in automatic workflows like this one is that the workflow runs in the security context of the workflow’s owner. Since this workflow is owned by the system administrator, any record it creates that does not have a different user specified as the owner will be owned by the administrator.

3. After the Task record is created, the next action in the workflow is to “assign” that Task record to the Open Leads queue. Remember, assigning to a queue doesn’t change the owner of the task. (I always put “assign” in quotation marks to remind myself so I don’t forget!) On this step you don’t need to use Dynamic Properties since you can simply enter the name of the queue onto the lookup field you can see back in the screenshot in step 1.
 

4. After putting the Task record into the queue, add a Wait condition. The workflow will wait until the Owner of the Task record changes – which will happen when any user other than Amy the Sales Manager accepts the Task record from the queue. The next figure shows how you can specify that Wait Condition. Notice you need to identify the Task in the “Local Values” section of the drop-down menu. Any record that a workflow creates will be available in Local Values, and once you select it you can access its attributes – in this example, to specify the condition the workflow will wait for.
 

5. After specifying this condition, save, close and publish the workflow to see how it works.

Queues, Record Assignment, Security Roles and Workflows

This workflow raises some interesting issues regarding queues, record assignment, security roles, and how they interact with workflows. Basically, any user who will accept one of these tasks to get the lead assigned to them must have sufficient Assign privilege on the Activity entity. The standard Salesperson security role only has User-level Assign privilege on Activity (along with most of the other core records, including Lead). What this means is that by default, a user assigned to the Salesperson security role will not be able to accept activity records from a queue (such as the Task record we used in this example).
Suppose that we had not specified the Sales Manager as the owner of the task record created in the workflow as we discussed above. In that case the task would be owned by the system administrator, who is assigned to the root business unit. If a user assigned to the Sales business unit with a Salesperson security role attempts to accept the task from the queue, he or she would see this rather dramatic error message:

This is why we assigned the new Lead records to the Sales Manager, who is in the same business unit as the Salesperson(s). We then needed to make one change to the Salesperson security role: elevate the Assign privilege on Activity from User to Business Unit. We highlight this in the next figure.

After making this change, a Salesperson in the same business unit as the user to whom the activity has been assigned (Sales Manager Amy, in our example) can accept the task record from the queue successfully. You might think they also need Business Unit level Assign for Lead, but they don’t. This is because it’s the workflow that does the assigning of the Lead record, and the workflow runs in the security context of the owner of the workflow! Since the user has to manually accept the Task record from the queue that happens in their security context and they need these slightly higher privileges on the Activity entity.

I know — not a very sexy title, but this can be a confusing issue, and it has plenty of applications if you need to create automatic workflows in CRM. The basic issue is this: if you have an automatic workflow that should only run for SOME users but not all users, how do you do that?  

Consider the following business requirements:

Your organization will make extensive use of service cases, and will use the Case entity to track them. Most users throughout the organization will be able to create a Case record (in CRM security-speak, they will have Create privilege on the Case entity), but when a Customer Service Representative opens a Case for a customer, an automatic workflow implementing an official service level agreement will run. A user in another department can create Case records for their own purposes, but the automatic SLA workflow should not run when anybody other than a CSR opens a Case.

Using the public VPC image for Dynamics CRM 4.0, we will create two child business units (Service and Sales) underneath the root business unit (MicrosoftCRM):

We will have some users assigned to each of these business units, as you can see here:

We have a workflow that will implement our official “SLA”, which we can see in the All Workflows view here:

Notice here that the “Standard SLA” workflow is owned by Paul West. Paul is playing the role of Customer Service Manager, but the important thing is that he is assigned to the Service business unit. Since he’s in that business unit, that is also the “Owning Business Unit” of the workflow. Here’s how the properties of the Standard SLA workflow are configured:

This workflow is scoped at the “Business Unit” level, and it runs automatically, whenever a new Case record is created. So, whenever any user in the Service business unit creates a Case record, the Standard SLA workflow will fire. Whenever any user in any other business unit creates a Case record, nothing will happen.

So this is all set up to solve the business problem outlined above, but here’s the catch: only the Owner of a workflow can publish the workflow! So unless Paul West (in this example), is a very technical Customer Service Manager, somebody else is going to have to create the workflow for him. Suppose that the user creating the workflow is a System Administrator, associated with the root business unit. This is a very typical configuration in many CRM organizations. The System Administrator can create the workflow, but if he publishes it, it won’t run for customer service reps, because the administrator is in the root business unit! And if the administrtor assigns it to Paul West, the administrator can’t publish it – because, like I said, you can only publish a workflow you own!

There are a couple different approaches to this, but on the whole, I think the best one is to assign the workflow to the most senior manager (user) in the business unit you want the workflow to run automatically for, and instruct that person how to publish it. (An alternative would be to move the administrator to that business unit, but that would probably cause more problems than it would solve!)

In any case, once Paul publishes the workflow, you get the results you want. Suppose Angela creates a new case. The workflow will run automatically, as she could see by clicking the Workflows link on the Case left-navigation as you see here:

But if anybody outside the Service business unit creates a Case record, nothing happens, just as it shouldn’t. (in terms of the workflow, anyway). Again, this takes a little getting used to especially if you’re in the habit of thinking that System Administrators can “do anything”. They can’t publish somebody else’s workflow, and they can’t trigger automatic workflows owned by somebody in a different business unit, as is illustrated by the following screenshot of a Case record created by the administrator:

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.