CRM 2011 Workflow Entity View records are not sorted alphabetically

I just recently come across customer reported CRM issue related workflow view displaying on demand workflow list random manner and not displaying them in alphabetically manner.

This issue mainly hits you if you got lots of workflow related to particular entity and while trying to run specific on demand workflow you will see lots of workflow in view and needs to manually sort the list to find your workflow.

Well looking at issue it seemed really easy issue to fix, so just open relevant view and apply sort on name column and publish the changes BUT this view is called “On Demand Workflows” is not customizable and hence we have to do unsupported change to fix this issue. Following steps done to fix this issue using unsupported manner.

Step 1: first find existing view and view definition from the data base. So run following query for this.

select savedqueryid,name,fetchxml from savedquerybase where name like '%On Demand Workflows%' order by name

Please note down savedqueryid as we will be using this in next steps.

Step 2: So following is the Original FetchXML definition for this view. As can be seen there is default sort order is not defined in this definition.

<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>

We will be Modifying above fetch xml with following to include sort order related to this. We refer highlighted change in fetchxml below for this.

<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><order attribute="name" descending="false" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>

Step 3: now update this specific view definition using SQL query against CRM database, please replace the SAVEDQUERYID with value noted down in step 1.

update savedquerybase
Set fetchxml ='<fetch version="1.0" mapping="logical"><entity name="workflow"><attribute name="workflowid" /><attribute name="name" /><attribute name="createdon" /><attribute name="modifiedon" /><attribute name="statecode" /><attribute name="owningbusinessunit" /><attribute name="ownerid" /><order attribute="name" descending="false" /><filter type="and"><condition attribute="type" operator="eq" value="1" /><condition attribute="ondemand" operator="eq" value="true" /><condition attribute="statecode" operator="eq" value="1" /><condition attribute="category" operator="eq" value="0" /></filter></entity></fetch>'
where name = 'On Demand Workflows'
and savedqueryid = 'SAVEDQUERYID';

After applying above changes log in to CRM and verify this view is displayed record correctly now.

****Important Notes regarding above change****
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.

Hope this helps…

Cheers,
MayankP:)

CRM : Rescheduling the Update Contract States job

Update Contract States job is CRM 2011 (also for CRM 4.0) system job which runs daily and change the contract status based on contract start date and end date (i.e. change contract from Invoiced to active and from Active to expired).

One of customer’s CRM organization has this job running 10 PM at night and wanted to change this job on particular time (very early in the morning) to make sure contract status are up to date when users starts using CRM system.

There is job editor provided by MS to reschedule CRM system jobs but this does not include this specific job.

so following are steps we undertook with the help of CRM parter/MS support to reschedule this particular job in CRM system.

Step 1: run the following query to find the current Jobs in the relevant CRM organization. (note down the AsyncOperationid returned from this query as we will be using this in next step)

select AsyncOperationid,RecurrenceStartTime,postponeuntil,StatusCode, RecurrencePattern from AsyncOperation WHERE
Name = 'Update Contract States job'
and
StatusCode =10
and RecurrencePattern is not null

following quick details regarding important columns in this table.

RecurrencePattern is used to describe frequency and interval of the system jobs and please refer this article for more information on regarding this.

Postponeuntil is datetime field and indicate when this job will run next time. And it is UTC date meaning if this field contains “2012-10-23 09:08:00.000” and if your time zone is GMT + 1 then this job will run next time on “2012-10-23 10:08:00.000”

RecurrenceStartTime is also UTC datetime field which will be used to set next runtime of job, date part of this field is not important as long as it is set in the past.

Step 2: so using noted down AsyncOperationid in step 1 run following query to update recurrencestarttime, postponeuntil for this Update Contract States job .


update AsyncOperation set
RecurrenceStartTime = '2012-10-22 09:08:00.000',
postponeuntil = '2012-10-23 09:08:00.000'
WHERE AsyncOperationid = '5EB24ECB-60ED-4F59-801D-A6C19465C4D8'


So as per above update, this job will run at 9:08 AM (GMT time) in the morning every day.after job run if you verify the details then it would look as follows. So as you can see system will update postponeuntil (next run time) to next day (24th October) after this job run today (on 23th October).

****Important Notes regarding above change****
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.

Hope this helps..

Cheers,
MayankP:)

CRM 2011: Reassign or Transfer Personal Views

Recently come across scenario where one of CRM user left the company and that user owned specific views which need to be transfer to his replacement CRM users.

These views created by old user using CRM advanced find screen and hence they are personal view of that user and as per CRM security settings even system administrator can not able to view and transfer this views to new user.

So I decided use impersonation to resolve this issue and created small standalone application for the same.

This application is shared below.
CRM2011_ReAssignViews_EXE

Following is few more details regarding this application for the same.

Step 1: after downloading, CRM2011_ReAssignViews_EXE.zip and extract the files.

Step 2: after extracting run the CRM2011_ReAssignViews_EXE.exe from extracted files.

Step 3: provide CRM server name, relevant port number, organization name and provide relevant credential for the same and then click on “Retrieve List of User” button.

For further steps and information please refer to following screen print for the same.

Note: – Please note that both users needs to active in CRM to perform above actions.

Hope this helps..

Cheers,
MayankP:)

How to find records created on weekends or created on after working hours

Recently one of our customer asked how to find cases or emails created on Saturday/Sunday or cases created after normal working hours (i.e. after 18 PM?).

Of course this CRM system had email router which were creating those email and automatic case were created based on this.

I did try to so this using advanced find but it is not possible to get the desired result and try to do this using Excel features and it was really easy, following quick steps for the same using Excel.

Step 1: Export data to excel, so do advanced find on CRM (CRM 4.0 or CRM 2011) to retrieve relevant data and then export that to excel.

Step 2: once exported add new columns, let’s say those columns are called “DAY”, “Time HOUR” and “Time MIN”.

Step 3: put formula to retrieve values for those columns, as shown below. Please note that column G is created on date and based on this column G following formula are created. Please change column name as per your requirement.

COLUMN I (DAY) :

=IF(WEEKDAY(G2)=1,"SUNDAY",IF(WEEKDAY(G2)=2,"MONDAY",IF(WEEKDAY(G2)=3,"TUESDAY",IF(WEEKDAY(G2)=4,"WEDNESDAY",IF(WEEKDAY(G2)=5,"THURSDAY",IF(WEEKDAY(G2)=6,"FRIDAY",IF(WEEKDAY(G2)=7,"SATURDAY",)))))))

COLUMN J (Time Hour):
=HOUR(G2)
COLUMN K (Time Hour):
=MINUTE(G2)

Step 4: after apply this formula to all rows and you will get following result.

Step 5: you can apply standard excel filter to return only cases created on weekends (Saturday/Sunday) or cases created after 18 PM easily.

Hope this helps…

Cheers,
Mayank:)

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+” as shown below.

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

****Important Notes regarding above change****:
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.

There is another registry key called “TurnOffFetchThrottling” is also there which will allow you to retrieve more than 5000 records in CRM, refer following blog article for more information regarding the same.

http://www.interactivewebs.com/blog/index.php/server-tips/turn-off-microsoft-crm-2011-5000-limit-on-data-retrieval-via-sdk/

but above key is only when call is done in the external SDK (fetchXML) calls only, this will not change data retrieve to be display in Entity Grid, so for account grid will still display “5000+” after applied this registry key change and only above SQL script (i.e. unsupported) can change this behaviour.

Hope this helps..

Cheers,
MayankP:)

CRM 2011 Chart: Drill Down enable/disable

Recently we noticed few charts in CRM 2011 displays drill down while other does not display drill down for the same.

For example standard Sales Pipe line chart report does not display drill down, after little investigation found that chard is displaying drill down if field used to display data set as Searchable to YES (as shown in the screen print below)

So your chart will display drill down if axis displays a field which got value searchable set to Yes.

Hope this helps..

Cheers,
MayankP:)

CRM 2011 Email router Issue

Problem

Recently while publishing CRM 2011 email router on our CRM 2011 system we started seeing following error

#17174 – An error occurred while validating the e-mail message with subject “email subject” in mailbox “mailbox email”. Microsoft.Crm.Tools.Email.Providers.EmailException: E-mails for address “mailbox email”. in forward mailbox could not be processed as the e-mail address is not associated with any approved forward mailbox user or queue record in CRM.
at Microsoft.Crm.Tools.Email.Providers.CrmPollingMailboxProvider.CheckForApprovedForwardMailboxUsersOrQueue(EmailMessage emailMessage)
at Microsoft.Crm.Tools.Email.Providers.ExchangePollingMailboxProvider.ValidateMessageInternal(EmailMessage emailMessage)
at Microsoft.Crm.Tools.Email.Providers.CrmPollingMailboxProvider.ValidateMessage(EmailMessage emailMessage)
at Microsoft.Crm.Tools.Email.Providers.CrmPollingMailboxProvider.Run()

Resolution

Step 1 -> Log in to CRM system and go to Settings.

Step 2 -> click business management and then Queues.

Step 3 -> select view called “Queues: Primary Email (Pending Approval)” , this will return relevant queues for which email address is not approved yet

Step 4: select all of them and hit Approval email button , this should resolve this issue.

Hope this helps

Regards,
MayankP 🙂

CRM 2011 Queue emails automatic contact creation issue

In CRM 2011, queues functionality is enhanced and changed as lot from CRM 4.0. You can read more about the changes in this article.

This current article is specific to behaviour with regarding to incoming email in to queue in CRM 2011. So if you received email in queue in CRM 2011 and if contact is not recognized by system automatically then it creates new contact automatically.

This is issue for one of customer because they did not want to end having lots of contacts created unnecessarily so we decided to turn off this feature for queues.

Solution

Following are steps to turn off this automatic feature in CRM 2011.

Step 1: Simply log on to CRM 2011 with administrative account (i.e. the account that was used to install CRM 2011 and CRM 2011 email router)

Step 2: go to File -> Options, this should open up Set Personal option page

Step 3: go to Email tab and un-tick option to automatically create records in CRM

Step 4: Click OK, Done!!

Hope this helps..

Cheers,
MayankP:)

CRM 2011 Custom workflow error

Problem

Recently we encounter following error coming from some of custom workflow in CRM 2011 organization.

Workflow paused due to error: Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Expected non-empty Guid.Detail: -2147220989 Expected non-empty Guid. 2012-02-23T10:15:53.6646892Z -2147220970 System.ArgumentException: Expected non-empty Guid. Parameter name: id 2012-02-23T10:15:53.6646892Z at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Retrieve(String entityName, Guid id, ColumnSet columnSet, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType) at Microsoft.Crm.Extensibility.InprocessServiceProxy.RetrieveCore(String entityName, Guid id, ColumnSet columnSet) at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.Retrieve(String entityName, Guid id, ColumnSet columnSet) at Microsoft.Crm.Workflow.Services.RetrieveActivityService.c__DisplayClass1.b__0(IOrganizationService sdkService) at Microsoft.Crm.Workflow.Services.ActivityServiceBase.ExecuteInTransactedContext(ActivityDelegate activityDelegate) at Microsoft.Crm.Workflow.Services.RetrieveActivityService.ExecuteInternal(ActivityContext executionContext, RetrieveEntity retrieveEntity) at Microsoft.Crm.Workflow.Services.RetrieveActivityService.Execute(ActivityContext executionContext, RetrieveEntity retrieveEntity) at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Solution

As per MS KB article this issue is fixed in CRM 2011 roll up 4, But for us even after applying roll up 6 this issue is not getting resolved.

so we have to update existing custom workflow to have a work around to fix this issue, following is more detail on this workaround.

So this error comes if you returning entity look up as return parameter from custom workflow and this error comes if your custom workflow returns null look up system throws above error.

To resolve this we put additional parameters called Dummy (Account GUID) as string and Success then pass test account record GUID as parameter.

[Input("Dummy")]
public InArgument Dummy { get; set; }

[Output("success")]
public OutArgument success { get; set; }

we also updated custom workflow code to return this dummy account if we are not able return correct account.


// find matching account
Guid accountId = FindAccount(crmService, perameterEmail, perameterName);

if (!accountId.Equals(Guid.Empty))
{
this.accountId.Set(executionContext, new EntityReference("account", accountId));
success.Set(executionContext, true);
}
else
{

//return dummy account

this.accountId.Set(executionContext, new EntityReference("account", new Guid(Dummy.Get(executionContext))));
success.Set(executionContext, false);
}

In workflow editor we just need to check if this returned success flag, if it is true then we got correct Id and if it is false then it is dummy account.

So this workaround resolves this issue not nice but effective.

Hope this helps..

Cheers,
MayankP:)

CRM 4.0 to CRM 2011 Upgrade Issue : The given key was not present in the dictionary

While importing CRM 4.0 organization to CRM 2011 environment we were kept getting error and error log is recording error as mentioned below..


InnerException:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.Crm.Metadata.PreloadedMetadataCacheDataProvider.AddEntityRelationshipRolesToCollections(IPreloadedMetadataInitializationContext context)
at Microsoft.Crm.Metadata.PreloadedMetadataCacheDataProvider.Initialize(IOrganizationContext organizationContext, MetadataContainer container, LoadMasks masks)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.CreateMetadataCacheDataProvider(MetadataContainer container, IOrganizationContext context, LoadMasks masks, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.BuildMetadataCacheFromMetadataContainer(MetadataContainer container, LoadMasks masks, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadCacheFromDatabaseInternal(LoadMasks masks, CrmDbConnection connection, CrmTransaction transaction, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadCacheFromDatabase(LoadMasks masks, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheFactory.LoadMetadataCache(LoadMethod method, CacheType type, LoadMasks masks, IOrganizationContext context)
at Microsoft.Crm.Setup.MetadataCacheLoadHelper.LoadCacheFromDatabase(CacheType existingMetadataCacheType, LoadMasks masks, String connectionString, Guid organizationId)
at Microsoft.Crm.Setup.DiffBuilder.PopulateExistingMetadataCacheFromDatabase(CacheType existingMetadataCacheType, String connectionString)
at Microsoft.Crm.Setup.DiffBuilder.Pass0(String metadataLocation)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.Crm.Setup.Database.DllMethodAction.Execute()
at Microsoft.Crm.Setup.Database.DatabaseInstaller.ExecuteReleases(ReleaseInfo releaseInfo, Boolean isInstall)
at Microsoft.Crm.Setup.Database.DatabaseInstaller.Install(Int32 languageCode, String configurationFilePath, Boolean upgradeDatabase, Boolean isInstall)
at Microsoft.Crm.Tools.Admin.InstallDatabaseAction.Do(IDictionary parameters)
at Microsoft.Crm.Setup.Common.CrmAction.ExecuteAction(CrmAction action, IDictionary parameters, Boolean undo)
InnerException:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.Crm.Metadata.PreloadedMetadataCacheDataProvider.AddEntityRelationshipRolesToCollections(IPreloadedMetadataInitializationContext context)
at Microsoft.Crm.Metadata.PreloadedMetadataCacheDataProvider.Initialize(IOrganizationContext organizationContext, MetadataContainer container, LoadMasks masks)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.CreateMetadataCacheDataProvider(MetadataContainer container, IOrganizationContext context, LoadMasks masks, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.BuildMetadataCacheFromMetadataContainer(MetadataContainer container, LoadMasks masks, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadCacheFromDatabaseInternal(LoadMasks masks, CrmDbConnection connection, CrmTransaction transaction, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadCacheFromDatabase(LoadMasks masks, IOrganizationContext context, CounterList counter)
at Microsoft.Crm.Metadata.DynamicMetadataCacheFactory.LoadMetadataCache(LoadMethod method, CacheType type, LoadMasks masks, IOrganizationContext context)
at Microsoft.Crm.Setup.MetadataCacheLoadHelper.LoadCacheFromDatabase(CacheType existingMetadataCacheType, LoadMasks masks, String connectionString, Guid organizationId)
at Microsoft.Crm.Setup.DiffBuilder.PopulateExistingMetadataCacheFromDatabase(CacheType existingMetadataCacheType, String connectionString)
at Microsoft.Crm.Setup.DiffBuilder.Pass0(String metadataLocation)

Solution

After investigating this issue further (and trying import multiple times) we guessed that issue is coming because CRM 4.0 organization had one of the unsupported customization applied on one of system entity relationship behaviour.

So first thing is to find out which entity relationship is causing this issue and once this is found then revert that changes in staging database (on top of fresh CRM 4.0 database before importing in to CRM 2011 as Organization).

We found this entity relationship and it is against “contact_as_responsible_contact” for this current Organization.

The way to find this out is compare your organization’s EntityRelationship table against standard out of the box CRM 4.0 EntityRelationship table.

After this we need to update problematic table records to match to system behaviour (CRM 4.0 out of box). We have run following script for the same.(please note schema name and ID would be different for your organization)

Update [MetadataSchema].[EntityRelationship]
set [IsCustomRelationship] =0,
[CustomizationLevel]=0
where SchemaName ='contact_as_responsible_contact'

Update [MetadataSchema].EntityRelationshipRelationships
set [CustomizationLevel] =0
where EntityRelationshipId ='ECE6333A-F356-42C5-BB26-B454DC0C0B33'

Update [MetadataSchema].EntityRelationshipRole
set [CustomizationLevel] =0
where EntityRelationshipId ='ECE6333A-F356-42C5-BB26-B454DC0C0B33'

Hope this helps..

Note: This is unsupported customization, Please unit test this module on your development/test environment before applying it to Live Environment…

Cheers,
MayankP:)