Form Scripting in CRM 2011

The ability to insert Javascript into Client-side Events was one of the most common ways of extending MSCRM’s functionality, allowing bespoke logic to be implemented with a small amount of development effort. This largely acted as mid-way development between Functional Customisation and more Technical Development for Server-side Plugins and Custom Workflow Actions.

However this ability to add Javascript was restricted in not being able to easily use Include Statements to incorporate Function Libraries containing common methods that the scripting could make use of across the solution – and this in turn gave rise to larger streams of Javascript on events, which could make for some unmanageable coding. (and indeed occasionally some of this, Copy and Paste Programming)

The Designers of CRM 2011 have clearly given thought to this problem and incorporated a system for attaching Javascript Libraries into CRM as Web Resources that individual CRM Events (such as Account onLoad or a particular field’s onChange) can invoke functions from.

This concept of having a Javascript Library is one that I have been advocating for a few years as way of encouraging MSCRM Developers to include their javascript files into a Visual Studio Project such that we could manage version control and source control – CRM 2011 is a big step forward to making this mandatory for including almost any Javascript in a project. This does give a natural downside in making the process for incorporating Javascript slightly more complex for people working with MSCRM, and particularly training Clients on the SDK, but this looks to be a trade-off that is common across CRM 2011 as the application comes of age.

To look at this in practise, the following steps show how to incorporate a simple helloWorld style method into the onLoad event of an Account in CRM 2011.

(1) First of all – we customise the entity in question to bring up Form Customisation.

Account Customisation - Form

Customising the Account entity form in CRM 2011

(2) Clicking Form Properties then opens a window (similar to V3 and V4) that allows Javascript to be added to the entity.

Form Scripting in CRM 2011 - 1

Customising Form Events for the Account Entity in CRM 2011

(3) From here we can either attach a Web Resource containing Javascript functions to the Account entity, or create a new Web Resource from scratch.

Form Scripting in 2011 - 2

Creating a new Javascript Web Resource from the Account Form that can then be used to hold Script Functions for events in the Account Form to invoke

(4) Typically here we would upload an existing JS file to import developed Javascript into the entity’s events – however we can also use the Text Editor button to amend or insert new Javascript directly into the Web Resource. This mimics the current method in CRM 3 and 4.

Form Scripting in CRM 2011 - 3

Directly developing the Javascript function code behind the Web Resource

(5) With the function developed within the Web Resource, and the Web Resource saved and attached to the entity within Form Properties – we can then attach the function to a particular event within the entity.

Form Scripting in CRM 2011 - 4

Attaching the helloWorld Javascript function within the Web Resource to the Account's onLoad Event with parameter

(6) This then gives us the Form Properties of the entity listing the Web Resource(s) in use with the entity, and the events that are invoking those functions:

Form Scripting in CRM 2011 - 5

The Form Properties for the Account entity listing the newly created Web Resource and Events

(7) With those steps done, we can preview the altered Account Form with the events added to see how the Web Resource script affects the running of the Account Form.

Hello World Alert

Resulting simple Hello World pop-up box alert on load of the Account Form

This gives us a simple piece of Javascript injected into CRM 2011 via a new Web Resource, and this concept of the Web Resource illustrates the change from CRM 4 in that to affect this change both the Account Form and the Web Resource must be published as two separate components before the end result will be affected to the users of CRM. In this, we can publish out code changes via the Web Resource without changing the Account Form and vice versa.

As well as the fact that we could publish this helloWorld function out to other events in CRM (such as field onChange events or other entity’s onLoad events) by simply invoking the same function and so can avoid duplicating the actual script, as we would have had to in CRM 3 or 4.

From a development management perspective, this means we can then say goodbye to managing Javascript files for each MSCRM Event in the fashion of the Visual Studio below and instead manage files of Javascript Functions that can be invoked against a variety of MSCRM Events.

Visual Studio Management of Javascript code for CRM 4 Project

Using Visual Studio to manage the Javascript for a CRM 4 Project where the Javascript code files are tied directly to MSCRM Events

Which gives us a much more structured method of managing our script files that separates out Entity Customisation from the underlying Script Functions – even if at the cost of getting accustomed to the change and appreciating the slight rise in complexity.

For further reading on using Web Resources for Form Scripting in CRM 2011 and how this now allows us to include additional Javascript libraries such as jQuery, the later post in this blog may be useful.

Advertisements
Posted in CRM 2011, JavaScript, MSCRM | Tagged , , , | 23 Comments

CRM 2011

CRM 2011 is a big step forward for Dynamics CRM, I have heard some of my colleagues describe the release as a ‘game-changer’ and having taken a more detailed look into CRM 2011 it is hard to disagree. Whilst the step between CRM 3 to 4 was evolutionary and more ‘under-the-hood’ to support multi-tenancy in order to allow MSCRM to be offered as a cloud service, CRM 2011 looks to be the most significant release for anyone working with Dynamics CRM since version 3.

And with the release of CRM 2011 just round the corner now alongside the current public Beta – now seems like a good time to start taking a detailed look at the new functionality and changes offered, and how these may affect Project Management, Functional Consultancy and Development for future Dynamics CRM projects.

The number of new areas offered by CRM 2011 is vast and so will be publishing posts on different areas that I find particularly relevant given past experience of client requirements, alongside posts detailing links and further information across the Dynamics CRM community across the web.

CRM Consultancy 2011 Posts
Dashboards in CRM 2011
Form Scripting in CRM 2011
Plugins in CRM 2011
Shared Picklists in CRM 2011
Solutions in CRM 2011
Filtered Lookups in CRM 2011
Community CRM 2011 Links
Microsoft Dynamics CRM Team Blog: SharePoint Integration for Document Management
Surviving CRM Blog: Dynamics CRM 2011 Walkthrough: new features in 74 slides
Result on demand | a CRM 2011 (aka CRM 5.0) blog
Microsoft Dynamics CRM Team Blog: Getting Started with Custom Reports in the Cloud
Creating a MS CRM 2011 VM
Customer Effective Blog: Top 10 CRM 2011 Customisation Tutorials
Microsoft CRM 2011 Links
CRM 2011 Beta Home Page
CRM 2011 Beta On-Premise Download Links
CRM 2011 Beta SDK
Posted in CRM 2011 | Tagged , | Leave a comment

Dashboards in CRM 2011

Dynamics CRM 3 and 4 could often feel like a Database-driven system given that Users were restricted to looking at data within a single entity at a time – a user would be looking at the list of their Opportunities or their list of Accounts, with only reports allowing them to see the picture of their work outside of the more narrow database definition of an Account or an Opportunity. This lead to the concept of Dashboards becoming a key requirement for many MSCRM projects as user’s liked the ability to see a view of their work, useful activities and other records in a single place.

However incorporating Dashboards into Dynamics CRM often involved SharePoint, detailed use of SQL Reporting Services or other BI Tools – this put the use of Dashboards outside of many clients and projects.

The CRM 2011 Team have clearly taken this to heart and implemented Dashboards as the heart of the user experience within CRM 2011 – replacing the default Workplace list of Activities with Dashboards as the user’s primary homepage of Dynamics CRM:

CRM 2011 Home Page Dashboard

An example of a CRM 2011 Dashboard acting as a homepage for CRM Users

These Dashboards give an option to combine different entities data together to build a entry-point into the solution that is tailored for the user – essentially taking the single-entity views of data provided by the MSCRM Advanced Find Views a big step forward.

CRM 2011 gives us the option to create these Dashboards within standard customisation options using the customisation area of 2011, which is now broken down into a more Dynamics AX approach of collapsible nodes for each of the possible customisation items (or components):

CRM 2011 Customisation

The Customisation area for CRM 2011

Here we can access all the usual CRM Customisation options that we are familiar with from CRM 4, and also the new customisation options added for CRM 2011. One of these new options is Dashboards which we can expand to view the list of existing Dashboards and potentially create new Dashboards.

CRM2011 Customising Dashboards

Customising the Dashboards Component in CRM 2011

From here, we can click the ‘New’ button to add a new Dashboard in 2011 as we have been able to add new fields or entities in the past – this brings up a simple selection screen for choosing the layout of the intended new Dashboard.

Creating a new Dashboard in CRM 2011

The first step in adding a Dashboard to 2011 is to select the layout for the Dashboard

This selection is similar to SharePoint in terms of selecting a page layout containing areas (Web Part Areas in SharePoint lingo) that can be used to drop Dashboard Charts or Lists into.

Editing the new Dashboard in CRM 2011

Editing the new Dashboard in CRM 2011 via Chart Areas

From here we can click to add content into any of the Chart Areas – typically this would be in the form of a Chart or List of CRM Data presented through the Dashboard, however CRM 2011 also gives options for incorporating iFrames or uploaded Web Resources.

Chart Charts allows the insertion of a graphical representation of a particular set of data within CRM. The data being defined as a particular view for a certain entity, to be presented via a graphical chart – such that the records returned by the View of the Entity Type can be totalled or listed within a Pie, Vertical Bar, Horizontal Bar, Funnel or Line graph.
List Lists allow the insertion of individual views of entities into the Dashboard, such that multiple different entities and/or views can be displayed to the user as a single view of the CRM Areas that relate to them.
iFrame iFrames allow the insertion of external Websites or linked Web Applications into the CRM Dashboard – this can affect Web Mash-ups or additional Custom Screen Logic to form part of the User’s Dashboard.
Web Resource Web Resources allow the insertion of static Web Content into the Dashboard, typically HTML or Silverlight sections. These differ from iFrames in that the Web Content is updated into the CRM Database as a Web Resource before being invoked in the Dashboard – such that the content is internal to CRM as opposed to being a Web Mash-up.

By combining these possible insertions (up to a possible 6 in total per Dashboard) we can build various Dashboards tailored to the appropriate audience, such as an example Sales Dashboard listing a particular Salesperson’s (through the use of the My Opportunities view filtering Opportunities to those assigned to the same person as viewing the Dashboard) list of Opportunities, list of Activities, Pipeline and key Customers.

Building an Example Dashboard in CRM 2011

Building an Example Dashboarding in CRM 2011 using standard Lists, Charts and a HTML Web Resource.

This Dashboard can also include a Web Resource containing a page of simple HTML uploaded into CRM 2011 as way of providing other information or announcements to the Dashboard audience.

Viewing the Dashboard in CRM 2011

How a Salesperson may view the customised Dashboard in CRM 2011

This Dashboard utilises the standard Charts and List available in CRM 2011 to build a simple sales-focused Dashboard – these Charts and Lists can be further extended by the creation of new Chart Types and new Views that can then be used in the Dashboard. We can see this in the customisation area of CRM 2011 in the ability to add new Charts for each Entity.

Charts for the Account Entity in CRM 2011

Customising the list of available Charts for the Account Entity in CRM 2011

Here we can add Charts that look at a particular entity and are capable of producing a simple Chart Report based on Grouping and/or Sub-Dividing based on the fields within this entity. This (similar to the Advanced Find) does give us an initial set of restrictions in what is possible here in that we cannot instruct a chart about the Account Entity to sum the Opportunities related to each Account, or vice versa compare Opportunities by a field on the related Account for comparing Opportunities by Account Type or Industry Sector – however for more simpler within the same entity purposes, this gives us a simple interface for creating new Charts that can then be used in different Dashboards.

Creating new Charts in CRM 2011

Creating new Charts in CRM 2011 for the Account Entity and for the Opportunity Entity

These Charts can then be saved as new customisations to the entity – and added to various different Dashboards. Crucially here we can take the existing Dashboard we have created and use the new ‘Save As’ function to create a copy of the Dashboard for this, as opposed to being forced to either create a new Dashboard from scratch or edit the existing.

Creating a copy of an existing Dashboard in CRM 2011

Creating a copy of an existing Dashboard to host the new Charts

This feature also applies for System Views, which solves a long sought after problem in CRM 4 for creating copies of System Views. (not quite full inheritance between views yet, but that would be a bit of a complex addition of most admin users!)

We can then add these Charts to the copy of the existing Dashboard to build up a new Dashboard:

Add Chart to Dashboard

Adding a Chart to a Dashboard in CRM 2011

This addition is done via standard MSCRM concepts in selecting Entity Type, View and then Chart to give a range of Dashboarding options.

Revised Sales Dashboard in CRM 2011

Copied version of the Dashboard with the full complement of 6 List or Chart Components

In these various restrictions this concept of Dashboarding is not as flexible as using SharePoint integrated with SQL Reporting Services, as well as the CRM 2011 Dashboards not being capable of Choice Filters and other User Interface components that SharePoint allows for.

However despite developed SRS Reports being inherently more flexible over customised Charts – the sheer simplicity here gives a powerful tool capable of creating many types of Dashboard and Charts quickly and easily, meaning that every deployment of CRM 2011 should contain a layer of Audience-specific Dashboards and so not just those projects able to afford the additional software and consultancy to maintain full developed Dashboards through SharePoint or other BI tools such as Qlikview.

This undoubtedly increases the value proposition for deploying CRM 2011 into many businesses and is a fantastic new feature that combines the best of earlier versions of MSCRM (Advanced Find, Custom Views and the Reporting Wizard) into a powerful new area of customisation without code.

Posted in CRM 2011, Customisation, MSCRM | Tagged , , | 18 Comments

Integrating SQL Reports into SharePoint for CRM

SharePoint is a collaboration product that has been growing in use and market share for a number of years, as it works to tie multiple products into a single place as a integration and presentation tool for day-to-day use.

SharePoint can be used to integrate a variety of Applications, Custom Code and Data Sources in various different methods – however as SharePoint is one of Microsoft’s flagship products, other Microsoft Applications have certain built-in hooks to allow the Application to be used via SharePoint.

Since SQL Server 2005, the Reporting Services aspect of SQL includes one of these hooks to allow Reports to be published through SharePoint as way of presenting and using any reports developed for Reporting Services. From a CRM view of world, this allows various custom SRS Reports to be presented via SharePoint as flexible Dashboards providing analysis of CRM Data.

The aim for this post is to describe how to activate this hook between SQL Reporting Services and SharePoint as a prerequisite to designing and building such Dashboards. However this concept of hooking SQL Reporting Services into SharePoint is of particular interest at the moment, as CRM 2011 will have a similar method for hooking Dynamics CRM into SharePoint.

Default Homepage for SharePoint 2007

The initial homepage for SharePoint 2007 after a fresh install of the Enterprise Edition

Once we have SharePoint (in this case, 2007) installed – we can create a new simple Team Site or Blank Site to use for our SRS Reporting. This Site will then require an initial Document Library to store the SRS Reports we wish to publish to SharePoint.

New Site in SharePoint with simple Document Library

Screen showing a new Site in SharePoint 2007 with a simple Document Library

With this in place we can look into our SQL Server Deployment to configure SQL Reporting Services to communicate directly to SharePoint – we do this by running the ‘Reporting Services Configuration’ tool within the ‘Configuration Tools’ area of SQL Server.

Reporting Services Configuration

The Reporting Services Configuration wizard for SQL Server 2005

Within this wizard, the first step is to ensure that the Reporting Services Database is set correctly for SharePoint integration by running in SharePoint Integrated Mode as opposed to the default Native Mode. This involves accessing the ‘Database Setup’ area of Reporting Services Configuration:

Reporting Services Configuration - Database Setup

The Database Setup screen within Reporting Services Configuration, allowing us to set the Reporting Services Database to be ready for integration to SharePoint

This Database Setup area then allows us to click the ‘Change’ button to change the current Reporting Services Database from Native Mode:

Reporting Services Configuration - Database Setup - Change Server Mode

Accessing the Change Database Mode screen within the Database Setup area of Reporting Services Configuration

From here we can specify to create a new Database to use for Reporting Services which is ticked for SharePoint Integrated Mode and then Apply the changes.

Reporting Services Configuration - Database Setup

After changing the Database Setup to Share Point Integrated Mode and applying the changes to SQL Reporting Services

With this step done, we can then configuration how this connection between SRS and SharePoint will operate – by clicking into the ‘SharePoint Integration’ step of the SQL Reporting Services Configuration wizard, we will be provided with a link that will open a new browser into the SharePoint Central Administration site.

Before opening this link, we should download and install the Reporting Services addition for SharePoint from the following link (depending on the version of SQL Server being used):

Reporting Services 2005
Reporting Services 2008

This is a simple MSI file which will download and install on the SharePoint Server to install the required Web Parts and Configuration options into SharePoint.

Installing Reporting Services Add-in for SharePoint

Installing the Reporting Services Add-in for SharePoint

With this installed, we can hit the link to open up the SharePoint Central Administration site to configure the link between SharePoint and Reporting Services:

Reporting Services Configuration - SharePoint Integration

The SharePoint Integration step within Reporting Services Configuration after the Database Setup has been changed from Native to SharePoint Integrated

From here, we can browse into the Application Management area to see the following list of options for configuring the SharePoint deployment – the key here is that installing the earlier Reporting Services add-in for SharePoint should have added a Reporting Services section into this list:

SharePoint Central Administration - Application Management

The Application Management area inside SharePoint Central Administration after the Reporting Services add-in has been installed

Clicking the Configure Integration option will then allow us to specify the details of the Reporting Connection into SharePoint, notably the website that SRS is currently using as it’s ReportServer Website and whether to use Windows Authentication or a Trusted Connection. For CRM purposes, we should always use Windows Authentication here to preserve the single sign-on security model between MSCRM, SRS and SharePoint through a user’s Active Directory (i.e. Windows) Authentication.

SharePoint Central Administration - Application Management - Reporting Services Integration

Configuring the Reporting Services Integration within the Application Management area of SharePoint Central Administration

With these steps done, we now have the two systems integrated together such that we can Publish SRS Reports directly into the Document Library we created earlier in SharePoint. If we open up a Reporting Project in Visual Studio (again either 2005 or 2008 depending on the version of SQL Server involved) and edit the the Properties of the Reporting Project, we can specify the SharePoint Web Site and Document Library to publish the report to.

Publishing the SRS Report to SharePoint

Publishing the SRS Report from Visual Studio to SharePoint

The parameters here for configuring the SRS Project to be published into SharePoint go as follows:

Deployment Property Description Example
TargetDataSourceFolder The URL Location of the Document Library in SharePoint that the Report will be published to http://crmserver/dashboard/SRSReports
TargetReportFolder Also the URL Location of the Document Library in SharePoint that the Report will be published to http://crmserver/dashboard/SRSReports
TargetServerURL The home URL Location of the SharePoint Site involved http://crmserver/dashboard

We now have the Report published to SharePoint and presented to any users who are accessing SharePoint through the Document Library, in the same fashion as the user would access a Document or file in SharePoint.

Published Report in SharePoint

Viewing the Published Report within SharePoint as an item in a Document Library

Published Report being viewed through SharePoint

The Published Report being viewed through SharePoint

This is not a vast improvement from the default behaviour of Reporting Services yet – which similarly gives each report a URL that user’s can access via Internet Explorer to access the report, however this does link the report into SharePoint alongside any other information held in SharePoint to start making the SharePoint Site a ‘one-stop-shop’ for information relevant the user’s accessing the site.

However the real power here is to then take the Report held inside SharePoint and use SharePoint’s Dashboard Pages and Web Part Pages to present this Report inside different pages in SharePoint as way of incorporating the report into different Dashboards, potentially with different filtering criteria to present the report differently for different dashboards as way of filtering for the audience who is likely accessing the page.

We can do this by add or using an existing Document Library in our Site to hold a number of Web Part Pages, and then creating each Web Part Page as a Dashboard to host the report we have added into SharePoint.

New Web Part Page

Adding new Content to a SharePoint Site - in this case a new Web Part Page to act as a Dashboard

Adding a Web Part Page to act as a Dashboard

Adding a Web Part Page to a Document Library in SharePoint to act as a Dashboard - choosing the title and layout for the Dashboard

We can open up this Dashboard Page and click ‘Edit this Page’ to begin populating the proposed Dashboard with Web Parts.

Customising the Web Part Page

Customising the Web Part Page in SharePoint to build the Dashboard

From here, we can add a new Web Part to represent the report we have uploaded via adding a ‘SQL Reporting Services Web Part’ to the page.

Adding Reporting Web Part to the Dashboard

Adding a Reporting Web Part to show our Report within the Dashboard

Then customise how we want this report to appear within the Dashboard by configuring this Web Part.

Configuring the Reporting Web Part within the Dashboard

Configuring the Reporting Web Part for the Dashboard we are building in SharePoint

Crucially we can click the ‘Load Parameters’ button to instruct SharePoint to read back the list of expected Parameters (only the non-hidden parameters however) into the configuration of the Web Part to then populate values for these parameters.

Configuring the Web Part to show the Report with specific Parameters

Configuring the Web Part to show the Report with a particular Parameter value

Here we are instructing the Web Part to show the Report with a hard-wired value within the parameter – such that the parameter for ‘CRM_URL’ will always be set to this for this Dashboard. In this case the Dynamics CRM Reporting Drilldown URL for the Dynamics CRM Deployment that we are reporting upon.

http://crmserver:5555/CRMReports/Viewer/DrillOpen.aspx

This allows for Dynamics Drilldown from the Report within the Dashboard into Dynamics CRM, further allowing this Dashboard to act as a home-page for the user however this could also be different filtering parameters varying from different Dashboard to Dashboard in order to make each Dashboard tailored for the intended audience without re-coding the underlying Report.

In this case we are looking at hard-wired values entered into the Reporting Web Part, however this can be taken forward to allow for specific Choice Web Parts that supply a user’s choice to the Reporting Web Part to allow the user to control how the Dashboard appears at run-time – this will be subject of further posts on this topic in the future.

However once we exit from edit mode, the Dashboard should then appear as a simple page hosting the report with the hard-wired CRM_URL value – we can then specify the Reporting Web Part to hide or collapse the other areas of the Web Part that the end user does not require:

The Resulting Dashboard Appearance

The Resulting Dashboard Appearance for the end user

Which gives us a simple CRM Dashboard presented via SharePoint.

Obviously this is a very simple example of a single report being hosted via SharePoint – but this concept can be taken forward to produce numerous flexible Dashboards that render different groups of SRS Reports that can then be further filtered by or for the intended Dashboard audience. So whilst this requires a number of different software products and technical knowledge to implement initially, this can be taken forward via no-code customisations to provide additional Dashboards more carefully filtered towards the end-user.

Dashboards have long been a key requirement for day-to-day use of CRM as way of giving user’s a Homepage to such solutions that is specifically relevant to them, and this technique of incorporating CRM, SRS and SharePoint together provides the ability to do this – particularly when we begin to look at having a suite of relevant SRS Reports and use of the Dynamics CRM Web Parts.

This concept of Dashboarding will be embedded as a core feature within CRM 2011 as will the concept of using SharePoint and CRM side-by-side, is a good time for CRM Consultants, Developers and Project Managers to familiarise themselves with the various Dashboarding options and technologies that are available. I will be endeavouring to post further on both how we can take this concept of SharePoint Dashboards further and also on the other methods such as the use of Silverlight and the Qlikview product.

Posted in Installation and Setup, SharePoint, SQL Reporting Services, Technical | 2 Comments

MSCRM and AX: Leveraging AX Business Logic from MSCRM

One of the running posting topics on this blog is integrating MSCRM and AX together to give a single solution across CRM and ERP (where using MSCRM as opposed to AX’s CRM Module), the following post has been added to this topic:

3. Leveraging AX Business Logic

This fits into the series of posts for integrating MSCRM and AX through the use of the recently released Scribe Adapter Template for MSCRM-AX integration, and how this initial template can be taken forward for more bespoke integration.

MSCRM and AX

1. Integrating MSCRM and AX via Scribe Template

2. Adding a New Published Entity to Scribe AX-MSCRM Integration

3. Leveraging AX Business Logic

Hopefully this is a topic I will have chance to expand on over the coming months, and whilst the integration here is predominantly focused on the use of Scribe as a middle-ware tool between MSCRM and AX, Microsoft are intending to produce their own integration routines and procedures between MSCRM and AX in 2011.

Posted in Dynamics AX, Integration, MSCRM, Scribe | Tagged , , , | Leave a comment

Filtered Lookup as a Drop-down Picklist in MSCRM

The ability to add a Filtered Lookup is one of those areas of core functionality that MSCRM currently lacks – this thankfully will be resolved in the forthcoming v5 of MSCRM, however at the moment we are forced to use extensions or customisations to replicate this functionality.

Given the extendibility of the platform, there are many methods for implementing a Filtered Lookup in MSCRM:

– Use a Custom Developed ASP.NET screen to select and then populate the relationship.
– Purchase the popular Stunnware Filtered Lookup tool here.
– Hack the existing Lookup field type and screen to contain a custom filter, this is completely unsupported and so not advised but you can find details on this here.

And I am sure there are many other methods beyond, this post aims to outline a simple pure-Javascript method of implementing a Filtered Lookup as a Picklist. This method has natural advantages and disadvantages but can be a simple solution for when time or budget may be an issue.

Drop-down selection of a Primary Contact for an Account

Drop-down selection of a Primary Contact for an Account

STEP 1 – Add a Dummy Picklist field to the Entity involved

This method aims to use a Picklist as the user interface for a drop-down selection of the Filtered Lookup, as such we need to create this Picklist as a new field. This is wasteful as the Picklist will simply be used for the presentation and not actually store anything, however this simply allows us to maintain a set of picklist values:

Adding a dummy Picklist attribute to the Account screen in MSCRM in order to allow for a Filtered Drop-down selection of a Primary Contact

Adding a dummy Picklist attribute to the Account screen in MSCRM in order to allow for a Filtered Drop-down selection of a Primary Contact

As a side note, we could use onLoad scripting to add a SELECT HTML Tag into the Entity screen to create a temporary non-MSCRM field Picklist, however this would involve injecting HTML into the Form’s Document Object Model and so would push us into unsupported territory.

STEP 2 – Add a Javascript Function to Populate the Dummy Picklist

We can implement this Filtered Drop-down via a series of Javascript functions within the onLoad script of a particular entity – starting with a function to populate the initial list of drop-down options:

/// <summary>
/// Populates a particular Picklist field with values retrieved from the MSCRM Web Service
/// </summary>
/// <param name="filteredDropdown">The CRM Picklist control to use as the Filtered Drop-down</param>
/// <param name="filterCondition">XML describing the CRM Filter for retrieving the records for the filtered drop-down</param>
/// <param name="entityType">Describes the Entity Type involved for retrieving the records for the filtered drop-down</param>

/// <param name="recordIdAttribute">Specifies the Database name of the Primary Key field for the Entity Type involved - should always be [entityType] + 'id'</param>
/// <param name="recordNameAttribute">Specifies the Database name of a String Name field to retrieve for the Drop-down</param>
/// <param name="recordAdditionalAttribute">Optional, specifies the Database name of an additional field to retrieve for the Drop-down</param>

/// <param name="relationshipAttribute">Specifies the CRM Lookup control to populate the relationship when a value is chosen from the Filtered Picklist</param>
/// <param name="allowNull">Specifies whether the filtered Picklist should include a Blank option</param>
/// <param name="defaultSelect">Specifies whether the filtered Picklist automatically selects the first option upon population</param>
PopulateDropdown = function(filteredDropdown, filterCondition, entityType, recordIdAttribute, recordNameAttribute, recordAdditionalAttribute, relationshipAttribute, allowNull, defaultSelect) {
    try {
        var includeAdditionalAttribute = false;

        filteredDropdown.length = 0;
        if (allowNull == true) {
            filteredDropdown.AddOption("", null);
        }

        // determine whether to inclue an additional attribute in the drop-down list (such as Job Title)
        if (recordAdditionalAttribute != "") {
            includeAdditionalAttribute = true;
        }

        // Build the Fetch XML Message to run against the MSCRM Webservice
        var fetchXml =
            "<fetch mapping=\"logical\">" +
                "<entity name=\"" + entityType + "\">" +
                    "<attribute name=\"" + recordIdAttribute + "\" />" +
                    "<attribute name=\"" + recordNameAttribute + "\" />";

        if (includeAdditionalAttribute == true) {
            fetchXml += "<attribute name=\"" + recordAdditionalAttribute + "\" />";
        }

        fetchXml += filterCondition +
                "</entity>" +
            "</fetch>";

        // Invoke function (defined above) to send the XML to the MSCRM Webservice
        var resultXml = FetchXmlResponse(fetchXml);

        var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
        oXmlDoc.async = false;
        oXmlDoc.loadXML(resultXml.text);

        // Parse the returned XML Response returned from the MSCRM Webservice
        var result = oXmlDoc.getElementsByTagName('result');

        if (result != null) {
            var n;
            var lookupItem = new Array;
            var optionText;
            var optionSelected = false;

            lookupItem = relationshipAttribute.DataValue;

            // cycle through results returned from the MSCRM Web Service
            for (n = 0; n <= (result.length - 1); n++) {
                var record_id = result[n].getElementsByTagName(recordIdAttribute);
                var record_name = result[n].getElementsByTagName(recordNameAttribute);

                if (record_name[0] != null) {
                    optionText = record_name[0].text;
                }

                if (includeAdditionalAttribute == true) {
                    var record_additional = result[n].getElementsByTagName(recordAdditionalAttribute); ;

                    if (record_additional[0] != null) {
                        optionText += ", " + record_additional[0].text;
                    }
                }

                filteredDropdown.AddOption(optionText, n);

                dropdownOptionText[n] = record_name[0].text;
                dropdownOptionValues[n] = record_id[0].text;

                if (lookupItem != null) {
                    if (lookupItem[0].id == record_id[0].text) {
                        filteredDropdown.DataValue = n;
                        optionSelected = true;
                    }
                }
            }

            // has the dropdown selected option being populated by a pre-defined value in the relationship?
            if (optionSelected == false) {
                if (result.length > 0) {
                    if (defaultSelect == true) {
                        // default select to the first option populated
                        filteredDropdown.DataValue = 1;
                    }
                    // otherwise a blank value will be selected
                }
            }

            filteredDropdown.onchange = function() {
                DropdownSelection(filteredDropdown, entityType, relationshipAttribute);
            };
        }
    }
    catch (ex) {
        alert("PopulateDropdown - " + ex.description + ".");
    }
}

This relies on other Javascript functions to send a XML Message to the MSCRM Web Service in order to receive a XML Response back from the MSCRM Web Service – this is common technique when working with MSCRM (althrough not one to be over-used as Javascript can quickly bloat for the various onLoad events) with a number of possible implementations.

The following script, which is re-used several times across this blog, is one possible implementation of this:

function htmlEncode(source, display, tabs) {
    function special(source) {
        var result = '';
        for (var i = 0; i < source.length; i++) {
            var c = source.charAt(i);
            if (c < ' ' || c > '~') {
                c = '&#' + c.charCodeAt() + ';';
            }
            result += c;
        }
        return result;
    }

    function format(source) {
        // Use only integer part of tabs, and default to 4
        tabs = (tabs >= 0) ? Math.floor(tabs) : 4;
        // split along line breaks
        var lines = source.split(/\r\n|\r|\n/);

        // expand tabs
        for (var i = 0; i < lines.length; i++) {
            var line = lines[i];
            var newLine = '';
            for (var p = 0; p < line.length; p++) {
                var c = line.charAt(p);
                if (c === '\t') {
                    var spaces = tabs - (newLine.length % tabs);
                    for (var s = 0; s < spaces; s++) {
                        newLine += ' ';
                    }
                }
                else {
                    newLine += c;
                }
            }
            // Leading or ending spaces will be removed from a HTML Request, unless flagged as a nbsp type character
            newLine = newLine.replace(/(^ )|( $)/g, '&nbsp;');
            lines[i] = newLine;
        }
        // re-join lines
        var result = lines.join('<br />');
        // break up contiguous blocks of spaces with non-breaking spaces
        result = result.replace(/  /g, ' &nbsp;');

        return result;
    }
    var result = source;

    // ampersands (&)
    result = result.replace(/\&/g, '&amp;');
    // less-thans (<)
    result = result.replace(/\</g, '&lt;');
    // greater-thans (>)
    result = result.replace(/\>/g, '&gt;');

    if (display) {
        // format for display
        result = format(result);
    }
    else {
        // Replace quotes if it isn't for display,
        // since it's probably going in an html attribute.
        result = result.replace(new RegExp('"', 'g'), '&quot;');
    }

    result = special(result);
    return result;
}

// Fires an XML Message to the MSCRM Web Service
FetchXmlResponse = function(fetchXml) {
    try {
        var xml =
            "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
            "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
                GenerateAuthenticationHeader() +
                "<soap:Body>" +
                    "<Fetch xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
                        "<fetchXml>" + htmlEncode(fetchXml) + "</fetchXml>" +
                    "</Fetch>" +
                "</soap:Body>" +
            "</soap:Envelope>";

        var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
        xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
        xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");
        xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
        xmlHttpRequest.setRequestHeader("Content-Length", xml.length);

        xmlHttpRequest.send(xml);

        return xmlHttpRequest.responseXML;
    }
    catch (ex) {
        alert("FetchXMLResponse - " + ex.description + "");
    }
}

Microsoft post another possible implementation of this as Scripting Sample Code here. There is also a good detailed description of how and why this AJAX concept works (as opposed to lines of script) here on Customer Effective’s shared blog.

STEP 3 – Add a second Javascript Function for populating the real Lookup relationship upon selection of the Dummy Picklist

However looking back at the first piece of scripting, the highlighted line in the script invokes another Javascript function to ensure that any selection made in the Filtered Drop-down populates the actual Lookup relationship.

/// <summary>
/// Function that runs when a drop-down value has been selected in our custom Filtered Drop-down
/// </summary>
/// <param name="filteredDropdown">The CRM Picklist control used as the Filtered Drop-down</param>
/// <param name="entityType">Describes the Entity Type involved for the relationship</param>
/// <param name="relationshipAttribute">Specifies the CRM Lookup control to populate with the selected value for the relationship</param>
DropdownSelection = function(filteredDropdown, entityType, relationshipAttribute) {
    try {
        var lookupData = new Array();
        var lookupItem = new Object();

        // check that the Picklist is not blank
        if (filteredDropdown.DataValue != null) {
            // build Lookup Value to populate for the Lookup field in the relationship
            lookupItem.id = dropdownOptionValues[filteredDropdown.DataValue];
            lookupItem.typename = entityType;
            lookupItem.name = dropdownOptionText[filteredDropdown.DataValue];
            lookupData[0] = lookupItem;

            // populate the Lookup field in the relationship as the Lookup Value
            relationshipAttribute.DataValue = lookupData;
            relationshipAttribute.ForceSubmit = true;
        }
        else {
            // populate the Lookup field in the relationship as Blank
            relationshipAttribute.DataValue = null;
            relationshipAttribute.ForceSubmit = true;
        }
    }
    catch (ex) {
        alert("DropdownSelection - " + ex.description + ".");
    }
}

For these two Javascript functions to function together – we need to declare a ‘global’ pair of arrays in the script to map a value selected in the Filtered Drop-down to the actual GUID value that needs to be populated into the associated Lookup field.

// 'global' arrays to store assocation between values in Filtered Dropdown and Relationship values
var dropdownOptionText = new Array();
var dropdownOptionValues = new Array();

STEP 4 – Invoke the functions with the relevant Relationship Values

Finally with these pieces of script in place on our onLoad event – we can invoke a Main (i.e. non-function) piece of scripting to invoke these scripting functions for the particular Picklist field, Relationship and Lookup field that we wish to filter:

try {
    // Main
    if (crmForm.FormType == 2) {
        var filterCondition = "<filter type='and'>" +
                                "<condition attribute='parentcustomerid' operator='eq' value='" + crmForm.ObjectId + "'/>" +
                                "<condition attribute='statecode' operator='eq' value='" + "0" + "'/>" +
                              "</filter>";

        PopulateDropdown(crmForm.all.new_contactdropdown, filterCondition, "contact", "contactid", "fullname", "jobtitle", crmForm.all.primarycontactid, true, false);
    }    
}
catch (ex) {
    alert("Main - " + ex.description + ".");
}

We can implement this via the following Javascript to produce a filtered drop-down listing Contacts that are associated to a particular Account, which then allows the CRM User to select a particular Primary Contact from the filtered drop-down.

The final point here is to wipe the selected value of the Dummy Picklist on save of the form with the following onSave script:

if ( crmForm.all.new_contactdropdown.DataValue != null )
{
  crmForm.all.new_contactdropdown.DataValue = null;
}

This is an unfortunate step as is hacking/changing the user’s entered DataValue, however is mostly unavoidable given how we are using the Picklist. (we could avoid this by creating a number of dummy selections in the Picklist’s possible values – however this is tricky as in most cases we cannot predict how many selections the picklist is going to have from the Filter Condition.)

Practical Example

The above scripting produces a drop-down list of Contacts from the Account screen, however I would rarely suggest using several large pieces of script to solve such a simple problem and would more often advise a client that the non-filtered selection of a Primary Contact is simply a current limitation of MSCRM.

However in other instances this concept of using a Filtered Lookup can be much more key to a project – this final section of the post aims to show one particular instance as way of an example of this.

If we take a business model which handles Orders between Customers and Suppliers, such that each Sales Order has a relationship to a Customer Account and a Supplier Account, and also needs to track an Account Number or Relationship Number that denotes the relationship between the particular Customer and Supplier:

Entity Relationship Digram for showing an example of using this Filtered Lookup concept

Entity Relationship Digram for showing an example of using this Filtered Lookup concept

In this instance, we may want the CRM User to specify a Customer and Supplier for an Order and then be presented with the list of Selling Relationships (if present) that link this Customer to the Supplier as way of authorising the purchase between the two parties.

Selecting the Buying Relationship via non-filtered Lookup between the Customer Account and Supplier Account

Selecting the Buying Relationship via non-filtered Lookup between the Customer Account and Supplier Account

By default MSCRM Behaviour, this will be a Buying Relationship Lookup showing a full list of the Buying Relationship records regardless of the Customer or Supplier selected. This would be a fairly negative experience for the User to have to search for the particular Buying Relationship records that applied for the Customer and Supplier they had already specified.

To improve this, we can use the script outlined earlier amended slightly to refresh a Buying Relationship Drop-down based on the Customer and Supplier selected:

PopulateBuyingRelationshipsList = function() {
    try {
        var customerAccountId = null;
        var supplierAccountId = null;

        var custLookupItem = new Array;
        custLookupItem = crmForm.all.customerid.DataValue;

        if (custLookupItem != null) {
            customerAccountId = custLookupItem[0].id;
        }

        var suppLookupItem = new Array;
        suppLookupItem = crmForm.all.new_supplieraccountid.DataValue;

        if (suppLookupItem != null) {
            supplierAccountId = suppLookupItem[0].id;
        }

        if ((customerAccountId != null) && (supplierAccountId != null)) {
            var filterCondition =   "<condition attribute='new_customeraccountid' operator='eq' value='" + customerAccountId + "' />" +
                                    "<condition attribute='new_supplieraccountid' operator='eq' value='" + supplierAccountId + "' />" +
                                    "<condition attribute='statecode' operator='eq' value='" + "0" + "' />";            
            PopulateDropdown(crmForm.all.new_accountnumber, filterCondition, "new_buyingrelationship", "new_buyingrelationshipid", "new_name", "new_accountnumber", crmForm.all.new_buyingrelationshipid, true, true);
        }
    }
    catch (ex) {
        alert("PopulateBuyingRelationshipsList - " + ex.description + ".");
    }
}

try {
    // Main
    PopulateBuyingRelationshipsList();
}
catch (ex) {
    alert("Main - " + ex.description + ".");
}

With this in place, we can hide the actual Buying Relationship Lookup field in the Administration tab and show the Dummy Picklist field on the General tab to present a simple selection of Buying Relationships once a Customer or Supplier has been selected.

NOTE: The Customer and Supplier onChange events would naturally need to invoke the PopulateBuyingRelationshipsList() function to facilitate this happening on selection.

Dummy Picklist field showing the list of possible Buying Relationships between the selected Customer and Supplier

Dummy Picklist field showing the list of possible Buying Relationships between the selected Customer and Supplier

This gives a much simpler selection – with the added benefit that via the [defaultSelect] parameter we saw in the earlier script the first Buying Relationship between the Customer and Supplier will immediately be selected as the default, this may also aid the User Experience.

Summary

Filtered Lookup functionality will be standard customisation in the forthcoming MSCRM v5, however in the meantime, the ability here to add a simple Filtered Dropdown through Form Scripting can be a useful technique – particularly for smaller lists of information or when the filter needs to change in real-time.

Posted in JavaScript, MSCRM, Technical | 1 Comment

Custom MSCRM Reporting in Excel

The ability to dynamically export data from MSCRM to Excel is a very powerful tool as this allows Users of the CRM Solution to build reports and analyse data without development or the more deep SQL Technical Skills required for custom MSCRM Reports via SQL Reporting Services.

However the ability to export from MSCRM to Excel can be limited by where we run the export from in MSCRM – such that an Advanced Find relating to the Account entity will return fields about the Account entity, a View relating to the Order entity will just return fields about the Order entity and so on. This can be problem for analysing related entities or reporting on entities that do not appear in the MSCRM Views or Advanced Find.

Usually most key entities will be accessible in the MSCRM Advanced Find and Views, however there is one big exception to this – the detail lines on a particular Opportunity/Quote/Order or Invoice. We cannot find say the Order Lines for the Order Detail entity via the Advanced Find, which can make exporting this to Excel difficult.

However if we want to perform analysis on Total Order Quantities or ‘Sweet Spot’ Analysis on Product Types – we would need to pull this list of ‘Order Detail’ records into Excel.

Fortunately this can be done by looking at how Excel is taking the data from MSCRM and editing this to take data from the Order Detail entity manually.

This can be done via the following steps:

1. Export a standard list of Orders from MSCRM as a Dynamic Worksheet into Excel.

Exported Orders from MSCRM into Excel

Spreadsheet containing the list of exported MSCRM Orders in Excel

2. Click the ‘Connections’ button in Excel to bring up the Data Connection that Excel is using to bring the Order Records from MSCRM.

The Connections list that Excel is using to read data from MSCRM

The Connections list that Excel is using to read data from MSCRM

From here, click the ‘Properties’ button to now view the specific details of this Connection.

3. This Properties window now shows the Technical Detail of how Excel is reading records back from CRM – the specific area which concerns us is the ‘Command Text’ area. This textbox contains the SQL that Excel is running against the MSCRM Database to retrieve the Order Records. The current SQL shown is the SQL Statement that MSCRM has built for us by exporting the Orders to Excel as a Dynamic Worksheet, however we can change this to a different SQL Statement to bring a different set of records.

In this example, we could instead bring back the list of Order Products as opposed to Orders by pasting the following SQL into the ‘Command Text’ area:

SELECT
	SOD.productidname AS [Order Line Product],
	SOD.extendedamount AS [Order Line Amount],
	SOD.uomidname AS [Order Line Unit of Sale],
	SOD.quantity AS [Order Line Qty],
	SO.name AS [Order Name],
	SO.pricelevelidname AS [Price List],
	SO.transactioncurrencyidname AS [Currency]
FROM
	FilteredSalesOrderDetail SOD
INNER JOIN
	FilteredSalesOrder SO
ON
	SO.salesorderid = SOD.salesorderid
Changing the Excel Query's Command Text to bring back the Order Product records into the Excel Spreadsheet

Changing the Excel Query's Command Text to bring back the Order Product records into the Excel Spreadsheet

Clicking OK here will then affect the change in the Excel Query.

We can now jump back to the Excel Spreadsheet and click the ‘Refresh All’ button in Excel to refresh the contents of our Spreadsheet based on the revised query:

Excel showing the results of the revised query to look at the Order Product entity

Excel showing the results of the revised query to look at the Order Product entity

4. The key here is that we can now run standard Excel Functionality against our MSCRM data which is been refreshed whenever records change in MSCRM by use of the Refresh All button in Excel.

So we can use Excel Filter Command in the Data area of the Ribbon to give us quick drop-downs to analyse the data, and similarly add Charts, Reports or Pivot Tables into the Spreadsheet to further analyse the data:

Excel showing the Order Detail records from MSCRM auto-filtered to only show a particular Product Type with Excel Formulae to show totaled Amounts and Quantity sold

Excel showing the Order Detail records from MSCRM auto-filtered to only show a particular Product Type with Excel Formulae to show totaled Amounts and Quantity sold

This combined MSCRM Query and Excel functionality gives us a simple powerful reporting platform that can be used to analyse data in MSCRM without requiring Training and Experience to use Visual Studio and SQL Reporting Services.

This approach for changing the Command Text of the Excel Query does still require SQL Knowledge and a experience of the ‘Database’ names behind the CRM Fields and Entities – this can be fiddly, so I would always recommend writing these queries in the SQL Management Console beforehand to allow for testing before copying into Excel.

Writing the SQL Query in SQL Server 2005's Management Studio

Writing the SQL Query in SQL Server 2005's Management Studio

Essentially this is using the standard Excel functionality to access data from a SQL Database which can be taken much further to produce multiple queries and more detailed reporting, however this can also be used for a simple reporting connection to MSCRM based from the existing Export to Dynamic Workshop functionality.

This functionality will be improved further through the use of Office 2010’s Power Pivot functionality working alongside MSCRM.

Posted in Uncategorized | 1 Comment