Thursday, August 2, 2012

Create offline filters for Custom Entity for each user in MS CRM 2011


In almost every CRM implementation we create custom entities. When user goes offline we need to create the offline filters for all the custom entities user wants to take in offline. To achieve this either we train users to create the offline filters or we create some package to do the same.

I achieve the same by creating offline filters in database by inserting new entry in UserQuery for each user for each custom entity.  Below find the Query. 

Declare @UserId nvarchar(100)
declare @OwningBusinessUnit nvarchar(100)
DECLARE @FetchXml nvarchar(max)
Declare @Description nvarchar(100)
Declare @Name nvarchar(100)
Declare @ObjectTypeCode int 

set @FetchXml ='<fetch version="1.0"
mapping="logical" output-format="xml-platform"><entity
name="new_salesgroup"><attribute name="new_salesgroupid" /><filter type="and"><condition attribute="statecode" operator="eq" value="0"
/></filter></entity></fetch>'

set @Description = 'Sales Groups owned by me'
set @Name = 'My Sales Groups'
select @ObjectTypeCode = ObjectTypeCode from EntityView where name='new_salesgroup'

declare cur_accounts cursor for
select SystemUserId from SystemUser where FullName not in ('SYSTEM','INTEGRATION')

open cur_accounts
fetch cur_accounts into @UserId
while (@@FETCH_STATUS =0)
begin
if not exists (select * from UserQueryBase where QueryType = 16 and FetchXml = @FetchXml and StateCode = 0 and Name=@Name and OwnerId = @UserId)
begin
select @OwningBusinessUnit=BusinessUnitId from SystemUser where SystemUserId = @UserId
Insert into UserQueryBase (QueryType,ModifiedOn,ModifiedBy,FetchXml,Description,StateCode,UserQueryId,Name,CreatedBy,ReturnedTypeCode,
OwningBusinessUnit,CreatedOn,OwnerId,OwnerIdType)
values (16, GETUTCDATE(),@UserId,@FetchXml,@Description,0, NEWID(),@Name,@UserId,@ObjectTypeCode,@OwningBusinessUnit,GETUTCDATE(),@UserId,8)
end
fetch cur_accounts into @UserId
end

close cur_accounts
deallocate cur_accounts

No comments: