Caching Issue in CRM 2011

In my recent implementation when I changed the date time of the server to and old date the customization and user roles stopped reflecting unless I reset the IIS.

It is because of the caching feature in CRM. IISRESET clears the server cache and everything works fine.

Upon investigating how the CRM2011 cache is managed - there is a Notifications table in the MSCRM_CONFIG database that includes entries that instruct CRM to clear cache items. The Async Server picks these up and notifies the HTTP Worker processes to drop each cache by Key.

The reason my notifications were not getting through was that I had set the server time as an year back. This meant that there were notifications sitting in this table queue that were for an year old. The Async server seems to use the latest date when it first starts to define a time window to query for new notifications. I performed an unsupported DELETE from the MSCRM_CONFIG.dbo.Notification table and all was well again.

Thanks to  Develop 1 Limited Blog for the Details 

Export/Import user saved views in Microsoft Dynamics CRM 2011

Export and Import user saved views in Microsoft Dynamics CRM 2011.

As we know user can't "move" his saved views from one environment to another. There is a third party product available to do the same.
The application has been created using Silverlight and its available for download for free.

The application can be downloaded from here:
http://exportimportuserview.codeplex.com/releases

Please go through the below link for the instructions:
http://dynamicslollipops.blogspot.in/2011/07/export-import-user-saved-views.html

Get Campaign List for a given Account/Contact in MS CRM 2011

Get all campaign where the given Account is part of.

SELECT CodeName,name FROM FilteredCampaign  -- Campaign Deatils
WHERE CampaignId IN (
SELECT CampaignId FROM FilteredCampaignItem -- Marketing list associated with the Campaign
WHERE EntityId IN (
SELECT LM.listid FROM FilteredListMember LM -- account associated with all marketing lists
LEFT JOIN FilteredAccount Account ON Account.AccountId=EntityId
WHERE Account.name='HP'))

Get all launched campaign where the given Contact is part of.

SELECT CodeName,Name FROM FilteredCampaign -- Campaign Deatils
WHERE CampaignId IN (
SELECT CampaignId FROM FilteredCampaignItem  -- Marketing list associated with the Campaign
WHERE EntityId IN (
SELECT LM.listid FROM FilteredListMember LM -- contact associated with all marketing lists
LEFT JOIN FilteredContact CONTACT ON CONTACT.ContactId=EntityId
WHERE CONTACT.FirstName='CHANDAN'))
AND statuscodename='Launched' -- Only LAUNCHED CAMPAIGN

Orion Beta (MS CRM 2013) On-Premise/Online trial

Orion Beta On-Premise/Online trial 

The CRM 2013 beta was released on July 28th 2-13 but hasn't been made publicly available as of yet.

You can get the access of the online version. Just register yourself @ https://connect.microsoft.com/site687/Survey/MandatorySurveys.aspx

Show Members n Target Product of a Campaign in MS CRM 2011

Very common requirement to show all members of a campaign and the target products of a campaign in Report. Below query returns the same.

-- Get Members of a Campaign

SELECT DISTINCT EntityId,
CASE
WHEN LM.entitytype=1 THEN 'ACCOUNT'
WHEN LM.entitytype=2 THEN 'CONTACT'
WHEN LM.entitytype=3 THEN 'Lead' END AS 'CUSTOMER TYPE',

CASE
WHEN LM.entitytype=1 THEN ACCOUNT.Name
WHEN LM.entitytype=2 THEN CONTACT.FullName
WHEN LM.entitytype=3 THEN Lead.FullName END AS 'CUSTOMER NAME'

FROM FilteredListMember LM
LEFT JOIN Account ON ACCOUNT.AccountId=EntityId
LEFT JOIN CONTACT ON CONTACT.ContactId=EntityId
LEFT JOIN Lead ON Lead.LeadId=EntityId
WHERE ListId IN
(Select entityid From FilteredCampaignItem where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=4300)

-- Get Target Products of a campaign.

SELECT DISTINCT entityid, Campaign = (SELECT Name FROM Campaign where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28'),
P.ProductNumber, P.name as productname
FROM
FilteredCampaignItem CMP
JOIN Product P ON P.ProductId=CMP.entityid
where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=1024

Get the last log-in DateTime of CRM user in MS CRM 2011

In Microsoft Dynamics CRM 2011 you can get the last log-in time of a CRM user by using the below query:

SELECT
SU.SystemUserId
,SU.DomainName
,SU.FullName
,SUO.LastAccessTime
FROM SystemUser SU
INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] SUO ON SUO.CrmUserId = SU.SystemUserId
INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserAuthentication] SUA ON SUA.UserId = SUO.UserId
ORDER BY SUO.LastAccessTime DESC,SU.DomainName

Get FROM, TO, CC, BCC parties in Reports/SQL in MS CRM 2011

It's very common requirement to get the TO and FROM party list  in our report for activities. As we know it doesn't store as part of activity/activitypointer. It get stores separably in activityparty table, and all parties are separate entry in activityparty table.

I wrote a function to get the FROM, TO, CC, BCC or any other party list from CRM in your report. Juse use below function in your report, it will return all parties as comma separated string.

-- To : To Recipient
-- From : Sender
-- TO get "TO" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','To Recipient')
-- TO get "FROM" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','Sender')

CREATE FUNCTION [dbo].[fn_GetPartyList](@ActivityId NVARCHAR(100),@PatryAttribute nvarchar(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(1000)
DECLARE @Indivisual NVARCHAR(100)
SET @Result=''
DECLARE @Count INT

SET @Count =(SELECT COUNT(partyidname) from FilteredActivityParty
   WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId)
IF(@Count>0)
BEGIN

DECLARE @Table1 TABLE(Row INT IDENTITY(1,1), partyidname nvarchar(100))
INSERT INTO @Table1
SELECT partyidname from FilteredActivityParty
WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId

WHILE(@Count>=1)
BEGIN
SET @Indivisual=(SELECT partyidname FROM @Table1 WHERE Row=@Count)
SET @Result=@Indivisual+','+@Result

SET @Count=@Count-1
END

END

DECLARE @LENGTH INT
SET @LENGTH=LEN(@Result)
RETURN SUBSTRING(@Result,0,@LENGTH)
END

Get all Attribute Detail of an Entity in MS CRM 2011

Use below query to get field specification details of any entity in CRM.


SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
LocalizedLabelView_2.Label AS AttributeDisplayName, AttributeView.Name AS AttributeName,
Type = (select XmlType from MetadataSchema.AttributeTypes where AttributeTypeId = AttributeView.AttributeTypeId)
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN EntityView
INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON AttributeView.EntityId = EntityView.EntityId
WHERE   LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
 AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
 AND LocalizedLabelView_1.LanguageId = '1033'
 AND LocalizedLabelView_2.LanguageId = '1033'
 AND EntityView.Name IN ('contact')  // Change the entity name
order by AttributeDisplayName

Apply the run-time filter on top of the security privilege in MS CRM 2011

Apply the run-time filter conditions on top of the security privilege to filter/remove/hide the entity records.

Create a plugin which will trigger on pre (stage) of retrievemultiple (plugin message) of the entity.
Context will return you the Query object which will have entity name and the Query expression details. you can append your filter conditions to query expression to filter the records for the entity.

Below please find the complete Plugin code.
It will trigger on load of every CRM view, so always check the entity name in the plugin and apply the appropriate logic.

public void Execute(IServiceProvider serviceProvider)
        {
            // Obtain the execution context from the service provider.
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));            
            
             if (context.Mode == 0 && context.Stage == 20 && context.MessageName.Equals("RetrieveMultiple"))
            
                if (context.InputParameters.Contains("Query"))
                {
                    if (context.InputParameters["Query"] is QueryExpression)
                    {
                        QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];
                        
                        if (objQueryExpression.EntityName == "product")
                        {
                            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                            service = serviceFactory.CreateOrganizationService(context.UserId);
                            ConditionExpression privateFlagCondition;
                            string SaleType = string.Empty;
                            string role = string.Empty;

                            role = getUserRole(context.UserId);
                            if (role.Contains("Sales"))
                            {
                                privateFlagCondition = new ConditionExpression()
                                {
                                    AttributeName = "statustype",
                                    Operator = ConditionOperator.Equal,
                                    Values = { "1" }
                                };                                
                            }
                            

                            FilterExpression newFilter = new FilterExpression()
                            {
                                FilterOperator = LogicalOperator.Or,
                                Conditions = { privateFlagCondition }
                            };

                            objQueryExpression.Criteria.AddFilter(newFilter);
                        }

                    }
                }
            }
        }

Direct data entry to PrincipalObjectAccess Table in MS CRM 2011

Direct data entry to PrincipalObjectAccess Table using SQL Query.
It is an unsupported approach. Try below SQL query to insert the records in PrincipalObjectAccess table from your table (i have taken the table name as backupTable).


Declare @SystemUser nvarchar(100)
Declare @ObjectId nvarchar(100)
DECLARE @ObjectTypeCode INT
DECLARE @UserType INT
DECLARE @Accessmask INT
DECLARE @InheritedAccessmask INT

declare Cur_SHAREWITHUSER cursor for
Select ObjectId,Principalid, ObjectTypeCode, PrincipalTypeCode, AccessRightsMask, InheritedAccessRightsMask from backupTable -- (CHange the backup table name)
where AccessRightsMask>0 OR InheritedAccessRightsMask>0

open Cur_SHAREWITHUSER
fetch Cur_SHAREWITHUSER into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType, @Accessmask, @InheritedAccessmask

while(@@fetch_status=0)
BEGIN
INSERT INTO PrincipalObjectAccess
(Principalid,ObjectId,ObjectTypeCode,[PrincipalTypeCode],[AccessRightsMask],ChangedOn,
[InheritedAccessRightsMask] ,PrincipalObjectAccessId )
values (@SystemUser , @ObjectId ,@ObjectTypeCode,@UserType,@Accessmask,GETDATE(),@InheritedAccessmask,NEWID())

fetch Cur_SHAREWITHUSER into @ObjectId,@SystemUser,@ObjectTypeCode, @UserType,@Accessmask, @InheritedAccessmask

END

close Cur_SHAREWITHUSER
deallocate Cur_SHAREWITHUSER

Reopened incident count using FetchXML in MS CRM 2011

Get all reopened incident count using FetchXML. It will include those tickets also for which state code changed more than twice.

<fetch aggregate='true'>
  <entity name='incident'>
    <attribute name='incidentid' aggregate='countcolumn' alias='countincident' distinct='true' />
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <link-entity name="incidentresolution" from="incidentid" to="incidentid" alias="aa" distinct='true'>
    </link-entity>
  </entity>
</fetch>


<fetch distinct='false' mapping='logical' aggregate='true'>
  <entity name='incident'>
    <attribute name='title' alias='IncidentReOpenCount' aggregate='countcolumn' />
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <link-entity name='incidentresolution' from='incidentid' to='incidentid' alias='aa'>
      <attribute name='incidentidname' alias='IncidentName' groupby='true' />
    </link-entity>
  </entity>
</fetch>

Get shared records for a User/Team in MS CRM 2011

Get shared records for a User/Team:

We get very frequent requirement on how to get the shared records for a User/Team in Plugin/web application using CRM SDK/service.

I prepared below fetchXML which returns all shared records of an entity for a user/Team.

Its a generic fetchXML. Just we need to replace 3 value.
1. Entity name (just replace account with any crm entity).
2. Primary field (for account it is accountid). It is used to join principalobjectaccess and the entity. So just provide the entity primary field in "to".
3. User/Team GUID. (just replace the user/team GUID in value).

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='account'>
<link-entity name='principalobjectaccess' to='accountid' from='objectid' link-type='inner' alias='share'>
<filter type='and'>
<condition attribute='principalid' operator='eq' value='E36D3084-8135-E211-AC03-000C29AD15D1' />
</filter>
</link-entity>
  </entity>
</fetch>

Hide Entity from Advanced Find in MS CRM 2011

Hide Entity from Advanced Find

As you know You should be able to handle this through security role, don't provide read permission on that entity to particular user, and it will be hidden for him.
But if you have read privilege on the entity and still you want that entity not to be shown in Advanced find then there is no supported way.

But there is an unsupported way to achieve the same. CRM exposes below Boolean property in EntityMetadata to identify which entity should appear in Advanced Find.

EntityMetadata.IsValidForAdvancedFind Property - Gets or sets whether the entity is will be shown in Advanced Find.
AttributeMetadata.IsValidForAdvancedFind Property - Gets or sets the property that determines whether the attribute appears in Advanced Find.

Try below query to hide Account entity from Advanced Find.

update MetadataSchema.Entity set IsValidForAdvancedFind = 0 where Name = 'account'

update MetadataSchema.Relationship set IsValidForAdvancedFind = 0
where ReferencingEntityId = (select MetadataSchema.Entity.EntityId from MetadataSchema.Entity where Name = 'account')
and IsValidForAdvancedFind  = 1


Reset IIS and clear server(and client) cache.

This will hide the entity for all users on the Advanced Find.

CRM 2011 views: getting more than 5000 records

If you access any CRM views for example account page and if it got more the 5000 records you will see “5000+”. You can update this limit by running following query again MSCRM_Config database, so IntColumn contains number of maximum records to be run for CRM. If you put value -1 it will retrieve all the records.

Update DeploymentProperties Set IntColumn=-1 Where ColumnName = 'TotalRecordCountLimit'

So IntColumn contains number of maximum records to be run for CRM. If you put value -1 it will retrieve all the records


Note
1. Above change is unsupported so make sure you apply this change to Dev, Test environment before applying this to Live environment. Also make sure you take back up of database before doing this change
2. This change might impact performance as well since system will retrieve all the records.

Reference : https://community.dynamics.com/product/crm/crmtechnical/b/crmmayankp/archive/2012/06/15/crm-2011-views-getting-more-than-5000-records.aspx

Read Subject sub-child from tree structure in MS CRM 2011

Always its difficult to get the Subject Tree structure to implement your business logic.
I have written CTE to access the sub-child just by passing the Root level Subject.

Create the function in CRM database.

CREATE FUNCTION [dbo].[GetDownSubjectHierarchy] ( @SubjectId NVARCHAR(100))
RETURNS TABLE
AS
RETURN
(
WITH DirectReports (ParentSubject, ParentSubjectName, SubjectId, Title, Level)
AS
(
-- Anchor member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title,0 AS Level
FROM dbo.Filteredsubject AS FSU1
WHERE SubjectId = @SubjectId

UNION ALL
-- Recursive member definition
SELECT FSU1.ParentSubject,FSU1.ParentSubjectName, FSU1.SubjectId, FSU1.Title, Level + 1
FROM dbo.Filteredsubject AS FSU1
INNER JOIN DirectReports AS d
ON FSU1.ParentSubject = d.SubjectId
)

    -- Statement that executes the CTE
    SELECT Distinct * FROM DirectReports d
)

Execute the below query to get the Subject sub-child of (down) level 2  by passing the subjectid.

SELECT * FROM [GetDownSubjectHierarchy]('35344367-8535-E211-AC03-000C29AD15D1')
WHERE Level=2

Just execute the Query and store the result in array/list/data set/data table as you want to execute your business logic.

You can get the sub-child of any level, just change the value of level (0 is the root subject).