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 »

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 »

Entity Navigator

If you are working on different entities at the same time and you need to quickly navigate to schema details for each entity you can use the following link to see the list which contains all entities in your metadata which have an object type code.

https://<your_host_name>/sdk/list.aspx

for example

https://powerobjectscrm.com/sdk/list.aspx

Also, if you need to see the schema detail you can use the following link.

https://<your_host_name>/sdk/mdbrowser/entity.aspx?entity=<entity_name>

for example:

https://powerobjectscrm.com/sdk/mdbrowser/entity.aspx?entity=new_policydetail
By Muhammad Usman Arshad

Posted in Dynamic CRM 3.0 | 1 Comment »

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 »

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 »

Proximity Search in MapPoint – Drawing Microsoft Dynamics CRM data on Maps

Proximity Search allows you to find businesses located around a reference point you provide. For example, hotels or restaurants can provide the nearest location of all automatic teller machines (ATMs). Or a manufacturing company can list all gas stations within a short drive from a supply center. Or a retail outlet can allow website visitors to search for stores that are open early or stores that handle return items. Or you can locate your potential customers nearby an existing customer.

Proximity Searches can be utilized in MSCRM to get maximum benefit of customer data. For example, you can draw your potential customers nearby an existing customer so that you can analyze and refer your potential leads and opportunities to existing customer to get maximum business benefits. In below section, I will demonstrate the idea by showing code snippets and pictures.The FindServiceSoap.FindNearby method is a powerful feature of the MapPoint Web Service SOAP API. Using the FindNearby method, you can add the power of proximity searching to your Web site or solution. You can perform proximity searches on regularly-updated commercial data, such as Yellow Pages listings, which are included with a MapPoint Web Service subscription. Additionally, you can upload custom data and use FindNearby in custom store-locator and brand-finder applications.For further information on how to use this method, please refer MSDN article at:
http://msdn2.microsoft.com/en-us/library/ms980179.aspx.Here are the possible steps to follow in order to draw the above example. Following code snippets will help you in understanding different steps involved in calling FindNearBy method.Get Longitude and Latitude for Existing Customer using Find Service

//Find Address Longitude and latitude
FindServiceSoap FindService = new FindServiceSoap ();
FindService.Credentials = new NetworkCredential(”", “”);
FindService.PreAuthenticate = true;�
FindAddressSpecification spec = new FindAddressSpecification();
spec.InputAddress = new Address();
spec.InputAddress.AddressLine = mAddress.Line1;
spec.InputAddress.CountryRegion = mAddress.Country;
spec.InputAddress.Subdivision = mAddress.StateProvince;
spec.InputAddress.PrimaryCity = mAddress.City;
spec.InputAddress.PostalCode = mAddress.Postalcode;�
spec.DataSourceName = “MapPoint.NA”;
FindResults results = FindService.FindAddress(spec);
�
Specifying Search Distance and Longitude and Latitude of Origin point and custom Database from where to search for nearby pointsFindNearbySpecification myFindSpec = new FindNearbySpecification();
//Specify the data source.
myFindSpec.DataSourceName = “Mappoint.InstallerAccounts”;
//Specify the latitude and longitude.
myLatLong.Latitude = 47.63873;
myLatLong.Longitude = -122.131;
myFindSpec.LatLong = myLatLong;
//Specify the distance.
myFindSpec.Distance = 10;

Limit the number of records returned by specifying the Range property//Set the Range property.
myRange.StartIndex = 0;
myRange.Count = 10;
myFindOptions.Range = myRange;�
myFindSpec.Options = myFindOptions;Return only the Properties you are interested in otherwise it will return all the properties from your custom database uploaded in MapPoint//Return only the properties that we’re interested in.
myPropertyNames[0] = “Name”;
myPropertyNames[1] = “AddressLine”;
myPropertyNames[2] = “PrimaryCity”;
myPropertyNames[3] = “RelationshipType”;
myPropertyNames[4] = “longitude/latitude”;�
myFindFilter.PropertyNames = myPropertyNames;�
myFindSpec.Filter = myFindFilter; Specify Filter Expressions to return desired data depending on the entity properties//Specify the text of the Expression.
string myText = “(RlationshipType = {0} AND ” +“Rating > {1} “;
myFilterExpression.Text = myText;�
//Set the paramater values to match the placeholders
//in the Text string.
object[] myParameters = new object[2];
string myFirstParameter = “Installers”;
myParameters[0] = myFirstParameter;
int mySecondParameter = 25;
myParameters[1] = mySecondParameter;
myFilterExpression.Parameters = myParameters;
myFindFilter.Expression = myFilterExpression;
myFindSpec.Filter = myFindFilter;

Perform Proximity Search using FindNearby Method

myFindResults = myFindService.FindNearby(myFindSpec); Find result contains potential customers who reside nearby the existing customer. This also specifies their longitude and latitude data. Now you can simple follow my previous post regarding MSCRM integration with MapPoint to draw these potential customers and existing customer to Map.http://ayazahmad.wordpress.com/2007/06/12/mscrm-and-mappoint-integration-a-picture-is-worth-a-thousand-words/ Possible MapPoint Integrations with Microsoft Dynamics CRM1. Store locators
2. Branch Finders
3. Drawing Customers to analyze Customers
4. Lead/Opportunity Analysis per Region
5. Driving Directions
6. Order Tracking/shipment Tracking
7. Finding Points of Interests in a specific route �

Posted in Dynamic CRM 3.0 | No Comments »

Snap-ins for Microsoft Dynamics CRM 3.0

Snap-in applications enable you and your people to work within the familiar environments of Microsoft Office applications, such as Microsoft Office Outlook, Word, Excel, and SharePoint Server. Microsoft Dynamics Snap is a collection of products for Microsoft Office 2003/2007 users to work with certain Microsoft Dynamics applications. Snap-ins allow Microsoft Office users to interact with data and business processes from Microsoft Dynamics applications as they create documents, collaborate and manage their calendar—all without having to leave Microsoft Office. The following Microsoft Dynamics Snap for Microsoft Dynamics CRM 3.0 applications are available today in Technical Pre-release and are distributed under the Microsoft Permissive License. Microsoft partners can easily extend and customize the applications, and even use components within applications they’ve developed.

• Business Data Search
• Business Data Lookup
• Custom Report Generator
• Customer Journal

For more information, please look at:

• http://www.microsoft.com/dynamics/product/snap.mspx
• http://www.codeplex.com/crmsnap
• http://www.microsoft.com/presspass/features/2006/feb06/02-20Dynamics.mspx
• http://www.gotdotnet.com/codegallery/codegallery.aspx?id=b44f8ee7-4d2b-4e39-9bfb-1119fffbe018

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

VPC Development Environment for Microsoft Dynamics CRM Projects

After collapse of my QA server, I have been searching for various developments environment options for Microsoft Dynamics CRM 3.0 projects. As per my findings and discussion with other peer developers, most of them are using Virtual PC images for their development tasks. This is also recommended by Microsoft and a download at Microsoft website regarding VPC image is also available.  This will benefit in various ways: 

1. Developers will not be dependant on other developers in performing their Customization tasks at the same time. In this way overlapping of customizations done by developers can refrained.
2. In case of multiple clients, it’s the ideal option as we are not going to setup our QA server and Development Server for every client.
3. Building new test environment will be a Zero copy deployment.
4. One has the options to cut down Development Server cost by just adding more RAM to developer PCs.
5. One can test various components developed by other vendors with no impact over our QA server and Development server.
6. One can test various integration scenarios with no impact over QA and Development Server.
7. Maintaining Microsoft Dynamics CRM server would be just as easy as importing and exporting Customizations to fresh VPC image.

If you have any issues regarding VPC please do comment. Thanks!

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