Monthly Archives: July 2017

This blog is running on WordPress, and would not it be nice if I were able to somehow integrate WordPress with Dynamics? Well, actually I don’t have my own instance of Dynamics to start with, so it would be nice if Microsoft was offering some sort of development instances in the first place(is Salesforce offering them, still?), but, hypothetically..

Besides, I might not be the only one using WordPress, and, so, if there was this kind of solution, it might be of some interest to others as well.

That’s more or less what I was thinking tonight when I decided to google for this kind of WordPress plugin, and, surprisingly, something came up in the search results: https://alexacrm.com/plugin/

I figured I’d try a quick test? You will see how it worked out below, but here is a quick summary:

• The potential there is great. I was able to set up a form, and I was able to get the data submitted to Dynamics. This plugin can, actually, read form (and view) layouts from Dynamics, even if it does not necessarily keep the formatting
• From what I’ve seen, it’s relatively easy to set up a page that will be capturing form input and store it in Dynamics, but it seems to be more challenging to setup something more complicated(I’m thinking about all those customer support scenarios.. case details, case notes, status updates, etc)

Anyway, here is how it works:

1. You can install the plugin from the Word Press plugin store – the plugin is called “Dynamics 365 Integration”

2. Once it’s been installed and activated, you need to configure the connection

3. Notice that “Regenerate Metadata Cache” button. You can use it whenever you have updated some metadata in Dynamics (including form layouts)

4. So, I got that done, and, then, went to Dynamics to create a test entity

Here is how the form looks like for that test entity

There are a couple of things about this form to keep in mind:

• It seems that the plugin will verify if all required fields have been populated, and, if not, will display an error message when submitting form data. To avoid this, I tried moving those required fields to the header of the form. That allowed me to remove them from the body.. Not sure if this is how it is supposed to be – it was simply an educated guess, but it worked
• Notice that I have a two-column section. The plugin won’t display two columns – it will display all fields from top to bottom. That’s just something I wanted to try and see what happens, though
• Notice the name of that form – I’ll use it below

5. So, now that I have an entity(and a form) in Dynamics, I want to add it to my page

• Remember that Refresh Metadata Cache button? I used it to refresh the metadata at this point
• Then I created a test page and put this code on the page:

   [msdyncrm_form entity="tcs_wordpress" name="WP Request" mode="create" captcha="false" message="Thanks!" hide_form="true"]

6. Here is what showed up when I clicked “preview”

The layout is not exactly the same that I have in Dynamics. But, at least, all the fields are there.

7. I have entered some test data, clicked “submit”.. and here we go – all that data is in Dynamics now:

Well, in a nutshell, it was really a quick and easy setup.

However, I have not tried anything more complex so far – there are two versions of this plugin(premium and free). In the premium version, it seems to be possible to define your own templates, for example.. Which probably means I can set up something more advanced.. I might probably display a service schedule as a view and allow web site visitors to click some links there.. so that they would be presented with a form to fill in. Those templates don’t seem to be available in the free edition, though, and the pricing starts at $45 per month for the basic premium version.

If you did happen to use those advanced features, I’d really like to see how far you were able to take this integration.

We know that that “all interaction with Microsoft Dynamics 365 application pages must only be performed through the methods with the Xrm.Page or Xrm.Utilitynamespaces documented in the Client-side programming reference”. This is a direct quote from msdn:

https://msdn.microsoft.com/en-us/library/gg328350.aspx

However, what if we could extend/override the Xrm itself, and what if we did not have to modify any of the files for that?

The beauty (or the curse) of JavaScript is that there are not too many restrictions in that language, and, yet, it offers quite a few features you won’t easily find in other languages(such as C#).

Do you know about javascript prototypes? I think a seasoned javascript developer would be able to explain this in much more details, but, in my mind, a prototype is an object template. It’s a set of methods you can use on with that object.

For example, in the Xrm library we have form controls(control objects). They are all of different types (date time, grid, text, optionset, etc), but, within each type, they are sharing the same set of methods which you can officially use to work with the controls.

Right now I am interested in these two methods:

• setDisabled
• getDisabled

If I were able to replace those two with my own methods dynamically, I would suddenly have a solution that would extend out of the box functionality in an integrated way. What I mean is.. You may have a javascript in your environment that’s already using setDisabled. You may have a business rule which is locking/unlocking some fields on the form, and such business rules are using setDisabled/getDisabled behind the scene as well. If you wanted to make your own form engine to work with those out of the box features, overriding getDisabled/setDisabled looks like the most straight forward solution.

With JavaScript, we can do it easily:

      
  if(typeof control.constructor.prototype.originalSetDisabled == 'undefined') 
  {
        control.constructor.prototype.originalSetDisabled = control.constructor.prototype.setDisabled;
        control.constructor.prototype.setDisabled = function (disabled) {
           //DO WHAT YOU NEED HERE 
           this.originalSetDisabled(calculatedDisabled)
        };
   }

Now every piece of the out of the box or custom code that’s using setDisabled will be calling your own version of the setDisabled. You’ll do what you want in your own function to see if the control should be locked/unlocked, and, then, you will call the original setDisabled with an updated parameter value to finally lock/unlock the control.

Do the same with setVisible, and, out of a sudden, you can build your own engine which is completely integrated with the Xrm library.

Is it a supported solution? Well, technically it’s not accessing HTML directly, and it’s not doing anything else that would not be allowed. It’s just using JavaScript, and that’s allowed.

Can it break? Not very likely, since setDisabled/getDisabled are two of those methods which are fully supported, and, as long as those two methods are there, this kind of solution will keep working.

And, so, there is probably no need to walk you through the complete script – I’ll post the current version below, and you can also find it in the TCS Tools solution if you install that solution into your CRM organizations. The rest of that script should be very familiar (it’s disabling controls, disabling sections, disabling tabs.. etc):

var TCS = TCS || {};

TCS.FormSupport = {



    EVENT_TYPE: {
        OnFormLoad: 1,
        OnFieldChange: 2
    },

    GetFormConfiguration: null,
    WebResourceLock: null,
    CurrentTick: null,
    InternalDisableOnChange: false,

    FormConfiguration: null,


    Init: function () {
        if (typeof Xrm == "undefined" || Xrm == null || typeof Xrm.Page == "undefined" || Xrm.Page == null) {
            SetTimeout(function () { TCS.FormSupport.Init(); }, 200);
        }
        else {
            TCS.FormSupport.AddPrototypes();
            if (typeof TCS.FormSupport.GetFormConfiguration != undefined && TCS.FormSupport.GetFormConfiguration != null) {
                TCS.FormSupport.FormConfiguration = TCS.FormSupport.GetFormConfiguration();
                TCS.FormSupport.ProcessFormConfiguration(TCS.FormSupport.EVENT_TYPE.OnFormLoad);
            }
        }
    },

    AddPrototypes: function () {
        Xrm.Page.ui.controls.forEach(function (control, index) {

            if (typeof control.constructor.prototype.originalSetDisabled == 'undefined') {
                if (typeof control.getDisabled != 'undefined') {
                    control.originalDisabled = control.getDisabled();
                    control.constructor.prototype.originalSetDisabled = control.constructor.prototype.setDisabled;
                    control.constructor.prototype.setDisabled = function (disabled) {
                        this.originalDisabled = disabled;
                        TCS.FormSupport.CalculateSettings();
                        TCS.FormSupport.LockControlAsNeeded(this);
                    };
                }

                if (typeof control.getVisible != 'undefined') {
                    control.originalVisible = control.getVisible();
                    control.constructor.prototype.originalSetVisible = control.constructor.prototype.setVisible;

                    control.constructor.prototype.setVisible = function (visible) {
                        this.originalVisible = visible;
                        TCS.FormSupport.CalculateSettings();
                        TCS.FormSupport.HideControlAsNeeded(this);
                    }
                }
            }
        });

        /*
        Xrm.Page.ui.tabs.get(0).sections.get(0).constructor.prototype.setDisabled = function (isDisabled) {
            this.controls.forEach(function (control, index) {
                if (typeof control.getDisabled != 'undefined') {
                    control.setDisabled(isDisabled);
                }
            });
        };
        Xrm.Page.ui.tabs.get(0).constructor.prototype.setDisabled = function (isDisabled) {
            this.sections.forEach(function (section, index) {
                section.setDisabled(isDisabled);
            });
        };
*/

    },

    CreateOnChangeHandler: function (action) {
        return function (context) {

            if (TCS.FormSupport.InternalDisableOnChange != true) {
                TCS.FormSupport.InternalDisableOnChange = true;
                if (typeof action.action != 'undefined' && action.action != null) action.action();
                TCS.FormSupport.InternalDisableOnChange = false;
                TCS.FormSupport.ProcessFormConfiguration(TCS.FormSupport.EVENT_TYPE.OnFieldChange);
            }

        };
    },

    HideControlAsNeeded: function (control) {
        if (typeof control.originalSetVisible == 'undefined') return;
        if (typeof control.getAttribute == 'undefined') return;

        var attributeName = control.getAttribute();
        if (attributeName != null) attributeName = attributeName.getName();
        var sectionName = null;
        var tabName = null;

        var section = control.getParent();
        if (section != null) {
            sectionName = section.getName();
            var tab = section.getParent();
            if (tab != null) {
                tabName = tab.getName();
            }
        }

        var controlHidden = control.originalVisible == false;
        if (!controlHidden) {
            for (var index = 0; index < TCS.FormSupport.FormConfiguration.settings.length; index++) { var currentSetting = TCS.FormSupport.FormConfiguration.settings[index]; controlHidden = ( currentSetting.hideFields.indexOf(attributeName) > -1 ||
                     currentSetting.hideSections.indexOf(sectionName) > -1 ||
                     currentSetting.hideTabs.indexOf(tabName) > -1) && currentSetting.conditionMet;
                if (controlHidden) {
                    break;
                }
            }
        }
        if (control.getVisible() != !controlHidden) {
            control.originalSetVisible(!controlHidden);
        }
    },

    LockControlAsNeeded: function (control) {
        if (typeof control.originalSetDisabled == 'undefined') return;
        if (typeof control.getAttribute == 'undefined') return;

        var attributeName = control.getAttribute();
        if (attributeName != null) attributeName = attributeName.getName();
        var sectionName = null;
        var tabName = null;

        var section = control.getParent();
        if (section != null) {
            sectionName = section.getName();
            var tab = section.getParent();
            if (tab != null) {
                tabName = tab.getName();
            }
        }


        var controlLocked = control.originalDisabled == true;
        if (!controlLocked) {
            for (var index = 0; index < TCS.FormSupport.FormConfiguration.settings.length; index++) { var currentSetting = TCS.FormSupport.FormConfiguration.settings[index]; controlLocked = (currentSetting.lockFields.indexOf(attributeName) > -1 ||
                     currentSetting.lockSections.indexOf(sectionName) > -1 ||
                     currentSetting.lockTabs.indexOf(tabName) > -1) && currentSetting.conditionMet;
                if (controlLocked) {
                    break;
                }
            }
        }
        if (control.getDisabled() != controlLocked) {
            control.originalSetDisabled(controlLocked);
        }

        /*if (typeof control.getObject != 'undefined' && TCS.FormSupport.WebResourceLock != null) {
                TCS.FormSupport.WebResourceLock(control, controlLocked);
            }*/
    },

    CalculateSettings: function () {
        for (var index = 0; index < TCS.FormSupport.FormConfiguration.settings.length; index++) {
            var currentSetting = TCS.FormSupport.FormConfiguration.settings[index];
            if (typeof currentSetting.condition == 'undefined' || currentSetting.condition == null) {
                alert('Form Support Script: Condition is not defined for ' + currentSetting.name);
                return;
            }
            currentSetting.conditionMet = currentSetting.condition();
        }
    },

    ProcessFormConfiguration: function (eventType) {
        debugger;
        if (eventType == TCS.FormSupport.EVENT_TYPE.OnFormLoad) {
            if (typeof TCS.FormSupport.FormConfiguration.onLoadAction != 'undefined'
                && TCS.FormSupport.FormConfiguration.onLoadAction != null) {
                TCS.FormSupport.FormConfiguration.onLoadAction();
            }

            for (var index = 0 ; index < TCS.FormSupport.FormConfiguration.onChangeActions.length; index++) {
                var currentAction = TCS.FormSupport.FormConfiguration.onChangeActions[index];
                var attrib = Xrm.Page.getAttribute(currentAction.attributeName);
                if (attrib != null) {
                    attrib.addOnChange(TCS.FormSupport.CreateOnChangeHandler(currentAction));
                    TCS.FormSupport.InternalDisableOnChange = true;
                    if (typeof currentAction.action != 'undefined' && currentAction.action != null && currentAction.action != "") {
                        currentAction.action();
                    }
                    TCS.FormSupport.InternalDisableOnChange = false;
                }
            }
            //TCS.FormSupport.ProcessFormConfiguration(TCS.FormSupport.EVENT_TYPE.OnFieldChange);
        }


        TCS.FormSupport.CalculateSettings();

        /*
        Xrm.Page.ui.tabs.forEach(function (tab, index) {
            TCS.FormSupport.HideTabAsNeeded(tab);
        });
        */


        Xrm.Page.ui.controls.forEach(function (control, index) {
            TCS.FormSupport.LockControlAsNeeded(control);
            TCS.FormSupport.HideControlAsNeeded(control);
        });
    }

};

Before I continue, I just wanted to show you something:

On the left side is what you would have to do if you wanted to hide all the fields in the company profile section on the out of the box Account form. There are only 5 fields there; however, when using a business rule, you have to do lock/unlock, so you have to add two actions per field to the business rule. This kind of business rule will quickly become rather complicated if the number of fields turns 10.. or 15.. or 20.

On the right site is what a javascript solution can do to simplify that business rule. And there is another advantage to it: you don’t even need to update the script once it’s created – in that particular example, it will be operating on the section level, so you can keep adding/removing fields to/from the section, and the script will be picking them up automatically.

I think it’s one of the improvements we will see in the out of the box business rules sooner or later, but, for now.. there are TCS Tools🙂

Here is how it works:

1. Add tcs_/scripts/FormSupport.js web resource to your form

2. Create a web resource for the following script

function GetFormConfiguration() {
    return {
        onLoadAction: function () {},
        onChangeActions: [
        ],
        settings: [
        ]
    };
}
function InitializeFormSupport()
{
  TCS.FormSupport.GetFormConfiguration = GetFormConfiguration;
  TCS.FormSupport.Init();
}

3. Add that new web resource to the form as well

4. Add OnLoad event handler to the form

InitializeFormSupport will be the function name for that event handler

5. Publish all customizations

From here, you can focus on the configuration of the rules – all the preparations have been done.

So, now, you just need to add onChange actions and, also, settings.

Every action has two elements:

• attributeName (required) – it’s the name of the attribute that, when updated, will make the engine to re-apply visibility/disabled rules
• action (can be null) – it’s a function that will be called when an attribute is updated (for example, you can use it to update some other attributes)

Every setting has the following elements:

• name (can be anything)
• condition – it’s a function that will verify some conditions. If the return value is true, the engine will hide or lock the tabs/sections/fields mentioned in the other properties of the setting element
• lockTabs: that’s the list of tabs where all the controls will be locked when the condition evaluates as true (for example.. lockTabs: [“tab1”, “tab2”])
• lockSections: that’s the list of sections where all the controls will be locked (for example.. lockSections: [“section1”, “sections2”])
• lockFields: that’s the list of attributes where all corresponding controls will be locked (for example.. lockFields: [“field1”, “field2”])
• hideTabs: same as lockTabs, but all controls will be hidden
• hideSections: same as lockSections, but all controls will be hidden
• hideFields: same as lockFields, but all controls will be hidden

Here is an example for the company profile rule mentioned at the beginning of this post:

function GetFormConfiguration() {
    return {
        onLoadAction: function () {},
        onChangeActions: [
         {
           attributeName: "tcs_profilelocked",
           action: function () {}
         }
        ],
        settings: [
         {
           name: "Profile Locked",
           condition: function () {
               return Xrm.Page.getAttribute('tcs_profilelocked').getValue() == true;
           },
           lockTabs: [],
           lockSections: ["COMPANY_PROFILE"],
           lockFields: [],
           hideTabs: [],
           hideSections: [],
           hideFields: []
        }
       ]
    };

}

function InitializeFormSupport()
{
   TCS.FormSupport.GetFormConfiguration = GetFormConfiguration;
   TCS.FormSupport.Init();
}

That’s it. If you want, you can add additional elements to the onChangeActions array, and you can add additional elements to the settings array.

I will explain the internal workings of this solution in the next post.. Continue reading

How would you go about setting up readonly/visibility rules for a form that has lots of sections, tabs, and fields? Where the rules can change any time depending on what your users think today? Where one rule can affect another? And where each rule may affect multiple fields, but that set of fields can change from time to time (and, probably, more often than you were told)?

There are two ways we can do it in Dynamics:

• We can set up business rules
• We can create javascripts

Both are legitimate solutions. However, if I don’t like anything about the business rules is that they are too granular. You have to lock fields one after another, you have to define conditions one after another.. Sure you can set everything up, but, from the maintenance standpoint, it is not easy to maintain and modify those rules once the reach some level of complexity.

Javascripts, on the other hand, are like Wild Wild West.. you can do anything, you can probably do it much quicker than with the business rules (if you are comfortable with Javascript), you can change everything quickly. Problem is, it all works for as long as you still remember how it works. I have yet to see a developer who would remember all the details about his/her own code a year from now unless they are looking at that same code daily.

However, what if we could come up with something more structured than JavaScript that’s still more flexible than the business rules?

You’ll see what I ended up with on the screenshot below, and I’ll explain the details of that solution in the subsequent posts; however, here is the summary:

• There is a core script that does not have to be updated. It does all the heavy lifting required to lock/unlock the controls, and, also to hide/show them on the form
• What is still required is a separate javascript web resource that defines form configuration
• In that web resource, I can define those visibility / lock properties per tab, per section, per field
• A field will be locked on the form if there is a condition that locks the field, or if there is a condition that locks the section containing the field, or if there is a condition that locks the tab containing the section containing the field. The same logic applies to the visibility of the controls
• But that’s not all.. This engine works is not conflicting with the business rules/other scripts. You can use a business rule to hide/lock a control. Or you can use another script. If you hide a field that way, it’ll be hidden no matter what. If you lock a field that way, it’ll be locked no matter what
• And, finally, this engine is not using DOM model or any other unsupported feature. However, it is using some javascript features which MAY be considered unsupported (although, it is using those features only to work correctly with the out of the box business rules / scripts). We’ll get to that in the subsequent posts.

So, have a look:

You can try it in your environment:

• Download TCS Tools solution – all the scripts are included there
• Start creating a new TCS Sample entity, but make sure you are using a form called “Form Support” (the other one does not have the scripts attached)

I’ll explain script configuration in more details later, but, for now, here is a simplified example:

     {
        onLoadAction: function () { },
        onChangeActions: [
                {
                    attributeName: "tcs_tabswitch",
                    action: function () {
                        //alert('Tab Switch Clicked');
                    }
                },
                {
                    attributeName: "tcs_sectionswitch",
                    action: null
                }
        ],
        settings: [
            {
                name: "Demo Tab Readonly",
                condition: function () {
                    return Xrm.Page.getAttribute('tcs_tabswitch').getValue() != true;
                },
                lockTabs: ["DemoTab"],
                lockSections: [],
                lockFields: [],

                hideTabs: [],
                hideSections: [],
                hideFields: []
            },
            {
                name: "Demo Tab Visibility",
                condition: function () {
                    return Xrm.Page.getAttribute('tcs_tabswitchvisible').getValue() != true;
                },
                lockTabs: [],
                lockSections: [],
                lockFields: [],

                hideTabs: ["DemoTab"],
                hideSections: [],
                hideFields: []
            }
        ]};

• You can define an onLoadAction – that’s a function that will be called once the form is loaded
• You can define a number of onChangeAction-s – those are per attribute, and they will be called when the associated attribute has changed
• Finally, you can define the settings. Each element of the settings array has a name, a condition, and a few array fields (lockTabs, lockSections, lockFields, hideTabs, hideSections, hideFields). Those fields identify form attributes/sections/tabs that will be locked and/or hidden whenever the condition is met. Note that everything is done by name (attribute name, section name, tab name)

For example, if you wanted to lock all controls in a particular section whenever there is a value in the “new_name” attribute, your settings element might look like this:

             {
                name: "Lock That Section",
                condition: function () {
                    return Xrm.Page.getAttribute('new_name').getValue() == null;
                },
                lockTabs: [],
                lockSections: ["ThatParticularSection"],
                lockFields: [],

                hideTabs: [],
                hideSections: [],
                hideFields: []
            }

In the next post, I’ll explain how to add those scripts to your form, and I’ll also walk you through the details of the core script (although, that may qualify for yet another post).. Continue to part 2

To start with, I’m not a professional front-end developer. I will do something every now and then, but for the most part, I’ll be configuring Dynamics, writing plugins, talking to the users.. you get the picture – it’s called Dynamics consulting.

However, we all need to be somewhat up to date on the front-end development side, at least so we were able to tell what’s doable and what’s not.

Those were all the reasons I had when I decided to try Angular with Dynamics. What I wanted to do looked very simply – I wanted to display this “Hello World” Angular sample in an iframe in Dynamics:

https://hello-angularjs.appspot.com/helloworld

As usual with Dynamics – this did not go as smoothly as I expected, but, to be fair, it could have gone worse:)

Here is the html code you will need if you wanted to try the same (it’s, basically, the same sample code from that helloworld page.. Though they don’t have the template specified correctly there as I’m writing this post, so there is a small change.. other than that, it’s all the same):

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html ng-app="helloApp">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Hello AngularJS - Hello World</title>
<link rel="stylesheet"
	href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
<script
	src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script
	src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
<script
	src="//ajax.googleapis.com/ajax/libs/angularjs/1.2.17/angular.min.js"></script>

<script>
	var helloApp = angular.module("helloApp", []);
	helloApp.controller("HelloCtrl", function($scope) {
		$scope.name = "Calvin Hobbes";
	});
</script>

</head>
<body ng-controller="HelloCtrl">
	<header class="navbar navbar-static-top" id="top" role="banner">
	<div class="container">
		<div class="navbar-header">
			<button class="navbar-toggle" type="button" data-toggle="collapse"
				data-target=".bs-navbar-collapse">
				<span class="sr-only">Toggle navigation</span> <span
					class="icon-bar"></span> <span class="icon-bar"></span> <span
					class="icon-bar"></span>
			</button>
			<a href="/" class="navbar-brand">Hello-AngularJS</a>
		</div>		
	</div>
	</header>
	<div class="container">
		<div class="page-header" style="margin: 0">
			<h1>Hello World</h1>
		</div>
		<div style="padding-top: 15px">This example demonstrate the code
			sample for Hello World program. Type your name in the text field
			below</div>
		<div style="padding-top: 30px">
			<div style="padding: 0px 0px 20px 30px">
				<h4>Hello {{name}}! How are you doing today</h4></div>
			<form class="form-horizontal" role="form" method="post" action="#">
				<div class="form-group">
					<label class="col-md-1 control-label">Name</label>
					<div class="col-md-3">
						<input type="text" class="form-control" name="firstname"
							ng-model="name">
					</div>
				</div>
			</form>
		</div>
		<div class="panel panel-default">
			<div class="panel-heading">
				<h3 class="panel-title">Notes</h3>
			</div>
			<div class="panel-body">Pay attention to ng-model="name" and the template Calvin Hobbes</div>
		</div>		
	</div>


</body>
</html>	

Here is the problem that I ran into, though. Notice this part at the top of the html:
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”>
<html ng-app=”helloApp”>

If you create an html web resource in Dynamics and choose to use provided text editor to paste this whole HTML into the web resource, the only part that will be left of those two lines is this:

<HTML>

Yes, just like that. Everything else will be stripped of. I mentioned I’m not a front-end developer, so I don’t develop full-blown html web resources too often, and, even when I do, I don’t do it with Angular (up until now). So it took me a little while to recall this interesting bug/feature of the web resource text editor that comes with Dynamics.

The workaround is simple – just load that html as a file into your web resource. Do not EVER open the text editor:

Once that was done, it all just started to work:

What would you choose if you were given an option to use SQL or FetchXml to run a query? I’m sure that 99.9% of us would go with SQL in that situation since FetchXml tends to limit what we can do.

However, FetchXml is, actually, more powerful than you would think if you simply continued to use “Download FetchXml” option in the advanced find.

For example, there are two limitations we have in the advanced find:

• If we had am account and two contacts, and if we used Advanced Find to look for the accounts having those contact, we would get only one row for that account in the list. Even though, in SQL we would be able to get two rows – one per each valid combination. With the Advanced Find, we are only getting “unique” records
• We cannot use outer joins(which would be especially useful if we could define “not exists” conditions), and we cannot join in the same related entity more than once

However, both of those are exclusively Advanced Find limitations. FetchXml is perfectly capable of handling either of those.

Consider the following query:

Have you ever noticed that distinct=”true” attribute gets added to the FetchXml when you have a linked entity added to the query? For example, for the query displayed on the screenshot above, here is how downloaded FetchXml looks like:

That’s exactly why Dynamics will be displaying one row per account in the results. Even if there are multiple contacts linked to that account through the parentcustomerid field. Replace that part of FetchXml with distinct=”false”, and you’ll get multiple rows. Of course you can’t do it in the Advanced Find, but.. you can use XrmToolBox to update a saved view, or you can do it in the report. Point being, it’s not a FetchXml limitation at all.

And what about those outer joins?

For the same FetchXml above, I can re-write it like this:

Notice how I’ve added link-type=”outer” to the link-entity. And, also, notice how I moved the filter from the link-entity section up to the main entity.

The first change turned this whole query into an outer join. If I did not move the filter, that would produce a list of all accounts then (an outer join does not require a related record to be there).

If I removed the filter completely, I would still be getting all accounts.

However, since I moved the filter, and since I’m using entityname in the condition now, I can apply that condition to the entity joined through the “outer” join. Which basically turns the whole thing into the same original query – I will only see the accounts which have contacts with the fullname like “%Test%”.

That’s not a lot of progress so far, but wait.. here is what I can do next:

I can keep adding those outer joins and related conditions. This time, my FetchXml will return all accounts where there is a contact with the fullname like “%Test$” linked to that account OR if that account has a primary contact with the fullname like “%Main%”.

I can add more conditions. I can add linked entities to the linked entities (and still use filter conditions). For example, I might add opportunities to that fetch under the primary contacts and add a filter for the opportunity title. That would add accounts with the primary contacts having linked opportunities with such titles (and it would still be “OR” for all those conditions, not “AND” – you can do “AND” out of the box)

Interesting, isn’t it?

This last feature of FetchXml, in combination with RetrieveMultiple plugins, can help us achieve some interesting results:

Quick Find on Your Terms

What’s all that about doing Quick Find on your terms?

Think of it this way.. When you are doing a quick find on Dynamics terms, it goes like this:

You: “Well, Dynamics, I want to use Quick Find to find all the contacts where this particular keyword occurs in the FullName, or if this contact belongs to an account which has this keyword in the account name, or if the parent account for that account has this keyword in the name.. or when there is an opportunity linked to this contact that has this keyword in the subject.. got the idea?”

Dynamics: “Hah.. I can certainly do that part with the FullName or Email.. maybe with the contact’s parent account.. but, come on.. Who do you think I am to go into all those other complexities with the account’s parent and/or the opportunity?”

And, so, you are not, really, able to do the Quick Find on the related entities.

Now, would not it be nice if you got this response from Dynamics instead?

Dynamics: “Sure thing! Here is the list!”

This is what it’s all about – we want to find what we need to find, not what the system is able to find. And you can easily achieve this if you install TCS Tools for Dynamics and use it to configure the Quick Find.

Let’s start with the end result. I want to find all contacts which have the keyword “night” in either the full name, or in the linked opportunity title, or in the name of the account that is a parent account for the contact’s account (sounds complicated.. well, it’s really just a grand-parent account for the contact).

Here is one of those contacts:

Here is the second one:

And here is the third one – there are two screenshots this time.. You can see how this contact is in the “Adventure Works” account, and that account has a parent account which has “night” in the title:

So, when I do a quick search for “*night”, I want to see all 3 contacts. Out of the box, here is what I’ll see:

Dynamics will search for that keyword in the contacts, and it will also search for that keyword in the parent account names, so I will, actually, see one of the contacts (Summer Knight). BUT I won’t see the other two (Adrian Dumitrascu and Rene Valdes are missing from the list).

And here is what I’ll get once I start using Quick Find on the TCS terms:

I got Rene there, I got Adrian.. and, somehow, I also got Ryan Gregg. But hey, that’s because Adrian has the same parent account as Adrian (which is Adventure Works.. and that account has a parent account which has “night” keyword in the title!). Yeay, it’s working!

How? That’s coming in the next post (hopefully, tomorrow), but, in the meantime, feel free to download and install TCS Tools solution for Dynamics 365