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:

crm2011

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!

Avatar for Joe D365

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.