The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

CRM Blog – Automating Local Data Query Creation

CRM Blog – Automating Local Data Query Creation

  • Comments 5

This month's guest CRM MVP blogger is David Jennaway.

CRM provides a powerful mechanism for controlling what data each user of the Outlook laptop client can take offline. This can be easily defined on a per user basis, however there is no simple way to configure identical settings for multiple users. In this blog article I’ll show how to use the CRM web service to automate this process.

Each laptop client user has a set of Local Data queries that define the criteria that determine which records should be taken offline. These queries are very similar to the user views that a CRM user can create and save via Advanced Find; in each case they are stored as an instance of the UserQuery entity, but with different values for the QueryType attribute. Local Data queries have a QueryType value of 16, while it is 0 for Advanced Find views. See SavedQueryType

The approach I’m taking here is to create an initial UserQuery via the CRM user interface, then creating code to create a copy of this UserQuery for each designated user.

The UserQuery entity is unusual in that it is not possible to create a UserQuery, then assign it to another user, as you can only get user level permissions on the entity. The only way to create a UserQuery for another user is to impersonate that user via the CallerIdValue property of the CRM web service proxy. This does raise a security issue, as you can only use CRM impersonation if your code is running under an AD account that is a member of the AD group PrivUserGroup.

A fully worked example of a .Net Console application that copies a view to one or more users is available at CodePlex. The important parts of this code are as follows:

Code to retrieve the initial UserQuery. This can be done with a standard QueryByAttribute:

private QueryByAttribute MakeQuery(Guid OwnerId, string ViewName)

{

QueryByAttribute qe = new QueryByAttribute();

qe.EntityName = EntityName.userquery.ToString();

qe.ColumnSet = CreateColumnSet(new string[] {"querytype", "name", "returnedtypecode", "description", "fetchxml", "columnsetxml", "layoutxml"});

qe.Attributes = new string[] {"ownerid", "name", "querytype"};

qe.Values = new object[] {OwnerId, ViewName, QUERYTYPE};

return qe;

}

The main loop that creates copies of the UserQuery:

public int DoClone(string ViewName, Guid[] DestinationUsers)

{

int count = 0;

try

{

QueryByAttribute qeSrc = MakeQuery(srcOwnerId, ViewName); // Build a query expression to retrieve the view

svcCrm.CallerIdValue = GetCallerId(srcOwnerId); // Set callerId to use source owner

BusinessEntityCollection srcQueries = svcCrm.RetrieveMultiple(qeSrc);

foreach (userquery srcQuery in srcQueries.BusinessEntities)

{

foreach (Guid destUser in DestinationUsers)

{

if (destUser == srcOwnerId) // Ignore the original user

continue;

svcCrm.CallerIdValue = GetCallerId(destUser); // Now run under the context of the destination user

try

{

srcQuery.userqueryid = null; // Remove the exisitng primary key value

Guid idNew = svcCrm.Create(srcQuery);

count++;

}

catch (System.Web.Services.Protocols.SoapException ex)

{

XmlDocument doc = new XmlDocument();

doc.LoadXml(ex.Detail.InnerXml);

XmlNodeList code = doc.GetElementsByTagName("code");

if (code == null || code.Count == 0 || code[0].InnerText != "0x80042f09") // Ignore error thrown if user has no roles

throw ex;

}

}

}

}

catch (System.Web.Services.Protocols.SoapException ex)

{

throw new Exception("Error in DoClone: " + ex.Detail.InnerXml);

}

catch (Exception ex)

{

throw new Exception("Error in DoClone: " + ex.Message);

}

return count;

}

And a couple of helper functions used in the above code:

private CallerId GetCallerId(Guid id)

{

CallerId ret = new CallerId();

ret.CallerGuid = id;

return ret;

}

private ColumnSet CreateColumnSet(string[] Columns)

{

ColumnSet ret = new ColumnSet();

ret.Attributes = Columns;

return ret;

}

David Jennaway

  • PingBack from http://crm.discoveryjournal.info/2007/12/14/crm-blog-%e2%80%93-automating-local-data-query-creation/

  • Ok, so I downloaded the application, and I appear to have all of the parameters correct, and am not receiving any error messages, but all I receive is "My Open Contacts: 0 views Created".  I have tried it with the view not being there on the user being copied to, I have tried it with creating the view before doing the copy, and both have the same result.  I am using a user that is in the correct AD group.  Any ideas as to what I am doing wrong?

  • Jonathan, exact same issue here. Did you manage to find a fix??

  • I just tried the tool and get the same issue. Have you found a solution for this?

  • Hi David & al. I think I have found a simpler way. Maybe not supported by MS though.

    There is an entity named filtertemplate with just 6 fields:

    FetchXML: the filter

    Description: desc. of the filter.

    QueryType: a number. You will look for querytypes = 16. Look in the SDK help for different querytype values. 16 is <quote> Specifies an offline Microsoft Dynamics CRM for Microsoft Office Outlook filter query. </quote>

    FilterTemplateId: primary key

    ReturnedTypeCode: the entity objecttypecode

    Name : the name of the filter.

    Two steps required:

    -change XML filter with an SQL update or with an SSIS package. You don't need or can do WS. There is no VersionNumber attribute, this table stays on the server.

    -tell your users to reset their local datagroups filters. (not sure there is a WS option to do this.).

    Anyway, this means that for new deployments, you have the good filters from scratch.

    Cheers.

    Joe

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post