Null Conditional Operator in C# 6

During my code session in the weekend, I discovered the Null Conditional operator in C# 6. In terms of CRM custom development, this operator reduces the risk of null reference exception. Let me give you an example.

Before

                var contactFetchQuery = @"
                <fetch version=""1.0"" output-format=""xml-platform"" mapping=""logical"" distinct=""true"">
                  <entity name=""contact"">
                    <attribute name=""contactid""/>
                    <attribute name=""birthdate""/>
                    <attribute name=""fullname""/>
                      <filter type=""and"">
		                <condition attribute=""statecode"" operator=""eq"" value=""0"" />
                      </filter>
                  </entity>
                </fetch>";
                var contactRecords = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(contactFetchQuery)).Entities;
                foreach (var contactRecord in contactRecords)
                {
                    var birthDate = contactRecord.GetAttributeValue<DateTime?>("birthdate");
                    if (birthDate.HasValue)
                    {
                        if (birthDate.Value.ToLocalTime().Day == DateTime.Today.Day && birthDate.Value.ToLocalTime().Month == DateTime.Today.Month)
                        {
                            Console.WriteLine("{0} was born today..", contactRecord.GetAttributeValue<string>("fullname"));
                        }
                    }
                }

After

                var contactFetchQuery = @"
                <fetch version=""1.0"" output-format=""xml-platform"" mapping=""logical"" distinct=""true"">
                  <entity name=""contact"">
                    <attribute name=""contactid""/>
                    <attribute name=""birthdate""/>
                    <attribute name=""fullname""/>
                      <filter type=""and"">
		                <condition attribute=""statecode"" operator=""eq"" value=""0"" />
                      </filter>
                  </entity>
                </fetch>";
                var contactRecords = crmSvc.OrganizationServiceProxy.RetrieveMultiple(new FetchExpression(contactFetchQuery)).Entities;
                foreach (var contactRecord in contactRecords)
                {
                    var birthDate = contactRecord.GetAttributeValue<DateTime?>("birthdate");
                    if (birthDate?.ToLocalTime().Day == DateTime.Today.Day && birthDate?.ToLocalTime().Month == DateTime.Today.Month)
                    {
                        Console.WriteLine("{0} was born today..", contactRecord.GetAttributeValue<string>("fullname"));
                    }
                }

This makes null checks almost redundant, as the part after the “?.” would not be even evaluated, if the part before “?.” is null. I compiled a project using .net 4.5.2 and VS2015 for the XrmToolBox project and I like using this new functionality. I will definitely use this in future applications.

Refer https://msdn.microsoft.com/en-us/library/dn986595.aspx for more information.

XrmToolBox Tool: Entity Image Updater

Around middle of last week, I published v1.0 of a new XrmToolBox tool called Entity Image updater. I created this tool so that it is easier to update the images on the crm record, if that entity has an image field.

When an entity has an image field and no image has been uploaded, it displays the default person image next to the primary name label.

BeforeImage

You can upload an image, by clicking the person image and choosing a local file.

UploadImageOnce the image is upload, the form looks much nicer IMHO.

AfterImageEven though this capability exists, not many people seem to use this because:

  1. You need source the images
  2. Manually upload them one by one OR write code to upload them based on some conditions

There are so many APIs available to retrieve this data. The most useful of these APIs for this task are

  1. Clearbit – Retrieve logo based on an organisation URL
  2. Twitter – Retrieve logo based on a Twitter handle
  3. Gravatar – Retrieve logo based on a person’s email. This works only if this person has a Gravatar profile

We can try to first source the logo/avatar from these APIs, and we fail to retrieve we can still fallback to uploading an image that is relevant. This is where I believe this tool will be useful. Here is how it looks after the tool has updated the entity images.

Entity Image Updater ScreenshotThe tool actually updates a field called “entityimage” in the entity.

EntityImageField

Installation

  1. Download the latest version of XrmToolBox.
  2. Install the tool from the XrmToolBox store. Refer http://mscrmtools.blogspot.com.au/2016/04/welcome-to-xrmtoolbox-plugins-store.html for additional details.

Using the Tool

  1. After opening the tool, click “Load Entities” to display all entities
  2. Choose an entity that has an entity image field. You’ll know that an entity has this field, if it has the person default image on the form. This is important, as I currently don’t check if the entity has this field or not. I plan to do this in the next version.
  3. Choose a source for the images. If you chose “Local Folder” you will be asked to select the folder that has the images. Please ensure that the folder has the right sized images. I plan to add resize, upload capability in the future version for “Local Folder” option
  4. Choose an attribute whose value you would use to search. For eg. if you choose “Clearbit”, select a field that has the company’s Url. This could be the file name, if you chose “Local Folder” (only png, jpg and jpeg images can be used, if you chose this option)
  5. Click on “Update All“, if you would like to lookup and update all the records for this entity
  6. If you would like to specify a criteria for update, then click the “Selective Update” field. For eg. you might want to only update records that were created by you. This will open FetchXML Builder that will help you build the conditions. Currently there is a 5-10 second delay in opening the FXB tab. I am still investigating the reason for this delay and will update the tool, once I figure out the fix. The tool will start the update process, straight after you click “Return FetchXML” in the FetchXML Builder tool

The tool will display all the images that were updated in the results window. I haven’t added the capability to save these images to disk, but I will add it in the future if that is a useful feature. I hope this tool will help all CRM folks to fully utilise the entity image field. Please feel free to use the Entity Image Uploader Issues area to log any issues that you encounter.

Thank you to

  • Guido and Adam for their feedback and suggestions
  • Tanguy for XrmToolBox
  • Daryl aka DLaB for the XrmToolBox improvements
  • Jonas for FetchXml Builder

Source code for this tool can be found at https://github.com/rajyraman/Ryr.XrmToolBox.EntityImageUpdater

Publish a Duplicate Detection Rule: Behind the scenes

When you import a solution you get a nice progress bar that you can use to approximately estimate the finish time. But when you publish a duplicate detection rule, you get nothing. All you can basically do is hit the publish and check back 10-15 minutes later.

Sometimes, it might sit in the publishing state longer that you expect. It can be frustating to just wait, without actually knowing whether the process has frozen or it is actually doing something. The underlying entity DuplicateRule is not searcheable from advanced find, and so there is no other information available except the window saying “Publishing”.

I did some research into what actually happens under the covers when you hit publish. When you hit publish CRM has to:

  1. Create new matchcode tables for the base entity and the matching entity
  2. Populate the matchcode tables with the duplicate criteria specified in the rule

For some reason that I haven’t been able to figure out so far, it seems the matchcode table count is always actual entity count + 1. There is a matchcode record with ObjectId 00000000-0000-0000-0000-000000000000 in each matchcode table. I have no idea why this record exists, but my theory is that this is some sort of bit mask. I maybe totally wrong on this though and this could just be some sort of sequence.

I wrote the script below to confirm the duplicate rules are indeed doing something.

declare @duplicaterules table (rownum int identity(1,1), name varchar(200),matchcodetable varchar(200),entity varchar(200))
declare @i int,@matchcodetable varchar(200),@entity varchar(200),@name varchar(200),@max int
declare @result varchar(max),@query nvarchar(max),@matchcodecount int,@entityrowcount int
insert into @duplicaterules
select name,MatchingEntityMatchCodeTable,MatchingEntityName from DuplicateRule where statecode=0
select @max=@@ROWCOUNT,@i=1
while(@i<=@max)
begin
	select @matchcodetable=matchcodetable,@entity=entity,@name=name from @duplicaterules where rownum=@i
	set @query=N'select @matchcodecountOUT=count(*) from ' + @matchcodetable + ' where ObjectId<>''00000000-0000-0000-0000-000000000000'''
	exec sp_executesql @query,N'@matchcodecountOUT int OUTPUT',@matchcodecountOUT=@matchcodecount OUTPUT
	set @query=N'select @entityrowcountOUT=count(*) from ' + @entity
	exec sp_executesql @query,N'@entityrowcountOUT int OUTPUT',@entityrowcountOUT=@entityrowcount OUTPUT
	print 'Duplicate Rule '+@name+' is '+CAST(CAST(@matchcodecount*100.0/@entityrowcount as numeric(5,2)) as varchar(6))+'% complete: '
	print 'Entity: '+@entity+', Matchcode Table: '+@matchcodetable+', Matchcodes: '+CAST(@matchcodecount as varchar(20))+', Entity Rows: '+CAST(@entityrowcount as varchar(20))+CHAR(10)
	set @i=@i+1
end

When you run the script in SSMS on the MSCRM database, you will see a output that looks something like this.

DuplicateOutput

So now you can quickly check the status in the database, if you are little impatient. Once again, sorry CRMOnline, but I like having access to the actual database.

EDIT (22/09/15): Updated SQL Script slightly to produce the percentages correctly. The previous script was always displaying integer. I also exclude matchcode row with objectid 00000000-0000-0000-0000-000000000000

SQL Script: Entity row count

Run the script below on the MSCRM database to quickly get a count of records, for real and customisable entities (not logical).

declare @query nvarchar(max)
set @query=''
select @query=@query+'select count(*) Records,'''+Label+''' Entity, '+ cast(IsCustomEntity as varchar(1)) +' as IsCustomEntity from '+name+' union all ' from dbo.LocalizedLabelAsIfPublishedView a
inner join dbo.EntityAsIfPublishedLogicalView b on a.ObjectId=b.EntityId
where a.ObjectColumnName='LocalizedName'
and IsLogicalEntity=0 and IsCustomizable=1
set @query='select Entity,Records, case when IsCustomEntity=''0'' then ''No'' else ''Yes'' end as CustomEntity from ('+left(@query,len(@query)-10)+') s where s.Records>0 order by Entity'
exec sp_executesql @query

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.