The CRM Grid – A Microsoft CRM Blog

Greg Owens’ rose-tinted MS-CRM mumblings

Archive for May, 2010

Dates in FetchXml queries

Another quick post. A colleague was trying to execute a FetchXml statement that included a filter on a date field but couldn’t understand why he was not returning the expected results (indeed, wasn’t returning any results).

At first glance, the query looked fine:

<fetch mapping="logical">
	<entity name="new_entity">
		<attribute name="new_entityid"></attribute>
		<filter type="and">
			<condition attribute="new_linkentityid" operator="eq" value="f25f4cee-e851-df11-bebe-000c29a1faa8"></condition>
			<condition attribute="new_date" operator="eq" value="2010-04-30"></condition>
		</filter>
	</entity>
</fetch>

The link entity was definitely correct and the date field certainly read 30th April 2010. The date field was also defined as Date Only in the attribute definition.

It transpired that the problem was (predictably) linked to times. CRM stores all dates in UTC format () so even if the attribute is defined as “Date Only” in the entity definition, 30th April 2010 will actually be stored as 2010-04-30T00:00:00. For some reason, which I haven’t yet determined for certain (there are a couple of theories that i don’t have time to test), the record he was seeking was dated 2010-04-30T08:00:00. It may be due to his time zone, since he was working in the US at the time of the problem. It may be that this attribute was previously a Date AND Time attribute and then got changed. Either way, the simplest solution was to use a different FetchXml operator – replacing the “eq” operator in the “new_date” condition with the “on” operator thereby ignoring times:

<fetch mapping="logical">
	<entity name="new_entity">
		<attribute name="new_entityid"></attribute>
		<filter type="and">
			<condition attribute="new_linkentityid" operator="eq" value="f25f4cee-e851-df11-bebe-000c29a1faa8"></condition>
			<condition attribute="new_date" operator="on" value="2010-04-30"></condition>
		</filter>
	</entity>
</fetch>

A list of FetchXml operators can be found on MSDN

Checking the ExecuteFetchRequest message

Sorry no updates for so long – NDAs with my main client and 5 hours commuting a day is hammering my blog time!

Just a quick note: new version of the CRM SDK is out now – version 4.0.12.

One of the items I noted was a technique to improve the performance of plugins that operate on the Execute message. I have been developing various plugins for my client which change and/or calculate data that gets passed to the UI, by harnessing the Retrieve and RetrieveMultiple messages. When data is being displayed a CRMGrid control however, CRM uses the Execute message instead (specifically the ExecuteFetchRequest message, but this can’t be checked directly).

Given the pervasive nature of Execute message calls (tested thanks to David Jennaway’s work here), we’d already determined that checks were necessary to processing the majority of calls to the Execute message. After discovering that the Execute message’s InputParameters only contain a FetchXml property when a grid is being processed, we can exit the plugin code much quicker thereby improving system performance. It’s nice to know that this is actually the preferred method too!

Here’s a little sample:

    public class ExecuteHandler : IPlugin
    {
        public void Execute(IPluginExecutionContext context)
        {
            if (context.InputParameters.Contains("FetchXml"))
            {
                if (context.OutputParameters.Contains("FetchXmlResult"))
                {
                     // here I intercept and change some values in the FetchXmlResult fragment
                }
            }
        }