Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Microsoft Dynamics CRM 2011- Update Users Outlook Filters via Script

Post Author: Joe D365 | February 13th, 2012

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!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

6 comments on “Microsoft Dynamics CRM 2011- Update Users Outlook Filters via Script”

  2. 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?

    1. 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.

    2. 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.

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

PowerObjects Recommends