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
