Tag Archives: Tips & Tricks

CRM AUDITING: SQL Database Standard to Enterprise version

So on MS CRM Audit Log screen we could not see any partition details available even though (as shown in screen print below) even though we are using enterprise version of SQL.

B102082016_1

We also realized that our audit table grown very large and was started impacting performance as well, during investigation it is revealed that MS CRM organization was created from back up which was taken from standard SQL version.

So when new organization import process run then CRM system did not created these partition so we contacted Microsoft support and they provided following SQL script for this.

After running script we started seeing CRM Audit partition in the system now.

B102082016_2

CRM AUDIT: SQL enterprise to standard version

if your requirement is other way around so your current CRM SQL DB is enterprise version and if you want to migrate to standard SQL version then follow the steps provided in this KB article to restore DB and then after this perform normal import organization operation in CRM for this.

Hope this helps…

Cheers,
MayankP:)

 

 

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

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 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 Workflow (i.e. Process) Editor: Invalid Condition Expression Issue: Solution

Sometimes (for various reasons) when user tries to update CRM 2011 workflow, we see it displays Red cross mark and says it’s Invalid Condition Expression as shown below.
This post will provide small trick to resolve this.

Solution

I recently found small workaround to fix this issue this is to add any new condition and then just delete the same condition and this resolve the issue as shown below.

Step 1: in the workflow above or below add condition at any of exisitng step as shown below

Step 2: This will fix this issue and then just delete the condition added in above step.

If issue still persists then this mean of the steps got wrong parameter so please fix this particular step and it will fix this issue.

Hope this helps…

Cheers,
MayankP 🙂

CRM 4.0 Error “Failure: Relationshipname : Invalid Argument”

Recently while importing customization from one environment to another I started getting following exception.

“Failure: : Invalid Argument”

we also checked logged error on Event log and this import was logging following error..

“Customization Import failed. Error: Attribute Display Name not specified”

After few trial and error we were able to resolve the issue.

Resoution

There are two ways to resolve this.
1. Make sure all entities are published on source system and then extract entities again and then try putting in to destination system and see if this works…
(This method is preferred)

2. Other method is to export only entities which had issue while importing on destination. Then open Export XML and then locate relevant field name and that field name will have tag. If you update this tag with some text then that should also resolve this issue.
(see below screen print)

Hope this helps..

Cheers,
MayankP:)

CRM 4.0 Adding Marketing List Member issue

Issues description

Recently we started getting following error while adding data marketing list member.
“There is a problem communication with the Microsoft Dynamics CRM server. The server might be unavailable”.

Solution

After few trial and error found that this error is coming because some of the record owner (System users) are disabled so if you select only records owned by enabled user then this works fine…

Hope this helps…

Regards,
MayankP:)