Microsoft Dynamics CRM 2011- Update Users Outlook Filters via Script

Posted on February 13, 2012 by JoeCRM

The Microsoft Dynamics CRM Outlook synchronization filters provide a way to granularly specify the data that is synchronized between Outlook and CRM. If the offline outlook crm client is enabled, then these filters specify the data that is available offline. These filters are defined on a per user basis:

Update Users Outlook Filters via Script

We often have had clients that would like to change the CRM Outlook filters for an entire organization, instead of having each user configure the CRM Client and then manually configure each filter according the business requirement. The CRM SDK contains methods for updating these filters for all users.

For testing purposes, we created a set of handy SQL script that runs at the database level to update everyone’s filters.

We first configured the desired filters for a single user and then used the script below to update everyone’s filters.

declare @defaultuser varchar(50)
--change GUID of default user
--This is the user that we are taking the 'defaults' from
SET @defaultuser='E38AFE25-45EA-E011-B45F-0050569877C4'  ?

--updates all outlook views
--this will update EVERYONEs rule.
update userquery
set fetchxml=(select defuserquery.FetchXml
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId),
statecode=(select defuserquery.statecode
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId),
statuscode=(select defuserquery.statuscode
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId),
name=(select defuserquery.name
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId),
description=(select defuserquery.description
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId)

from systemuser u
where u.systemuserid=OwnerId
and QueryType in (16,256)
and exists (select defuserquery.FetchXml
from userquery defuserquery
where defuserquery.OwnerId=@defaultuser
and userquery.ParentQueryId=defuserquery.ParentQueryId)

Since we are directly writing to the database use the above only for testing and learning about Dynamics CRM.

Happy CRM’ing!

My Twitter profileMy Google+ profile

JoeCRM

Microsoft Dynamics CRM Expert at PowerObjects
Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more business and organizations around the world.
This entry was posted in CRM 2011, Development and tagged , , , , . Bookmark the permalink.
  • Shai

    Look like there is a problem with how the script display on the blog post.

    • Alex Fagundes

      Thanks. WE fixed the formatting.

  • VD

    Hi, how can we disable users from altering the filters set by the script afterwards?
    Is there a security role setting that can prevent users from creating/editing the outlook and offline filters?

    • Alex Fagundes

      Hi, I don’t think there is a way to prevent users from creating these filters. However, you CAN write yoru queries in a way so they update if any of these filters are modified and run them once a day. Alternatively, with crm 2011 you CAN create system filters. These would then be locked down so users can not modify them. Creating these would be a bit more complex as they would have to be done via teh SDK and not a sql query.

    • Jeremy

      Disable the Manage User Synchronization Filters security role under Core Records. I believe this role is setup to prevent users from having the ability to modify the filters.

  • RC

    This SQL script appears to only be applicable to CRM on-prem. Any chances you have a similar method for CRM Online customers?