{Useful to know}– Moving User’s Business Units in Dynamics CRM? Beware of all the side effects

We had this requirement recently with one of our clients. They had a change in their business processes and from team based security, they wanted to move to region based security.

What they wanted from us is to migrate their Users to specific Business Units based on regions. So they had five region which they set-up as Business Units. And they had around 600+ users who needs to be put under the respective business units (regions).

So we quick developed a data migration utility to do the job for us. The data migration utility would basically read from an excel file the user and their regions and then move the user in CRM to the appropriate business unit. When the business unit changes, we all know the security roles gets lost. So the utility also took care of the fact that it re-assigns the security roles to the user after the business unit of the user has changed.

So far so good. After data migration happened everything worked great. We verified the roles and business unit of the user and everything was fine.

However to our horror, the business of the customer complained that they were getting emails from CRM which they were not supposed to. And hence I was there again trying to find out what is causing the issue.

After rounds of debugging we found that there is a specific functionality in which email is triggered to the manager of an employee. The manager was set-up in CRM using the OOB Manager field. So what really triggered the wrong emails?

Well if you change the business unit of a user in CRM, the following are the effects on the manager field.

If the manager is in a business unit which is neither the same nor the parent business unit of the user, the manager of the user would be automatically changed to someone above the hierarchy.  For example – Say the following is the business unit structure.

 

image

And say we have three users (user1, user2 and user3).  Say before migration this was the org structure.

  • User1 belongs to BU1
  • User2 belongs to BU2
  • User3 belongs to BU2
  • User2 is the manager of User3

Now as per the data provided by the customers, both User2 and User3 should be in the same new BU. say in our example it is BU4.

The problem that happened is the order in which the users got migrated. So User2 (manager of user3) was first in the list. So when we migrated User2 to BU4, the manager now belongs to BU which is in the lower hierarchy than the reportee’s BU.

So what CRM did is, it re-assigned the manager of the User3 to User1 (belonging to BU1) automatically behind the scenes.

And this totally resulted in multiple wrong manager set-up and hence the wrong emails.

 

So if you have a requirement where you are needed to change the BU of the users, please do remember the effect on the manager field also. Also luckily we did not have manager hierarchy enabled for our customer. If manager hierarchy would have been enabled, this kind of wrong manager set-up would result in security breach as well.

 

Hope this helps!

{Tips & Tricks} How to optimize your report query in Dynamics CRM minimizing the use of filtered views

There are many topics on optimizing the performance of the reports in Dynamics CRM. And it has been discussed so many times about the performance of filtered views. However no matter how much we try, we have to use filtered views for the security context of the report.

And here I was called for to optimize the performance of another such report which uses filtered views. The report was constantly timing out. The report was basically a join of the FilteredOpportunity with around 10 master data entities and around 5 filter operators. And the total opportunities in the system were around 1,50,000.

So I had to optimize the report for the maximum possible records to be returned. I have to use filtered opportunity view but how do I minimize the performance effect of it. And while I was thinking, suddenly a trick came to my mind.

First I ran the query

Select * from FilteredOpportunity

The query completed in 2 minutes and 35 seconds. Then I ran the below query.

DECLARE @OPPS TABLE
(
    ID UNIQUEIDENTIFIER
)

INSERT INTO @OPPS
SELECT DISTINCT OpportunityId From FilteredOpportunity

SELECT *
FROM Opportunity O INNER JOIN @OPPS OP
ON O.OpportunityId = OP.ID

And voila!. The query returned the results in just 8 seconds. The reason is because the primary key column of a table is clustered index and the fetch on just the primary key would be very fast compared to the fetch of the other columns from the view.

 

I replaced FilteredOpportunity with the Opportunity entity view. And joined the Opportunity with the @OPPS table on the opportunityid column. Off course to retrieve the names text values of the option set and all, I had to join with the String Map table but after I constructed the query and ran the report, it consistently executed within 30 seconds even with the maximum set of data.

 

Hope this helps when you face the same problem while you develop SSRS reports in Dynamics CRM.

Switch business process programmatically in Dynamics CRM based on business logic during create of a record.

Confused by the title? Well let me explain here the exact requirement that I had recently for one of my customers.

For the opportunity entity we designed multiple business process flows. However the condition was whenever a user tries to create a new opportunity, first a default business process flow would load. Depending on the data entered by the user, based on business logic as soon the record saves, the record should switch to the appropriate business process flow.

So it’s pretty obvious how we would do it.

  • On the save of the form, we would check for the data entered by the user and determine the business process.
  • Then we set the business process id and the stage id appropriately
  • After the record is saved successfully we refresh the entire form, so that the appropriate process loads.

 

Simple isn’t it. But there are two triggers we need to handle here.

  • Pre-Save of the record – To determine the business process to be set for the record.
  • Post-save of the record – To reload the form.

For the first point, we can easily register a function on the save of the form. However if you do that there is one catch to it. The OOB save button click is synchronous and you cannot register any event handlers on successful completion of the save.

So we need to approach this approach differently. Off-course there must be many other good ways to achieve this, but the following is way how I approached the same and it worked great.

  • Create a button with the same look and feel as the Save button. You can easily do that with the help of Ribbon Workbench Editor
  • Create visibility rules so that OOB Save is hidden during the create mode and the custom Save button is visible.
  • Register a function on the click of the custom ‘Save’ button.
  • Determine the processid the stageid as per the data entered by the user.
  • Set the processid and the stageid using Xrm.Page.getAttribute(“processid”).setValue(processid) and Xrm.Page.getAttribute(“stageid’).setValue(stageid).
  • Now the next trick would be using the method – Xrm.Page.data.save.then(successCallback, errorCallback);
  • successCallback – The method to call after the save is called.
  • errorCallback – The method to call if the save fails.
  • In the successCallback method write the code to refresh the form.
    • Xrm.Utility.openEntityForm(“<entitylogicalname>”, Xrm.Page.data.entity.getId());

So the form would be refreshed and the appropriate business process and stage would be loaded.

Hope this helps!

Show consolidated summation of values in Funnel chart in Dynamics CRM

Yes, I saw exactly the same line in the draft customer requirement sheet. My prompt reply would have been ‘No’ to the customer for this requirement. But as a consultant, we all know the perils of saying a direct ‘No’ to the customer. Smile

To be specific to the requirement, we had a  dashboard showing a funnel chart for opportunities with opportunity estimated value for different stages. What the customer wanted to show a sum of the values for all the opportunities just below the chart. As usual, we tried to propose them so many alternatives but customer was adamant to having the solution the way they wanted.

So here I was scratching my head over the internet trying to find a possible solution, preferably OOB to achieve the same. However to my disappointment I could find anything. So I decided to try it all out myself. So here is the approach I followed to achieve the same.

Before you move ahead and read the solution, one thing to let you all know. The below steps are all unsupported customizations and might not work with a future update for Microsoft CRM.

So if you are reading this line, you are also on the same boat as I am Smile. So without wasting further time, let us jump to the solution straightway.

Step1:

Create a HTML web-resource and place it in a section on the dashboard with the minimum width and height possible so that it does affect the appearance of the dashboard. The purpose of this web-resource is to read the funnel chart when the dashboard loads.

Step 2:

Enter the jscript code needed to read the funnel chart section. Below is the sample of the HTML file and the full javascript code. Please note that you have to refer jQuery.js and XrmServiceToolkit.js file in the code. Both these files are available for download from internet.

<html>
<head>
    <title></title>
    http://../scripts/libraries/jquery.js
    http://../Scripts/XrmServiceToolkit
   
        function attachEventToFrame() {
            setTimeout(“setEvents();”, 3000)
        }

        function setEvents() {
            //  debugger;
            var contentIFrame = window.top.document.getElementById(“contentIFrame0”);
            if (contentIFrame != null) {
                // get the dashboard frame.
                var dashboardFrame = contentIFrame.contentWindow.document.getElementById(“dashboardFrame”);

                if (dashboardFrame != null) {
                    // get all the iFrames under the DashboardFrames.
                    var chartIFrames = dashboardFrame.contentWindow.document.getElementsByTagName(“iframe”);
                    var regEx = /^\w+_vizIframe$/;

                    for (var count = 0; count                         if (regEx.test(chartIFrames[count].id)) {
                            var frame = chartIFrames[count];

                            // get the crmchart element.
                            var crmchart = frame.contentWindow.document.getElementById(“CrmChart”);

                            if (crmchart != null && crmchart.alt == “Chart: Funnel Value by Stage“) {

                                // run a fetchxml query and get the sum of opportunity value
                                var fetch = “” +
                                               “” +
                                                 “opportunityvalue
‘ aggregate=’SUM’ alias= ‘Opportunity_Value’/>” +
                                             “” +
                                             “”;
                                //var value = ”;
                                var FetchedValue = XrmServiceToolkit.Soap.Fetch(fetch);
                                var value = FetchedValue[0].attributes.Opportunity_Value.formattedValue;
                                $(crmchart).parent().append(“
Sum :” + value + ““);
                                $(frame).on(‘load’, iframeloaded);
                            }
                        }
                    }
                }
            }
        }

        function iframeloaded(e) {
            debugger;
            var frame = $(this).get(0);

            var crmchart = frame.contentWindow.document.getElementById(“CrmChart”);
            $span = $(crmchart).parent().find(“#sum_total_custom”);

            // run a fetchxml query and get the sum of opportunity value
           
var fetch = “” +
                           “” +
                             “‘opportunityvalue
aggregate=’SUM’ alias= ‘Opportunity_Value’/>” +
                         “” +
                         “”;

            //var value = ”;
            var FetchedValue = XrmServiceToolkit.Soap.Fetch(fetch);
            var value = FetchedValue[0].attributes.Opportunity_Value.formattedValue;

            if ($span.length > 0) {
                $span.html(“Sum :” + value);
            }
            else {
                $(crmchart).parent().append(“
Sum :” + value + ““);
            }
        }
   
</head>
<body onload="attachEventToFrame();">
</body>
</html>

You can copy and paste the same in your HTML webresource. However please pay attention to the highlighted variables

  • attacheEventToFrame: Function called on body onload
  • opportunityvalue: the schema name of the field on which you want the summation in the opportunity funnel chart
  • fetch: put the same fetchxml xml that is used by the opportunity funnel chart
  • Chart: funnel value by stage: This is basically the title of the chart. If your chart is showing some different title, you should use the same title here.

Now when you publish your dashboard you should see like below.

 

image

 

And when you enlarge it, below is the screenshot.

image

 

The summation here is just a dummy. So adding not necessarily will add up to the total value Smile

P.S – This will work only when you view the chart from the Dashboard. However if you open this chart from the context of a view, it would not work.

Hope this helps!

Role Based Views in Dynamics CRM – New version released

Hi All,

Finally got some time around to work on the bug fixes in the my codeplex tool ‘Role Based Views in Microsoft Dynamics CRM’. The new version has been uploaded in codeplex site- https://rolebasedviews.codeplex.com

Thanks everyone for the huge downloads and making the tool so popular.

What’s fixed in this version

  • Error in the tool for users without security roles
  • Intermittent error in the lookup view of the entities configured for role based views
  • Compatibility with CRM 2015 online update 1

 

Please visit the link https://rolebasedviews.codeplex.com and go to ‘Downloads’ section and download the release – Role Based Views – CRM 2015 Version.  Please download both the managed solution as well as the executable as using the new version of the managed solution with the old executable would give an error.

New to the tool? Please check for the Documentations tab at https://rolebasedviews.codeplex.com to learn how to use this tool for your need.

 

P.S – This version involves some schema changes in the configuration entities used by the tool and installing the latest version on top of the previous versions might not work properly. So if you are already using a version of this tool its best to uninstall the old version and install the new one.

However if there are lot of configurations that you have done already and do not want to loosed the data by uninstalling the solution, here are steps you need to follow. You should be logged in as administrator to perform the below steps.

  • Login to Advanced Find and look for the entity – ‘Role View Configuration’ and select the Active View.

image

  • Once you view the results, couple of changes you need to make.
    • In the column hidden views, you would find the value set as “<View 1> @<View 2>”. You would need to replace the name of the views with their GUIDs like ‘<View Guid 1>@<View Guid 2>
    • A new field called ‘View Id’ (schema name – rb_viewid) has been introduced in the entity. If you have the ‘View Name’ field populated for a row, you need to fill the ‘View Id’ field with the GUID of the ‘View Name’ field value.

 

 

Reach out to me at debajit.prod@gmail.com for any issues.

 

Hope this helps!

Auditing Team Membership table in Dynamics CRM

Before you start reading this, please note that although the title says auditing of team membership table, it strictly does not cover all audit scenarios. However this article will help you to track some stuffs in team membership table that would be of help in some scenarios.

Recently in my project, customer walked up to me and asked if I could find out when some users have been added to a specific team and if the additions were manual or a programmatic approach. Basically customer have team based security model and adding or removing the members of the team impact user’s access to the application. They had some undesired users who were added to a team and wanted to find out the root cause of the same.

Honestly I did not do it in any of my previous CRM implementations, but before saying anything I bought some time from the customer and gave it a shot.

Our’s is a on-premise deployment and hence I had the privilege of the looking up in the TeamMembership table in the SQL. Please note that whatever I mention below is applicable only to on-premise deployments. For online deployments, this stuff won’t work.

First of all, TeamMembership table in CRM is a N:N relationship table. So there would be not created on or modified on columns in the table.

So what else we have.

image

All the fields look familiar in except the VersionNumber column which is a timestamp column in the SQL server. Let us clear any doubt before you actually develop it. Timestamp does not contain the timestamp when the record got created. Rather it tracks a relative time within a database, not an actual time that can be associated with a clock. Please refer to the SQL documentation on timestamp if you need more information on the same.

So it does not store time information. Then how is it useful?

Well it gives you a pattern. I execute the below query and the screenshot shows the result we get.

image

For the demo, I have just queried for an arbitrary Teamid and also type casted the VersionNumber field to BIGINT so that it is in readable format.

Now can you identify some pattern in the entries? If you observe carefully, all the values in the RelativeTime column starting with 44684 seems to be created by the system almost at the same time.

Similarly the ones starting with 450295 are all created in the same time.

Obviously a not a great way to audit. But surely it can help you with something before you go ahead with some custom auditing stuff of the team membership table.

 

Hope this helps!

Using DIFFINDAYS to auto calculate age of a contact – Dynamics CRM 2015 Online Update 1

First of all, from the title this seems trivial isn’t it. After all as CRM consultant we do so many complex stuffs and calculating the age of a contact is just so simple!

However the point I wanted to highlight here is to use the DIFFINDAYS and host of other functions introduced in Business Rules editor and what can be more interesting than demoing it by automatic calculation of age which is so often required.

First things first. Fields like ‘Age’ of a contact or anything which increases year or year should be calculated and how many times I have seen in implementations that this field is set to numeric with free text entry. So please remember that if you have any requirement like this, please mark the field as read-only and it should be calculated based on some other field value.

So we have made the field read-only. If you are thinking wouldn’t it be nice if this field is auto calculated also, then you are on the correct way pal.

So we make the field auto calculated. We can set up this field and make it a calculated field. I have done the same. Please check for the screenshot below.

image

Please note here that I have kept the age field as Decimal because my requirement was to show in fraction of years. However you can keep this whole number also.

Now comes the fun part. I click on edit and the editor opens up. I set the age as show in the screen shot below. Please note that I have created another field called ‘Date of Birth’ based on which the age is calculated.

 

image

The two important functions you can notice here are DiffInDays and Now().

These functions have come up with the 2015 Online update 1 release.

Now you save and close out this field. Now when you enter the Date of Birth, the age calculates automatically.

image

 

So no more marking the field read-only and writing jscript/ plugin for calculating the age. All this is handled automatically once you set the field as calculated in CRM.

 

This is just one example. You can explore a host of other functions introduced and use it to your liking.

image

 

Hope this helps!