다음을 통해 공유

MSCRM - Rollup activities for custom entities. Alternative at plugin

  • 아티클

Introduction

In this article I want to explain an alternative at plugin to see all activities from custom entities in Activity Subgrid for Account and Contact .I needed to load all activities for all my custom entities. I found some solutions on Google with plugins but there were some limitations:        

  1. The load was too slow: Because inside the plugin there are QueryExpressions in the post

  2. There were limits on sort and on paging

  3. These two problems were big for the client.

I searched another way to view the activity for custom entities. In the database there are two stored procedures: p_RollupByAccount and p_RollupByContact. These get all activity related with account and with contact.

The stored procedure are simple: there is a first step where it read the AccountId and create a temporary table called RollupIds with all ID of entity we need to watch on Activity SubGrid on CRM. The other code gets IDs from all related entity with Account or Contact and put them inside the temp table #RollupIds:                                          

insert into  #RollupIds(RollupId (
    select distinct  i.InvoiceId from  InvoiceBase as  i (NOLOCK)
    where
        i.CustomerId = @AccountId and i.CustomerIdType = 1 and not exists(
            select RollupId from #RollupIds where i.InvoiceId = RollupId)
        )

Goals

I Need to get all actitivities where RegardingId is related at a custom entity for account and contact:

https://nothingnessit.files.wordpress.com/2016/04/capture.png

Steps

We are ready to modify the p_RollupByAccount and p_RollupByContact stored procedures

1 - For any custom entity before I check if table existing:  

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND TABLE_NAME =  'CustomEntityTable')) 
   BEGIN
   -- TODO 
   END

2      Create a temp table where save the ID of the all custom entities that we need:

Create Table  #RollupCustomIds (RollupId uniqueidentifier PRIMARY KEY)

3      Insert into the #RollupCustomIds all ID of the custom entities (for example for custom entity 1):

insert into  #RollupCustomIds(RollupId) (
        select distinct  ce.customEntitiyID from customentity as ce (NOLOCK) 
 where
          ce.account = @AccountId and not exists(
                select RollupId from #RollupCustomIds where ce.customEntity = RollupId
           )
 )

4      Now we need to get all activiyPointers ID from activityPointer table. We use the RollupCustomIds to get information and insert into #RollupIds table:

insert into  #RollupIds(RollupId)
(
                select distinct  activitypointer.ActivityId
from ActivityPointer as activitypointer (NOLOCK) 
join #RollupCustomIds  as ri on
(
                                activitypointer.RegardingObjectId = ri.RollupId
                                               )
where
not exists(
select RollupId from #RollupIds where activitypointer.ActivityId = RollupId
)

Conclusion

This is an alternative way to use Rollup but it is not an official solution. Before to modify any things inside the database please make a backup !! I like this solution because I found it too fast.