Tag Archives: Unsupported

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.

B6510

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.

B65112

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.

B65113

****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 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 4.0 to CRM 2011 Queues Upgrades

There are lots of improvements and enhancement with regards queue in CRM 2011 and they are mentioned in this article

Now if you migrated your organization from CRM 4.0 system the since for each user CRM 4.0 has two queues as mentioned below
1. User’s Private Queue (i.e. Assign Queue)
2. User’s WIP Queue (i.e. In Progress Queue folder)

While upgrading User’s Private Queue is converted as default queue and renamed user’s full name so if user is name mark smith, in CRM 2011 this user will have only one queue named with full user name

All WIP queue is migrated as it is but on them worker Id set to owning user, so since these were the items in user’s In progress queue in CRM 4.0 they are migrated with working user set as owning user.

If you match your queue details with standard CRM 2011 organization (which was not upgrade from CRM 4.0) then you will know that they have got only one named queue and do not have this additional WIP queue.

If users want to match up this queue experience for CRM 2011 environment (migrated from CRM 4.0 system) to new install of CRM 2011 environment then following ate recommended steps for the sae which are mentioned in following article. this article provides the details but does not provide tool or script to do the job, this post will provide tool and script code to achieve this.

http://msdn.microsoft.com/en-us/library/gg327885.aspx

1. Move all items from WIP queue to user’s default queue and set worker id as well
2. Delete or Deactivate the WIP queue

Now each user can manually do above task but if you got large user base then I guess it is better if system admin can do above task for the same.

Following two ways to achieve this,

Option 1: using CRM API to update relevant queue records (Supported)

I have created application which will move all current WIP queue item to user’s named queue and then disables the WIP queue.

This application is shared below.
CRM2011_WIPQueues

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

Step 1: after downloading CRM2011_WIPQueues_Upgrades_EXE.zip,extract the zip file.

Step 2: after extracting run the CRM2011_WIPQueues_Upgrades.exe

Step 3: provide CRM server details and then click on “Move Items“ button. Following are example screen print for the same.

One advantage of this application is it is supported but disadvantage is that it could take more then 2-3 hours if your CRM system got really large records in WIP queues but since we have run this only once I prefer this approach.

However there is another faster (but unsupported) method is mentioned below to achieve the same result.

Option 2: using SQL script **UNSUPPORTED**

System administrator can build SQL script to update all relevant queue item records to change queue id from WIP queue to user’s named queue.

Following example screen print for the script for the same for one user, this script can be downloaded from here and change the USER DOMAIN NAME for the relevant user in the following script. Of course this needs to be run for all users in the system.

-- find out all items present in relevant user's WIP queue
-- update user's domain name in query mentioned below
select * from filteredqueueitem
inner join filteredqueue on
filteredqueueitem.queueid = filteredqueue.queueid
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME'

--Update items in WIP queue to named queue
-- update user's domain name in query mentioned below
Update queueitembase
set queueid = (select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 2
and filteredsystemuser.domainname = 'USER DOMAIN NAME')
where queueid = (
select filteredqueue.queueid from filteredqueue
inner join filteredsystemuser on
filteredsystemuser.systemuserid = filteredqueue.ownerid
where queuetypecode = 3
and filteredsystemuser.domainname = 'USER DOMAIN NAME')

One advantage of this approach is that it will complete the moving with few minutes even if million records needs moving but big disadvantage is that we are making change to SQL directly which is considered as *UNSUPPORTED CHANGE * so if you are going for this route make sure you test this script fully on your development/UAT environment thoroughly before running them in to Live environment.

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:)

Getting View Details from CRM Grid (CRM 4.0 vs. CRM 2011)

We had custom application which uses were passing selected view id to our custom web page in CRM 4.0, following code we put in JavaScript button click event to retrieve this details.


if (top.stage.crmGrid != null)
{
var sViewId = top.stage.crmGrid.GetParameter('viewid');
var sViewType = top.stage.crmGrid.GetParameter('viewtype');
var sOtc = top.stage.crmGrid.GetParameter('otc');
}

Above code does not work in CRM 2011 and by doing really simple change as mentioned below, so following code works fine for CRM 2011..


if (document.getElementById('crmGrid') != null) {
var sViewId = document.getElementById('crmGrid').GetParameter('viewid');
var sViewType = document.getElementById('crmGrid').GetParameter('viewtype');
var sOtc = document.getElementById('crmGrid').GetParameter('otc');
}

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,
Mayank:)

CRM 2011 Changing CRM Active/Invoice Contract to Draft

I have written article how to do this in CRM 4.0 at this place,Following source code for similar class in CRM 2011.

CRM2011ContractUtilities

This comes as dcoument (.doc) but download and rename as .zip and it will have all source files. this was done because wordpress only allows these types to be uploaded.

Once downloaded steps are similair to CRM 4.0 Article but still mentioned below again for reference..

Step 1: Download the application files (as mentioned above rename .doc to .zip and extract the files) and register this assembly using plug in registration tool.

Step 2: Once assembly is registered successfully, go to setting and create new workflow against contract, make sure contract is owned by admin user who have access to modify the database tables.

Step 3: Call this workflow “Change Status to Draft” and add step from Contract utilities -> Change Status to Draft. (Or whatever group name, friendly name you put in plug in registration tool)

Step 4: provide input parameters (i.e. database connection to string)

Step 5: Now publish the workflow.

Step 6: go to contract screen and run this workflow against relevant active/invoiced contract record and this workflow will make contract draft. Now user can change any field on this contract or delete the contract now

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

Hope this helps!!!

Cheers,
MayankP:)