Customer Effective Blog

Large Picklists in CRM and using an XML Picklist Generator – updated for CRM 4.0

Keying in the values for a large picklist in CRM can prove quite tedious. While we try to steer clear of creating picklist attributes with too many values (~20 is a good general cut off) it is often required by the business rules. For example, records created in CRM may be passed to another application via integration and only certain values are allowed. The tedium of creating the list is magnified if you have to create more than one large picklist attribute with the same values in the same order (this would be required for example if the two attributes are mapped within the relationship between two entities).

So if you do have to create some large picklists, here's a great tool for generating the XML for the picklist values. In addition, the tool has been updated to support CRM version 4.0.

http://www.beatnik.at/picklist_for_mscrm.php

Following are the steps for creating and populating the picklist attribute:

Within CRM

1. Create the attribute in CRM and assign it at least one value
2. Export the entity
3. Make a backup copy of the XML file in case of errors in the following steps

   XML Generator website

4. Take your picklist (you can copy from a row in Excel for example) and paste it into the window on the picklist generator website.
5. Select the CRM version, Base Language, and Starting Value
6. Click the Genereate XML button and a window will open with the XML message
7. Copy the XML text

   Within Notepad or other text editor

8. Open the XML file for your exported entity
9. Locate the attribute within the XML file
10. Replace the section of the XML file from your entity with the XML text copied from the website

    Note- it will start with:

    <options>

    <option value="1">

    <labels>

    Within CRM

11. Save the XML file for your entity
12. Import the entity into CRM

The Very Annoying Beep in Virtual Server/PC

Have you ever been lost in deep concentration working on a project on a virtual machine when you click on something that generates the system alert box - and the annoying and sometimes very loud system beep that comes along with it?

The system beep is actually controlled by the Beep service. The Beep service cannot be controlled by the Service Manager, so you cannot modify the default startup properties (Manual/Automatic/Disabled). You can, however, stop the Beep service by entering the following command:

net stop beep

Note that the next time the machine reboots, the Beep service starts again. You can automate the stopping of this service by putting the stop command in a logon batch script.

Error When Deploying Web Applications in CRM 4.0

If you've written a web application that you've that utilizes the CRM web service and try to deploy it, you may receive the following error:

'Microsoft.Crm.WebServices.Crm2007.MultipleOrganizationSoapHeaderAuthentica­tionProvider,
Microsoft.Crm.WebServices, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35' doesn't exist.

The issue is that the application cannot find the Microsft.CRM.WebServices library. I've seen a few ways to solve this error. Credit goes to Wes Weeks on finding these:

1. Add the following code to your application's web.config file:

<assemblies>
<clear/>
<add assembly="*"/>
</assemblies>

2. Copy Microsoft.CRM.WebServices.dll from the CRM web root bin directory (For default web site installations, this would be c:\inetpub\wwwroot\bin\) to the Global Assembly Cache (C:\windows\assembly\)

3. Copy Microsoft.CRM.WebServices.dll from the CRM web root bin directory to the bin directory for your web application.

So is any one of these methods better than the others? Well, I think it depends, although I'm no .NET expert. I'm not a big fan of #1 - it seems almost like carpet-bombing.

I think either #2 or #3 would be your best options. If you're going to have just one web application, then copying the assembly to your app's bin directory would be fine. However, if you're going to have multiple web apps, them adding the assembly to the GAC would prevent you from having to add the assembly to each application's bin folder every time you deploy a new one.

Managing the Microsoft CRM Outlook Add-In

Workstation support teams may find themselves needing to remove the CRM add-in from Outlook and re-adding it. This was a popular troubleshooting routine for v3.0, but I've rarely (if at all) had to do this for v4.0 Nonetheless, I usually forget how to add/remove the add-in and I've never easily been able to find a quick how-to guide on doing it (my google skills may be lacking, though). I'm going to outline the steps to remove, then add the CRM Outlook add-in for both v3.0 and v4.0 in Outlook 2003 and also Outlook 2007.

Where to Get Free Microsoft Dynamics CRM Help

If you are new to Microsoft CRM, you may not be aware that there are several very active on-line communities where you can get answers from CRM experts around the world.  Here are several that Microsoft hosts:

Newsgroups

• CRM General Questions
• CRM Deployment
• CRM Developer

Forums

• CRM
• CRM Deployment
• CRM Development

All of these are very active, and questions usually have answers within several hours.  They are great repositories to search when you have a problem, and reading other people's posts can help you increase your knowledge.  They are also a good place to bounce ideas that you wouldn't want to use a support call for. 

There are users posting from all over the world, and it is nice to see how users in other countries are using CRM.

Displaying Related Records from the Microsoft CRM User Record

The Microsoft CRM user record can be very useful. It stores all contact information, job titles, manager hierarchy, etc. It can in effect take the place of a company phone list, since users (if they have read access) can easily retrieve that information from CRM.

What if you also want to utilize it as a centralized location to view all opportunities, accounts, or contacts that are assigned to a user? One hitch is that since the User record is related to all system entities, the navigation bar links for system entities are not displayed on the User record navigation bar.

Fortunately, all hope is not lost, there is a work around with Microsoft CRM 4.0. For example, here's how you would add a side tab showing all opportunities assigned to a user when you look at their user record:

Step 1: Create a new many-to-one relationship between the Opportunity entity and the User entity. Call the attribute display name whatever you want (just not owner)—I called in Salesperson in this example. We are not going to display this attribute on the Opportunity form.

 

Now you will see a side tab on the User form for Opportunities; however, when you click on it, you will see that it is empty. Next, we're going to do some workflow magic to tie it all together.

Step 2: create a new workflow for the Opportunity entity. We want this workflow to run every time that an account is created or assigned, so check the checkboxes next to "Record is Created" and "Record is Assigned." We are also going to have to manually apply this rule to any existing opportunities so they are included in the navigation bar link, so also check "On Demand."

 

Step 3: On the bottom step control pane, insert a step to update the entity. Click the View Properties hyperlink to bring up the Opportunity form template. Since we are not displaying the Salesperson lookup on the form, we will find it on the "Additional Fields" tab of the form.

Step 4: Select the Salesperson lookup form, and using the form assistant, select the Opportunity Owner under Look For, click Add, then OK.

Click "Save and Close" and go back to the workflow creation wizard. Save your workflow, then publish the workflow. Now any time an opportunity is created or assigned, it will be reflected in the Opportunity Navigation Bar link for the owner's user record.

Step 5: To make all open Opportunities display from their owner's User record, we will need to apply this rule to all open opportunities. To do so, Click on the link for Opportunities from the CRM Sales are of the Wunderbar, and select all opportunities on the page (you can click the checkbox in the upper left corner to quickly select all.

Click the "Run Workflow" button at the top of the screen, and select your newly published workflow and run it. Repeat this step for all pages in the view.

Now when you go to a user record and click opportunities, you will see a full list of active opportunities associated with the user record.

You can apply this solution to any other entity, such as contacts or accounts.

Trouble Logging In To Microsoft CRM IFD URL with SSL Certificate

In certain situations we have seen users have problems logging into an Internet Facing Deployment of Microsoft CRM 4.0 after an ssl certificate is applied. What happens is they don't get an error; it just bounces back to the login screen.

The issue has to do with browser security level. The solution is to have the users add the site to Internet Explorer "Trusted Sites" zone, and setting the security level of this zone to low or medium low. After that, users should be able to log in.

If you have a large number of users, you can simplify the process by adding the site to multiple PC's via group policy:

1. On the DC open Group Policy by opening AD Users and Computers, right-clicking on the domain name and choose Properties.

2. Click the Group Policy tab.

3. Click New.

4. Type IE Trusted Sites as the name and then press Enter.

5. Now highlight the IE Trusted Sites name and click Edit.

6. In the Group Policy Editor drill into User Configuration, Windows Settings, Internet Explorer Maintenance, Security and then double-click on Security Zones and Content Ratings.

7. Change the Security Zones and Privacy setting to "Import the current security zones…" and then click the "Modify Settings" button.

8. Click "Local Intranet" and then click the "Sites" button.

9. Click the "Advanced" button.

10. Add the CRM Web site address (ex. http://crm) and then click Add.

11. Click Close, click OK, click OK and then click OK a 3rd time.

12. Instruct all users to log out and back into their client computer.

Microsoft Virtual PC 2007 Service Pack 1 is now available

If you use Microsoft Virtual PC 2007, such as for the Microsoft CRM 4.0 demo virtual, you should be aware that Microsoft has just released Virtual PC 2007 Service Pack 1.

You will want to download this update if you are running any of the following operating systems on the virtual or on the host (your PC):

Windows Server 2003 SP3
Windows XP SP3
Windows Vista SP1
Windows Server 2008

Microsoft CRM Reporting by Fiscal Year

Many companies track their sales by quarter. When you want to report on historical sales or on future pipeline, it can be helpful to view this information by quarter; however, Microsoft CRM does not record the quarter that the sale happened. CRM does include Fiscal year settings, but this is only really used for the CRM quota functionality.

So what do you do if you have a fiscal year that doesn't correspond to calendar year? For example, if your fiscal year runs June – May?

Here is a SQL query that will do the job:

select name, accountidname, estimatedclosedate,case

when datepart(m,estimatedclosedate) >=6 then (datepart(year,estimatedclosedate))+1

Else

datepart(year,estimatedclosedate)

end as 'Fiscal Year',case

when datepart(m,estimatedclosedate) between 6 and 8 then 'Q1'

when datepart(m,estimatedclosedate) between 9 and 11 then 'Q2'

when datepart(m,estimatedclosedate) =12 then 'Q3'

when datepart(m,estimatedclosedate) between 1 and 2 then 'Q3'

when datepart(m,estimatedclosedate) between 3 and 5 then 'Q4'

Else

null

end as 'Fiscal Quarter'

from FilteredOpportunity

This query is a simple pipeline type query that returns the Opportunity Title, Account Name, Fiscal Year, and Fiscal Quarter. If the month is June-December, it puts the opportunity in the next fiscal year.

Update Microsoft CRM 4.0 from Excel

I have been testing EBAX for Microsoft CRM from Prosoft Systems. The original trial version didn't work for me, but the latest release fixes the problem that I was having. It's also much better than the first preview release, because it is a full release—it works with every entity in CRM, including custom entities.

After you install the add-on and launch Excel, you will notice that you now have a MS CRM tab on the Excel ribbon. Click on the "Set CRM Server" to configure it to work with your CRM environment. The latest version works whether your CRM environment is on-premises, IFD, or CRM Online.

Once you have configured EBAX to work with your server, you will now see drop-downs menus to select your entities and views. You will see all entities available. Once you select your entity and view, click the "Populate Data" button.

EBAX will then bring the data from the view into Exce.. If there are any required fields on the form that are not part of the view, these fields will also be retrieved. You can then update any fields that you want to change and hit the Update Data button to send your changes back to CRM.

Inside the CRM Task Pane you will see some additional drop down selectors—these correspond to any picklist, date, or lookup fields in the view. If you need to update any of these, for example sales stage, select your record, and select the new sales stage value in the dropdown that corresponds to column J.

 

What I find especially exciting about this release is that you can not only edit existing records, you can also create new records—just add a row to the bottom of the spreadsheet. It also is very logical for tracking what records are being changed, because any new/modified records are changed to a green font.

Also, it is smart enough so that you can sort the rows however you want in Excel, without messing up your CRM data when you synch your spreadsheet with CRM.

Great job—can't wait until the final release.

CRM Email Router Required to Send Email from Microsoft CRM Web Client

Sometimes you learn that everything you know is wrong. I recently had that experience in a discussion about the Microsoft CRM email router.

In Microsoft CRM 3.0, the E-Mail router (formerly called the Exchange Router) had the job of routing in-coming Exchange emails with a tracking token to the CRM mailbox, where they were tracked in CRM.

With 4.0, the E-Mail router works quite differently. It now is called the E-mail router because it not only works with Exchange, it also works with POP3, but also SMTP for outbound e-mail. That last part is the part I had to unlearn.

Microsoft CRM 3.0 web client would send outgoing emails directly via SMTP. With 4.0, outgoing E-mail also utilizes SMTP; however, the E-mail Router is now required to send email from the Web client. If you don't have the E-mail Router installed and you try to send e-mails from the web client, they won't go anywhere.

The flip side is if you are using the Outlook client, you do not have to use the E-mail Router, as CRM 4.0 for Outlook can now monitor and track emails without forwarding rules or the router. There are reasons why you would still want to use the E-mail Router if your users are using the Outlook client, if you want to centrally manage it, if you have multiple organizations, or if you use email enabled queues.

A great in-depth explanation of the pros and cons of each approach can be found on the Microsoft Dynamics CRM Team Blog.

 

Dynamic Worksheets from Microsoft CRM IFD Deployment

Problem: CRM is accessed via Internet Facing Deployment. When a user hits the Excel button on a list view, and selects either Dynamic Worksheet or Dynamic Pivot table, the resulting spreadsheet just shows the CRM login screen.

Solution: To use dynamic spreadsheets from an IFD deployment, the person opening the spreadsheet needs to have the Outlook client installed and configured on the workstation that he is using to open the spreadsheet. Since CRM is not on the same domain as the workstation, the Outlook client facilitates the login credentials for the CRM datasource used in the spreadsheet.

Solution for Scribe Error 111

I recently had an issue connecting to an ODBC data source in Scribe Insight Workbench. I could connect to the data source, view the database objects, but when I attempted to query, either a single object or custom query, it returned an error box that looked like this:

Scribe support was very helpful. They suggested that we use the Scribe OLE DB adapter to connect to the data source. We configured the OLE Adapter to connect to the ODBC DSN that we had previously set up and entered the name and password, and it worked great.

Thanks Scribe.

Hiding Navigation Items within a form

The ability to hide items on the main Navigation menu is a function of modifying the site map.  However, hiding Navigation items from within a form requires some basic jscript.  For example, a customer is not using the invoices or quotes functionality of the CRM system.  Therefore, when the Account or Contact form is opened these items need to be hidden.  The following jscript is entered on the on load event of the entity in question.  In this case, it would be the Account and Contact entity.

Of course other navigation items can be hidden to by simply replacing the item in the ("navXXXX") section of the code.

var oCloseMenuItem = document.all("navQuotes");
if (oCloseMenuItem != null) {
    oCloseMenuItem.parentElement.removeChild(oCloseMenuItem);
    }

var oCloseMenuItem = document.all("navInvoices");
if (oCloseMenuItem != null) {
    oCloseMenuItem.parentElement.removeChild(oCloseMenuItem);
    }

More IFD Lessons Learned: IFD in a DMZ

In a previous post we talked about lessons learned from using the Microsoft CRM IFD tool, or important details that aren't explicitly detailed in the documentation. 

In a recent implementation we came across a unique wrinkle.  The deployment needs to be internet facing; however, corporate policy at the client dictates that anything that touches the internet needs to be isolated from the rest of the network via a DMZ (Demilitarized Zone).

The challenge presented was that this option is officially supported at Microsoft, but it is not really documented, other than a diagram on p. 4-63 in the CRM 4.0 Implementation Guide (Planning section).

Prior to deployment, I discussed it with MSFT support.  Based on the newness of the IFD Tool, they did not have many recommendations on how to do the DMZ deployment.

So we went ahead and did the implementation, and it went very smoothly.  These are the steps and the order that we did them:

1.  Install CRM, just like any other deployment

2.  Enable DMZ

3.  Open the required ports back to the LAN: 

SQL 1433
SRS 80
Exchange 25
Domain Controllers (whatever your Domain Controllers are)

(NOTE:  Each of these pieces has an option to specify a different port upon install, so if you did a custom install, open whatever ports you selected during install)

4.  Test from CRM server to make sure nothing is broken

5.  Procure and install SSL certificate.  Important note:  for IFD deployment, a Wildcard SSL certificate is required.

6.  Set up the CRM URL (see point #3 on the previous post for details about what your IFD CRM URL needs to be)

7.  Run the IFD tool, testing to see if you can resolve the DNS.

Biggest lesson learned is that yes, it is possible to install CRM behind a DMZ and make it internet available; however, I would recommend against it if possible.  Given the number of ports that need to be opened up back to the LAN, it somewhat defeats the purpose of having a DMZ, and the IFD deployment is pretty secure without a DMZ, especially if you use SSL.

Creating A New Report System View in Microsoft Dynamics CRM 4.0

Microsoft CRM includes four categories of reports by default:

• Administrative Reports
• Marketing Reports
• Sales Reports
• Service Reports

These are good categories, but say you want to create a new category for your custom reports. Say you work for Acme co, and you want to categorize your company specific reports "Acme Reports." You can add this category, but it is a several step process.

1. Create the category

   In Microsoft CRM 4.0, go to Settings on the Wunderbar, and select Administration from the left side navigation bar. Click "System Settings." In the System Settings form, go to the Reporting tab. From here, you can create new categories, delete or rename existing categories.

   

2. Now you will have your new category defined, and you can classify reports as "ACME Reports." However, you will notice that when you view CRM reports, ACME Reports is not listed as one of the available views.

3.  To create an "ACME Reports" view, you will need to create a view, just like you would for any other entity. In CRM 4.0, click Settings, customization, Customize Entities. Select the Reports Entity and create your view. The filter logic should look like this:

You may notice that the report category is not stored in the report entity—you will need to select it from the related category entity.

4.  After you publish your customizations, you should now see "ACME Reports" listed as one of the report views; however, when you select it, you might see an error box that looks like this:

If you get this error, the reason probably is that you neglected to add the "Report Type" column to your view. Once you add the column to your view, it should work.