Bookmarklet: Advanced Find Outer Join a.k.a Not-In Condition

I had discussed a technique earlier (https://dreamingincrm.com/2015/05/31/performing-outer-join-in-advanced-find/) on how to use a plugin to intercept the RetrieveMultiple message and modify the fetchxml to facilitate outer join. The guys at Cobalt also have developed a full-fledged solution using the same technique called Intelligent Query

If you don’t want to use a plugin, because you are worried about performance or cannot install either of these solutions into an environment because of organisation policies, you can use this bookmarklet to do the outer join.

Code

var contentPanel = $('#crmContentPanel > iframe');
if (contentPanel && contentPanel.length > 0) {
	var targetFrame = contentPanel[0].contentWindow;
	targetFrame.ExecuteQuery();
	var xml = targetFrame.$get("FetchXml").value;
	var isFetchModified = false;

	var xmlDoc = $.parseXML(xml),
	$xml = $(xmlDoc),
	outer = $xml.find("condition[operator=null]");

	var mainFilter = $xml.has('entity > filter');

	if (mainFilter.length === 0) $xml.find('entity').append('<filter></filter>');
	
	$xml.find('link-entity').each(function (i, d) {
		var entity = $(d).attr('name');
		var entityAlias = $(d).attr('alias');
		var outerJoinCondition = $(d).find("condition[operator=null]");
		if (entity + 'id' === outerJoinCondition.attr('attribute')) {
			$(d).attr('link-type', 'outer');
			$xml.find('entity > filter').append('<condition entityname="' + entityAlias + '" attribute="' + entity + 'id" operator="null" />');
			if (!isFetchModified) isFetchModified = true;
		}
	});
	if (isFetchModified) {
		outer.remove();
		targetFrame.$get("FetchXml", $get("resultRender")).value = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">' + xmlDoc.documentElement.innerHTML + '</fetch>';
	}
	targetFrame.changeArea(targetFrame.ResultsPage);
	targetFrame.$get('resultRender').submit();
} else {
	alert('Cannot locate Advanced Find Frame');
}

Bookmarklet

Select the script below and drag it into your bookmarks bar into Chrome or IE.

javascript:var contentPanel=$('#crmContentPanel > iframe');if(contentPanel&&contentPanel.length>0){var targetFrame=contentPanel[0].contentWindow;targetFrame.ExecuteQuery();var xml=targetFrame.$get("FetchXml").value;var isFetchModified=false;var xmlDoc=$.parseXML(xml),$xml=$(xmlDoc),outer=$xml.find("condition[operator=null]");var mainFilter=$xml.has('entity > filter');if(mainFilter.length===0){$xml.find('entity').append('<filter></filter>');}
$xml.find('link-entity').each(function(i,d){var entity=$(d).attr('name');var entityAlias=$(d).attr('alias');var outerJoinCondition=$(d).find("condition[operator=null]");if(entity+'id'===outerJoinCondition.attr('attribute')){$(d).attr('link-type','outer');$xml.find('entity > filter').append('<condition entityname="'+entityAlias+'" attribute="'+entity+'id" operator="null" />');if(!isFetchModified)
isFetchModified=true;}});if(isFetchModified){outer.remove();targetFrame.$get("FetchXml",$get("resultRender")).value='<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">'+xmlDoc.documentElement.innerHTML+'</fetch>';}
targetFrame.changeArea(targetFrame.ResultsPage);targetFrame.$get('resultRender').submit();}else{alert('Cannot locate Advanced Find Frame');} void 0;

How to use this

The condition to indicate outer join is same as with the previous post i.e. you would specify the primary key of a link entity to be null, to indicate that you would like this link to be a outer join. In the screenshot below you are trying to find organisations without any contacts. When the script sees a link entity primary key, in this case contactid, set to null, it modifies this link as outer and places the appropriate filter criteria in the parent entity, in this case organisation. You basically have to build your conditions in the standard Advanced Find UI and execute the bookmarklet.

Outer

How it works

When you execute the Advanced Find, fetchxml and layoutxml is stored in the DOM. This script modifies this attribute and executes the Advanced Find again with the outer join bits added.

Open Issues

  • Even though the underlying fetchxml has been manipulated, the condition builder UI is not updated to reflect this.
  • Script error after the result page is rendered (Ignore this, as it doesn’t affect the results)

I have tested this only in CRMOnline with Firefox and Chrome.

EDIT (05/11/15): This bookmarklet does not work for Marketing List – specifically from Contact to Marketing List. e.g. You want to know the contacts who are not in a marketing list or any marketing list.

Advertisements

Quicktip: Always retrieve primary key with distinct=’true’ fetchxml

FetchXml is great when you quickly want to extract some data. Today, I found a weird behaviour, that is either a bug, or a documented “feature” that I am not aware of.

If you run this code below

            var fetch = @"
            <fetch version='1.0' output-format='xml-platform' mapping='logical' {0} count='1'>
	            <entity name='contact'>
		            <attribute name='fullname'/>
	            </entity>
            </fetch>";

            Console.WriteLine("With Distinct True And Without Id\n--------------------------------");
            var results = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(string.Format(fetch, "distinct='true'"))).Entities.ToList();
            results.ForEach(x => Console.WriteLine("Id: {0}",x.Id));

            Console.WriteLine("\nWith Distinct True And With Id Field\n--------------------------------");
            results = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(
            @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true' count='1'>
	            <entity name='contact'>
                    <attribute name='contactid'/>
		            <attribute name='fullname'/>
	            </entity>
            </fetch>")).Entities.ToList();
            results.ForEach(x => Console.WriteLine("Id: {0}", x.Id));

            Console.WriteLine("\nWith Distinct False\n--------------------------------");
            results = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(string.Format(fetch, "distinct='false'"))).Entities.ToList();
            results.ForEach(x => Console.WriteLine("Id: {0}", x.Id));

            Console.WriteLine("\nWithout Distinct\n--------------------------------");
            results = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(string.Format(fetch, string.Empty))).Entities.ToList();
            results.ForEach(x => Console.WriteLine("Id: {0}", x.Id));

You get this output,

Output

As you can see, if you put distinct=true in your fetchxml, without retrieving the primary key, and iterate through the RetrieveMultiple result, the Id property will be Guid.Empty for all the entities in the EntityCollection.

Using Advanced Find FetchXml capability in custom forms

I prefer FetchXml compared to QueryExpression or LINQ when writing custom code. Back during the CRM3/4 days there was Stunnware. CRM2011 introduced capability to export the FetchXml directly from the Advanced Find. In CRM 2013/2015 you have four tools, to write and export the FetchXml.

  1. FetchXml Tester (comes with XRMToolBox)
  2. FetchXml Builder
  3. Fetch Tester 3000
  4. CRM DevTools (Chrome only)

The fetchxml I build, is used mostly in a plugin or workflow assembly, but there are times when I want to store the fetchxml in custom entity, and use it a part of scheduled workflow logic. This also needs to be flexible, as I may need to change the fetchxml, without needing to rebuild any assemblies. The obvious and most straight forward way to do this would be to have a textarea field in the form, and copy-paste the fetchxml that was generated from one of this tools, or from the advanced find.

What if there is an better, albeit unsupported way? You can embed the advanced find on the entity form that needs to store the fetchxml, and get the fetchxml from the advanced find, when the user saves the record.

Step 1:
You need two controls: a text area to store the fetchxml and an IFrame that will display the advanced find. The target of the IFrame is “about: blank”. We will set the correct URL using Javascript. I have added the IFrame to a seperate tab, and the default state of this tab is collapsed.

Step 2:
Add the script below as a Javascript webresource and hookup the onSave and onLoad functions to the form save and form load respectively. The id of my IFrame is IFrame_Advanced and the fetchxml is stored in textarea ryr_fetchxml.

var RYR = window.RYR || {};

RYR.onLoad = function(){
  Xrm.Page.getControl('IFRAME_AdvancedFind').setSrc(Xrm.Page.context.getClientUrl()+'/advancedfind/advfind.aspx?pagemode=iframe&amp;navbar=off&amp;cmdbar=false');
};

RYR.onSave = function(){
 var advancedFindFrame = document.getElementById('IFRAME_AdvancedFind').contentWindow;
 advancedFindFrame.ExecuteQuery();
 advancedFindFrame.ShowQuery(); 
 Xrm.Page.getAttribute('ryr_fetchxml').setValue(advancedFindFrame.document.getElementById('FetchXml').value);
};

window.RYR = RYR;

Here is how it looks.

The trick is to call ShowQuery straight after ExecuteQuery, so that div with id FetchXml contains the correct value. If we don’t call the ExecuteQuery, the value of FetchXml won’t be updated when the advanced find is modified by the user.

Once again, this technique is unsupported, so use it at your own risk. I have tested this in Firefox 34 and CRM 2013 6.1.0.581.

UPDATE (24/12/14): There was an issue with the initial script, as it was only working if the organisation was a default one. The initial script split the screen horizontally, if the IFrame url was correctly set using getClientUrl. The updated script fixes this issue. Based on my testing, this is working in Firefox 34, Chrome 39.0.2171.95 and IE 11(in IE9 mode only). One more thing to note -> If the tab with the Advanced Find IFrame doesn’t started out collapsed, the Ribbon Interface of the Advanced Find shows up on the form.

Calculated fields using Plugin

Calculated fields will eventually make its way to Dynamics CRM. In the mean time, you can use ISV products like north52 Formula Manager, to achieve this functionality. If you are a developer and would like to implement a light weight version of calculated fields, this can be done using Plugins.

First we need a configuration entity to store the fetchxml that will be used for the calculation and the entity/attribute details that will use this on the Retrieve message.

In this case, the account field exp_invoicetotal will use the fetchxml result’s totalworthofinvoices. This is an aliased field. Note that we use {0}, that will be replaced by the primary key of the entity that the plugin executes on.

Here is how the plugin is registered in the Plugin Registration tool. It executes on Retrieve of account.

Now the sourcecode for the actual plugin.
using CalculateOnRetrievePlugin.Entities;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;

namespace Experiments.Plugins
{
    public class CalculateOnRetrievePlugin : Plugin
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="CalculateOnRetrievePlugin"/> class.
        /// </summary>
        public CalculateOnRetrievePlugin()
            : base(typeof(CalculateOnRetrievePlugin))
        {
            //Third Param is primary entity name
            base.RegisteredEvents.Add(new Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>(PipelinePhase.PostOperation, Message.Retrieve, "account", ExecuteCalculateOnRetrievePlugin));

            // Note : you can register for more events here if this plugin is not specific to an individual entity and message combination.
            // You may also need to update your RegisterFile.crmregister plug-in registration file to reflect any change.
        }

        /// <summary>
        /// Executes the plug-in.
        /// </summary>
        /// <param name="localContext">The <see cref="LocalPluginContext"/> which contains the
        /// <see cref="IPluginExecutionContext"/>,
        /// <see cref="IOrganizationService"/>
        /// and <see cref="ITracingService"/>
        /// </param>
        /// <remarks>
        /// For improved performance, Microsoft Dynamics CRM caches plug-in instances.
        /// The plug-in's Execute method should be written to be stateless as the constructor
        /// is not called for every invocation of the plug-in. Also, multiple system threads
        /// could execute the plug-in at the same time. All per invocation state information
        /// is stored in the context. This means that you should not use global variables in plug-ins.
        /// </remarks>
        protected void ExecuteCalculateOnRetrievePlugin(LocalPluginContext localContext)
        {
            var targetEntity = (Entity)localContext.PluginExecutionContext.OutputParameters["BusinessEntity"];

            var queryConfiguration = new QueryExpression(ExpCalculatedfieldconfiguration.Fields.SchemaName)
                {
                    ColumnSet = new ColumnSet(
                        ExpCalculatedfieldconfiguration.Fields.ExpName,
                        ExpCalculatedfieldconfiguration.Fields.ExpResultField,
                        ExpCalculatedfieldconfiguration.Fields.ExpQuery,
                        ExpCalculatedfieldconfiguration.Fields.ExpFieldName,
                        ExpCalculatedfieldconfiguration.Fields.ExpIsAliasedField)
                };
            queryConfiguration.Criteria.AddCondition(new ConditionExpression(
                ExpCalculatedfieldconfiguration.Fields.ExpName, ConditionOperator.Equal,
                localContext.PluginExecutionContext.PrimaryEntityName));
            queryConfiguration.Criteria.AddCondition(new ConditionExpression(
                ExpCalculatedfieldconfiguration.Fields.Statecode, ConditionOperator.Equal, 0));
            var calcConfigRecords = localContext.OrganizationService.RetrieveMultiple(queryConfiguration).Entities;

            foreach (var calcConfigRecord in calcConfigRecords)
            {
                var isAliased = (bool)calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpIsAliasedField];
                var calculatedQueryResults = localContext.OrganizationService.RetrieveMultiple(
                    new FetchExpression(
                        string.Format(calcConfigRecord[
                        ExpCalculatedfieldconfiguration.Fields.ExpQuery].ToString(),
                        localContext.PluginExecutionContext.PrimaryEntityId)
                        )).Entities;

                foreach (var calculatedQueryResult in calculatedQueryResults)
                {
                    var targetFieldName =
                        calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpFieldName].ToString();
                    var resultFieldName =
                        calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpResultField].ToString();
                    var fieldValue = calculatedQueryResult[resultFieldName] as AliasedValue;
                    if (fieldValue != null)
                    {
                        targetEntity[targetFieldName] = ExtractString(fieldValue.Value);
                    }
                    else
                    {
                        targetEntity[targetFieldName] = 
                            ExtractString(calculatedQueryResult[resultFieldName]);
                    }
                }
            }
        }

        private string ExtractString(object attributeValue)
        {
            var attributeType = attributeValue.GetType(); 
            var result = string.Empty;
            if (attributeType == typeof (EntityReference))
            {
                result = ((EntityReference) attributeValue).Name;
            }
            else if (attributeType == typeof(OptionSetValue))
            {
                result = ((OptionSetValue)attributeValue).Value.ToString();
            }
            else if (attributeType == typeof(Money))
            {
                result = ((Money)attributeValue).Value.ToString("C");
            }
            else
            {
                return attributeValue.ToString();
            }
            return result;
        }
    }
}

Here is the Plugin base class.

using ExceptionMgmtServices;

namespace Experiments.Plugins
{
    using System;
    using System.Collections.ObjectModel;
    using System.Globalization;
    using System.Linq;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;

    /// <summary>
    /// Base class for all Plugins.
    /// </summary>    
    public class Plugin : IPlugin
    {
        protected class LocalPluginContext
        {
            internal IServiceProvider ServiceProvider
            {
                get;

                private set;
            }

            internal IOrganizationService OrganizationService
            {
                get;

                private set;
            }

            internal IPluginExecutionContext PluginExecutionContext
            {
                get;

                private set;
            }

            internal ITracingService TracingService
            {
                get;

                private set;
            }

            private LocalPluginContext()
            {
            }

            internal LocalPluginContext(IServiceProvider serviceProvider)
            {
                if (serviceProvider == null)
                {
                    throw new ArgumentNullException("serviceProvider");
                }

                // Obtain the execution context service from the service provider.
                this.PluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

                // Obtain the tracing service from the service provider.
                this.TracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

                // Obtain the Organization Service factory service from the service provider
                IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

                // Use the factory to generate the Organization Service.
                this.OrganizationService = factory.CreateOrganizationService(this.PluginExecutionContext.UserId);
            }

            internal void Trace(string message)
            {
                if (string.IsNullOrWhiteSpace(message) || this.TracingService == null)
                {
                    return;
                }

                if (this.PluginExecutionContext == null)
                {
                    this.TracingService.Trace(message);
                }
                else
                {
                    this.TracingService.Trace(
                        "{0}, Correlation Id: {1}, Initiating User: {2}",
                        message,
                        this.PluginExecutionContext.CorrelationId,
                        this.PluginExecutionContext.InitiatingUserId);
                }
            }
        }

        private Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>> registeredEvents;

        /// <summary>
        /// Gets the List of events that the plug-in should fire for. Each List
        /// Item is a <see cref="System.Tuple"/> containing the Pipeline Stage, Message and (optionally) the Primary Entity. 
        /// In addition, the fourth parameter provide the delegate to invoke on a matching registration.
        /// </summary>
        protected Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>> RegisteredEvents
        {
            get
            {
                if (this.registeredEvents == null)
                {
                    this.registeredEvents = new Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>>();
                }

                return this.registeredEvents;
            }
        }

        /// <summary>
        /// Gets or sets the name of the child class.
        /// </summary>
        /// <value>The name of the child class.</value>
        protected string ChildClassName
        {
            get;

            private set;
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="Plugin"/> class.
        /// </summary>
        /// <param name="childClassName">The <see cref=" cred="Type"/> of the derived class.</param>
        internal Plugin(Type childClassName)
        {
            this.ChildClassName = childClassName.ToString();
        }

        /// <summary>
        /// Executes the plug-in.
        /// </summary>
        /// <param name="serviceProvider">The service provider.</param>
        /// <remarks>
        /// For improved performance, Microsoft Dynamics CRM caches plug-in instances. 
        /// The plug-in's Execute method should be written to be stateless as the constructor 
        /// is not called for every invocation of the plug-in. Also, multiple system threads 
        /// could execute the plug-in at the same time. All per invocation state information 
        /// is stored in the context. This means that you should not use global variables in plug-ins.
        /// </remarks>
        public void Execute(IServiceProvider serviceProvider)
        {
            if (serviceProvider == null)
            {
                throw new ArgumentNullException("serviceProvider");
            }

            // Construct the Local plug-in context.
            LocalPluginContext localcontext = new LocalPluginContext(serviceProvider);

            localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Entered {0}.Execute()", this.ChildClassName));

            try
            {
                // Iterate over all of the expected registered events to ensure that the plugin
                // has been invoked by an expected event
                // For any given plug-in event at an instance in time, we would expect at most 1 result to match.
                Action<LocalPluginContext> entityAction =
                    (from a in this.RegisteredEvents
                     where (
                     (int)a.Item1 == localcontext.PluginExecutionContext.Stage &amp;&amp;
                     a.Item2.ToString() == localcontext.PluginExecutionContext.MessageName &amp;&amp;
                     (string.IsNullOrWhiteSpace(a.Item3) || a.Item3 == localcontext.PluginExecutionContext.PrimaryEntityName)
                     )
                     select a.Item4).FirstOrDefault();

                if (entityAction != null)
                {
                    localcontext.Trace(string.Format(
                        CultureInfo.InvariantCulture,
                        "{0} is firing for Entity: {1}, Message: {2}",
                        this.ChildClassName,
                        localcontext.PluginExecutionContext.PrimaryEntityName,
                        localcontext.PluginExecutionContext.MessageName));

                    entityAction.Invoke(localcontext);

                    // now exit - if the derived plug-in has incorrectly registered overlapping event registrations,
                    // guard against multiple executions.
                    return;
                }
            }
            catch (Exception e)
            {
                localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Exception: {0}", e.ToString()));
                //string friendlyMessage = ExceptionManagement.LogException(e, PriorityLevel.HIGH, localcontext.OrganizationService);
                // Handle the exception.
                //throw new Exception(friendlyMessage);
                throw;
            }
            finally
            {
                localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Exiting {0}.Execute()", this.ChildClassName));
            }
        }

        public enum PipelinePhase
        {
            PreValidation = 10,
            PreOperation = 20,
            MainOperation = 30,
            PostOperation = 40
        }

        public enum Message
        {
            AddListMembers,
            AddMember,
            AddMembers,
            AddPrivileges,
            AddProductToKit,
            AddRecurrence,
            AddToQueue,
            AddUserToRecordTeam,
            Assign,
            AssignUserRoles,
            Associate,
            BackgroundSend,
            Book,
            Cancel,
            CheckIncoming,
            CheckPromote,
            Clone,
            Close,
            CopyDynamicListToStatic,
            CopySystemForm,
            Create,
            CreateException,
            CreateInstance,
            Delete,
            DeleteOpenInstances,
            DeliverIncoming,
            DeliverPromote,
            DetachFromQueue,
            Disassociate,
            Execute,
            ExecuteById,
            Export,
            ExportAll,
            ExportCompressed,
            ExportCompressedAll,
            GrantAccess,
            Handle,
            Import,
            ImportAll,
            ImportCompressedAll,
            ImportCompressedWithProgress,
            ImportWithProgress,
            LockInvoicePricing,
            LockSalesOrderPricing,
            Lose,
            Merge,
            ModifyAccess,
            Publish,
            PublishAll,
            QualifyLead,
            Recalculate,
            RemoveItem,
            RemoveMember,
            RemoveMembers,
            RemovePrivilege,
            RemoveProductFromKit,
            RemoveRelated,
            RemoveUserFromRecordTeam,
            RemoveUserRoles,
            ReplacePrivileges,
            Reschedule,
            Retrieve,
            RetrieveExchangeRate,
            RetrieveFilteredForms,
            RetrieveMultiple,
            RetrievePersonalWall,
            RetrievePrincipalAccess,
            RetrieveRecordWall,
            RetrieveSharedPrincipalsAndAccess,
            RetrieveUnpublished,
            RetrieveUnpublishedMultiple,
            RevokeAccess,
            Route,
            Send,
            SendFromTemplate,
            SetRelated,
            SetState,
            SetStateDynamicEntity,
            TriggerServiceEndpointCheck,
            UnlockInvoicePricing,
            UnlockSalesOrderPricing,
            Update,
            ValidateRecurrenceRule,
            Win
        }
    }
}

I have used Gayan Perera’s CRM Code Generator to generate the entity classes using the CSharp.tt template, which I have used in the plugin. This plugin inserts the result of the fetchxml in the calculated field configuration entity, as an attribute in the BusinessEntity, which is accessible from the OutputParameter in the plugin. BusinessEntity is one of the output parameters in the Retrieve response.

Here is the how the entity form looks with the calculated field, which I have made as readonly.

Number of cases, Number of Contacts and Invoice Total have all been calculated by the plugin using the configuration entity and hence can be viewed when the user opens the account form. Since this plugin is registered only on the Retrieve message this will not work when you do a RetrieveMultiple or execute a Saved Query or User Query.

If additional calculated fields are required on a different entity, a new plugin step has to be registered for the retrieve message of that entity. A new calculated field configuration record also has to be created for this entity.

The things I like about this approach is that it is very easily to manipulate the fetchxml and display the result in a field and I find it very useful for aggregating the child records to the parent. I have done this a quick proof of concept, to visualise the idea.