PowerObjects Microsoft CRM Blog

Filtered Views in CRM Reporting

While working with CRM Reports, we faced a scenario where data was not getting retrieved from FilteredViews in our IDE. Interestingly same queries were returning data from CRM tables but nothing from FilteredViews.

E.g. “Select * from SalesOrderDetail” table retrieved rows
“Select * from FilteredSalesOrderDetail” retrieved no rows

We made several checks including:

• Report connection/data sources
• User rights in CRM
• Using the right DB and Organization

These checks yielded no luck. Eventually we found the actual cause.

Filtered views will never return records from a connection string using SQL authentication

Use Windows authentication in connection string properties for all CRM reports.

Posted in Dynamic CRM 3.0, Dynamic CRM 4.0 | No Comments »

Programmatically attaching file to Note - CRM 4.0

In Dynamics CRM 4.0, file attachment to entities is pretty simple as compared to CRM 3.0. UploadFromBase64DataAnnotationRequest and UploadFromBase64DataAnnotationResponse object are both deprecated. Instead a new attribute has been introduces in annotation object with documentbody that accepts string in base64 encoding. Please find the below sample:

Reading file data into Byte array and then converting it to base64. Since I am using in memory object of StringBuilder class so I have just used str.ToString() to return string.

Byte[] data;
ASCIIEncoding encode = new ASCIIEncoding();
data = encode.GetBytes(str.ToString());
string b64 = Convert.ToBase64String(data);

Next, simply create the annotation entity and set the attributes as below:

annotation note = new annotation(); 

note.subject = “Test Export”;
note.filename = “exported.txt”;
note.mimetype = “text/html”;
note.documentbody = b64;note.objectid = new Lookup();
note.objectid.type = “new_export”;
note.objectid.Value = entityid;
note.objecttypecode = new EntityNameReference();
note.objecttypecode.Value = “new_export”;
                     �
Guid annotationId = service.Create(note);

Posted in Dynamic CRM 4.0 | No Comments »

Dr. Strangelove Or: How I Learned To Stop Worrying and Love The AutoUpdate Tool

Greetings,

Eric Newell from the Microsoft CRM Development Team recently posted a blog article outlining the procedures for setting up and configuring the AutoUpdate feature for Microsoft Dynamics 4.0 for Office Outlook. While Mike did a fine job with his post, it left a lot to be desired in terms of overall clarity and cohesion. Recently, I was assigned the task of setting up the AutoUpdate feature for one of our implementations and as I stared at the instructions written by Mr. Newell, eyes glazed over in confusion with a pounding headache from trying to pull my hair out for the fifth time in 1 hour, I had a revelation: Boy, wouldn’t it be nice if someone was able to present these instructions in a semi-nonsensical manner? So world, this is my gift to you: AutoUpdate Installation and Configuration steps that your mum could understand!

How many times have you started a troublshooting call with a client with: “Do you have all the latest updates installed?” This is the go-to questions for troubleshooters the world over. Wouldn’t it be great to have a mechanism in place, ensuring that each user updates his or her client with the latest patches and hotfixes? That is the intent of the AutoUpdate tool. So how does it work? Read on…

 When you install the CRM Outlook Client, one of the new features is a program simple called “Update.” Perhaps you have seen this icon in your Outlook Client folder and wondered what is was. Maybe you were really adventurous and double-clicked it to see what it did. Update is the client-side piece of the AutoUpdate process and honestly, is mostly worthless unless your CRM Admin (whom you should bring gift-wrapped sweets and/or cash bribes to every day) sets everything up properly. However, when everything IS in place, AutoUpdate will prompt the user to install available patches as they sign into the Outlook Client. During, sign on a window will pop up for the user and are given a “choice”: Install the patches or disable their access to CRM. The update tool can be launched manually from the client (and remember, without proper server side set up, it does NOTHING), ran as a scheduled task or (as we are discussing) prompted for the user on each time they log into the system. (Don’t worry…as soon as the update is installed, you no longer see the scary “install or die!!!” screen)

 1. You will need to create a folder to hold these downloaded patches. Eric Newell recommends going to [ServerInstalDirectory]\Server\CRMWeb and then creating a folder in that location called CRMPatches. My organization frequently takes advantage of the multi-tenancy design of CRM 4.0 so we really don’t have 1 org on 1 server. I looked around for awhile to determine where this location was in our implementation. We do not have a directory structure like this (and you probably don’t either) so you will need to create this folder where the root IIS folder is. In our case, I made this folder at C:\inetpub\wwwroot\. If you are unsure of where to create this file, open IIS Manager on your CRM server (start>Programs>Administrative Tools), select “Websites” from the Left Nav Column, and then right-click on “Microsoft Dynamics CRM” when it appears in the “tree” and select properties. By selecting the Home Directory Tab in the Properties Form you can determine what the root folder is.

NOTE: It is *not* required to place this folder in the CRM Server.  My organization created a “dummy” website we called “patches” that would simply point to the CRMPatches location. The creation of this dummy site allows us to store all patches in one location which is convenient when we must deploy patches to multiple organizations. Rather than needing to upload different patches to each each organizations server, we simply point their Update tools to our dummy site to download the new patches. This site eliminates the need for keeping track of what patches have been uploaded where…

2. Next, you will need to change a few things on each client. In the client’s registry we will need to add a value to ensure the AutoUpdater points to the patches in our CRMPatches Folder, as opposed to a website (which is the default). If you are unsure how to access the Registry Editor tool you can either open a command prompt and type “regedit” or navigate to C:\Windows\ and located the “Regedit” application file. Once we are in the editor we need to add a string value to HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRMClient. To create a new value simply right click anywhere to the right of the “tree” in the RegEdit window.

We will want to call this new string value “AutoUpdateDownloadUrl” and we will give it the value of our CRMPatches folder (or dummy site) from step 1…DO NOT FORGET THE CLOSING SLASH!!! /

3. Now that we have the folder created and the registry changes completed, now we must download the patches and extract the contents to our CRMPatches folder from step 1.

4. Now that we have the patches in our CRMPatches folder, we must do a little work to determine the PatchId value of each patch. We need this PatchId for the next step when we create our config.xml file. You may be thinking to yourself, “Hey, how do I unzip a .exe file? Is that even possible?” I didn’t think it was possible either, but with some help from our friend Command Prompt, we can unzip this file and figure out what it’s PatchId is…

a. On your CRM Server, open a command prompt and type [DownloadLocation]/CRMv4.0-KB[KB # here]-i386-Client-INTL.exe /x

b. The system will prompt you to create a location for the unzipped files. Choose one to your liking, as it does not matter too much where this location is.

c. Once extraction is complete, navigate to the folder where you unzipped the contents and look for a file called “config.xml.”

d. Open the config.xml file and look for a node called <PatchId>. Write this number down. We will need it in Step 5 when we create a NEW config.xml file for the patch.

e. You should delete the unzipped contents of the file from your system at this point. The CRM AutoUpdate tool will not know how to work with unzipped contents; it only recognizes the CRMv4.0-KB1234-i386-Client-INTL.exe file. If you need to, redownload the .exe file to your CRMPatches folder. After this step we should once again only have a .exe file in our CRMPatches folder…Nothing Else.

5. Now for the fun part: XML File Creation! There are a whole host of options when it comes to creation of this XML file. There are some pretty powerful abilities at your disposal here so I recommend checking out the CRM 4.0 Implementation Guide which explain these abilties in much more detail. For our purposes we are going to create a plain Jane config file. No bells and whistles. What you see below is a fully functional file that was made for the KB949086 fix. You can use this XML file as a template for each subsequent patch you intend to deploy…As a general rule, each patch should have it’s own config file (although it is possible to include multiple patches per file, this is a more “advanced” technique and goes beyond the scope of this blog) 

<ClientPatches>

<Create>

    <ClientPatchInfo>

        <PatchId>{85F5616A-F266-4E0B-BB4C-39B5B3AECE5C}</PatchId>

        <Title>Duplicates in Outlook with Shared Calendars, Tasks or Contacts</Title>

        <Description>Sharing calendar, contacts or tasks can allow editors to clear all custom crm information resulting in duplicates when synching</Description>

        <IsMandatory>true</IsMandatory>

        <IsEnabled>true</IsEnabled>

        <ClientType>OutlookDesktop,OutlookLaptop</ClientType>

        <LinkId>CRMv4.0-KB949086-i386-Client-INTL.exe</LinkId>

    </ClientPatchInfo>

</Create>

</ClientPatches>

You may call the XML file whatever you desire…Just be sure to save it in the CRMPatches directory!

6. We are getting close to completion now! There should only be one more step to complete now that we have our config file created. We must locate the directory where clientpatchconfigurator.exe is located (Best accomplished by using “search” if you do not know the location off the top of your head). Once you have this located the directory, open the Command Prompt and change the directory to that location. The shot below indicates the default location for this file as well as the “cd” command to Change Directory in command prompt.

Now that the directory has been changed in the prompt, we are finally ready to run! Type microsoft.crm.tools.clientpatchconfigurator.exe [configFile].xml (The config file you are referencing will the the file that we created in Step 5). For my implemenation this command was as follows:

microsoft.crm.tools.clientpatchconfigurator.exe c:\inetpub\wwwroot\crmpatches\config.xml

If all went according to plan you should see…nothing. The process should run and you will not receive any type of message of successful completion.

7. Once the patch has been uploaded to the server, navigate back to your client and launch Outlook. If all is well in the world, you will see this screen:

Just click the Update Now button and you are now completed! Now, each time a user starts their Outlook Client they will be prompted with the above screen before being allowed to log on. The hotfix installs will show in the Installed Updates area within Programs and Features in the Windows Vista Control Panel or in Add/Remove Programs on Windows XP. That should be it!

A FEW CLOSING THOUGHTS:

1. The AutoUpdate Tool, while needing a little work to get set up, can greatly improve your operating environment by ensuring that all users are running the same client version. Support calls and e-mails shouldn’t have to be answered with the generic “Do you have all the updates installed?” question. For new patches that become available, follow the same process from Step 3 to the end. Each new patch will need their own config XML file which then needs to be ran through the clientpatchconfigurator tool. After this tool is run, the patches will be available for download to your users.

2. I initially had some hesitations about this tool, mostly due to the fact that it appeared as thought it would require the system admin to go in and manually change EACH AND EVERY user’s registry. After doing some more research I came across a tool know as Group Policy which can be used to set global registry values. Rather than going through the horror of tracking down all of your users, Group Policy can be used to make sure that each user has the appropriate registry values. While the Group Policy Management Console may not have this ability by default (to my knowledge) there is a free extension available here that can make the organization wide registry changes we desire.

 Hope this article has expanded and clarified a lot of Eric Newell’s original blog entry. While this may seem like a lot of leg work to get going, once properly configured and running, the AutoUpdate tool is an unbeatable addition to CRM 4.0. It has made a believer out of me!

Posted in Dynamic CRM 4.0 | 1 Comment »

Dynamics CRM in Firefox? You Betcha!

Have you ever tried to use the Microsoft Dynamics CRM 4.0 web client in Firefox and received a scary screen that looked something like this?

After witnessing such a horrific screen did you immediately resign yourself to the fact that you would be forced into using IE? Did you shed a tear when you had to put away your trusted Firefox friend? While that may have previously been the case, the fine folks over at Mozdev have developed  handy plug in extension for Firefox that makes use of the IE rendering engine, making it possible to run Dynamics CRM in the Firefox browser. Madness you say? Believe it!

 So, intrepid navigator of the blogosphere, how can you experience Dynamics CRM on your Firefox browser? First you will need to install the IE Tab extension. Mozdev is a trusted, open source developer/hoster of Mozilla extensions and plug-ins so do not fear you are installing unknown and potentially malicious software. The download will contain an XPI file. To install the extension, simply drag the file into your Firefox browser window.

After downloading and installing the IE Tab extension, open your Firefox browser and point it towards your CRM site du-jour. You will see that nasty, “broken” screen again, but fear not! You are only a right-click away from salvation! Simply select the View Page in IE Tab option. Alternatively, you can click the small Firefox icon in the lower-right side of the page.

After selecting that option, the IE Tab extension will go to work, displaying the page in question through the IE Rendering Engine. Do not adjust your monitors. The image below truly is Dynamics CRM 4.0 running in Firefox!

The IE Tab Extension also allows you to set a list of websites that will automatically display using the IE rendering engine. This eliminates the need to right click and select “View Page In IE Tab” each time you visit a website. You can add your CRM web address to this list of websites along with Windows Updates Sites and other sites that are not Firefox Friendly. To access this saved list of websites, right click the Firefox button in the lower-right hand corner of the browser window and add away using the supplied menu!

The IE Tab really is a wonderful extension that will allow you to experience the amazing abilities of Microsoft Dyanmics CRM 4.0 in the browser of your choice. This extension, as you may have guessed, is not only a CRM tool. The IE Tab extension can be used to display any webpages that natively run on the IE Rendering Engine. Imagine performing Windows Updates through your Firefox Browser! What once was considered impossible is now reality thanks to this handy extension!

Posted in Dynamic CRM 3.0, Dynamic CRM 4.0 | No Comments »

Plug-in Storage

Plug-ins can be deployed to the Microsoft Dynamics CRM server’s database or the file system that is known as “on-disk”. We strongly recommend that you deploy your plug-ins in the Microsoft Dynamics CRM database, instead of on-disk, as the primary way to deploy plug-ins for Microsoft Dynamics CRM. Plug-ins stored in the database are automatically distributed across multiple Microsoft Dynamics CRM servers in a datacenter cluster. On-disk deployment of plug-ins is supported for backward compatibility with Microsoft Dynamics CRM 3.0 callouts and also to support debugging of plug-ins by using Visual Studio 2005.

By Asim Sajjad & Bilal Ilyas

Posted in Dynamic CRM 4.0 | No Comments »

Auto Numbers in MSCRM

Last week I have been looking for the best approach to use auto numbers in Microsoft Dynamics CRM. After discussion with my peers I have come to the following possible options that can be used for auto numbering. Although these may not be the efficient techniques to get auto number work in CRM but they are all workable solutions.

Option 1:

Very simple, supported and mostly acceptable way of auto numbering is to read the attribute Max value and then add 1 to get next auto number. For example you have a custom entity named Project and you want to auto number Project_Ref_Number attribute.  But do not retrieve all records. Just retrieve the record with maximum number value by using following two properties of PageInfo Class in SDK and set descending order.

PageInfo.Count = 1;
PageInfo.PageNumber = 1;

One more consideration should be taken in account to register your auto number plugin to at PreCreate rather at PostCreate.

Issues:

In multi-user environment, this approach can come up with duplicate numbers in Project_Ref_Number when more than one user is creating project records.

Option 2:

This technique requires a bit more work but still supported way of doing things and all the time you will be getting 100% unique number.

You need to create a new table for project entity in a different database and not in CRM default database.

create table dbo.ProjectNumbers
(
 Project_Ref_Number int identity(1,1) primary key clustered,
 Projectid uniqueidentifier not null
)
go

Create a stored procedure for inserting a record into new database. This will increase the performance of your insert query.

create procedure dbo.proc_new_ProjectNumber

@ProjectId uniqueidentifier,

@Project_Ref_Number int output

as

insert into dbo.ProjectNumber

(

 ProjectId

)

values

(

 @ProjectId

)

select @Project_Ref_Number = scope_identity()

go

In your Plugin/Callout:

1.   Access you database using ADO.NET

2.   Execute stored procedure and get next number from returned results

3.   Set the value of the returned number to target entity attribute approperiatly.

4.   All Done

Issues:

1.   You have to setup database.

2.   You have to read and insert into external database.

Option 3:

This option used a lock mechanism on a text file placed somewhere at your webserver. You can create one file to store next number for all entities or you can create one file for each entity. In your Plugin/Callout:

1.   Put a lock on file.

2.   Read the file and read the number there.

3.   Update the number by adding 1.

4.   Release the lock.

5.   Use this number and set your target entity attribute appropriately.

string ProjectAutoNumber = “FilePath”

lock(this)

{

TextReader textReader = File.OpenText(ProjectAutoNumber);

AutoNumber = textReader.ReadLine();

textReader.Close();

AutoNumber = (long.Parse(AutoNumber) + 1).ToString();

TextWriter textWriter = File.CreateText(ProjectAutoNumber);

textWriter.WriteLine(AutoNumber);

textWriter.Close();

}

Issues:

1.   Resource locking

2.   File system Read/write cost

Although all these options work well but I am still looking for some more robust solution. Ideally I am looking for something like GUID. GUID’s are instantly available and 100% unique.

For your suggestions and improvements, please comment.

Posted in Dynamic CRM 3.0, Dynamic CRM 4.0, Dynamic CRM 4.0 - Installation / Errors, Dynamic Online CRM | 2 Comments »

Yahoo Address Verification

Yahoo Address Verification

Problem 1: When calling Yahoo Address Verification Webservice directly in CRM forms, we get the ‘mixed content’ message.

Resolution: We create our own webservice and call the Yahoo Address Verification Webservice within it. Call our webservice in CRM forms.

Problem 2: The custom webservice works as expected when called directly but doesnt return result when called within CRM forms.

Resolution: Make sure the WS has sufficient Execute Permissions by checking Application Settings in IIS.

Posted in Dynamic CRM 4.0 | No Comments »

An error occurred loading Microsoft Dynamics CRM functionality

Well this may or may not help someone out there - but I thought I would put up a post to save you some time if you are in the same situation!  Here is where I was at:

- Used Microsoft Dynamics CRM 3.0 w/outlook client
- I am running Vista with Office 2007
- We upgraded to 4.0
- Could not get the 4.0 Outlook Client to work!

I think the big issue is that there is not a great way to clean your system of the 3.0 Outlook client.  I found numerous posts on what to do…but I still had no luck.  At one point I even completely uninstalled Office (at-least I thought I had) .  The screen shot below shows the sign of the remnant 3.0 client - I was never able to get ride of the greyed out CRM menu item.

 Then when I would go to configure the new 4.0 client after installing - all went well till right at the end then poof - the error below showed up.

I did everything I could to solve - then had about 3 different CRM specialists look at and try to solve to no avail!  I finally called in a support case and without too much pain got the issue solved.

Below is the relevant info

Scope:

**********************

After uninstalling CRM 3.0 and then installing the 4.0 client you are unable to access CRM in Outlook and you have a duplicate toolbar loading.

“An error occurred loading Microsoft Dynamics CRM functionality”

Environment:

**********************

Vista

CRM 4.0

Assessment:

**********************

First to help remove the Duplicate toolbar that we are encountering I would like you to go through the below steps.  I know you mentioned that you had your CRM engineers completely uninstalled CRM 3.0 so before going through a clean Uninstall I would like you to navigate to “C:\document and settings\user\application data\Microsoft\Outlook”  When running under vista it should be located C:\Users\”Username”\AppData\Roaming\Microsoft\Outlook.  When you find the Outlook folder I would like you to Rename this from Outlook to OutlookOld.  Then I would like you to reload your Outlook client and verify that the Duplicate toolbar no longer exists.

They had listed out a number of other steps as well - but I knew once I renamed the folder and the phantom CRM button on my toolbar disappeared I was in business.  Sure enough the install went clean and I am up and running!

 The only downside to this is the loss of some personal settings in Outlook and the stored pre-popped address that I had built up.

Posted in Dynamic CRM 4.0, Dynamic CRM 4.0 - Installation / Errors | 5 Comments »

CRM Live - Now CRM Online

This move by Microsoft has been questioned by many partners and the community at large, but we feel it makes sense, given the naming scheme that is taking shape as part of the company’s Software+Services (S+S) push. Microsoft is attempting to categorize its services into one of three categories:

• “Live” refers to consumer and very-low-end small-business offerings. Examples: Windows Live Messenger, Office Live Workspace.
• “Online” refers to small-/mid-size and enterprise services that are hosted by Microsoft in its own datacenter. Examples: Exchange Online, SharePoint Online and now Dynamics CRM Online.
• “Hosted services” refer to Microsoft offerings that are Partner hosted by its integration/reseller partners at the partners’ facilities. Examples: Exchange Hosted Services, CRM Hosted Services, and SharePoint Hosting Services.

Until now, Microsoft has been referring to the Microsoft-hosted version of its Dynamics CRM 4.0 product as Dynamics CRM Live. But given that Microsoft has made it plain that it is going to offer Microsoft-hosted versions of many of its business products to not just large, enterprise customers, but to SMBs, as well, the CRM Live name just didn’t make a lot of sense.

 We think the toughest part will be getting people to quite saying CRM Live and start saying CRM Online!

Posted in Dynamic CRM 4.0, Dynamic Online CRM, Dynamics CRM in the News | No Comments »

Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show

Office Open XML is an XML-based file format specification for electronic documents such as memos, reports, books, spreadsheets, charts, presentations and word processing documents. The specification has been developed by Microsoft as a successor of its binary office file formats and was published by Ecma International as the Ecma 376 standard in December 2006. The format specification is available for free at Ecma International.

Let me introduce you the technique I have used to generate the Excel 2007 sheet from CRM data. I have downloaded an Excel package. ExcelPackage provides server-side generation of Excel 2007 spreadsheets. See http://www.codeplex.com/ExcelPackage for details.

Specifying Output Directory and Template Directory

Output directory is where you place the generated excel file and Template directory is where you place the template file. Template file is simply a .xlsx file without data. Use following code to specify both directories.

Write data to Excel File

ExcelPackage contains library functions for accessing worksheet, cells etc. For details please have a look into the Excel package code. Uses following code to write data to individual cell in excel worksheet.

Adding Rows to Sheet

Return Excel File for Download

Simply redirect the response object in ASP.NET to the excel file generated.

All cell styles, formates and pictures objects that you set once in Template will remain the same in the generated file.

Please find below my sample quote geneation application. I have placed a button at Quotes toolbar named Generate Quote. It will generate quote report in excel 2007 with the Quote data from crm currently selected.

My program fetches data from MSCRM through web services and populated my Excel sheet. It then returns my generated excel sheet with options to open or download.

Posted in CRM Integrations, Dynamic CRM 3.0, Dynamic CRM 4.0 | No Comments »