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

CRM Mail Merge Letter Printing Issues

Today we came across interesting problem regarding mail merge letter with regards to Dynamics CRM.

We were generating customer letter from Dynamics CRM Mail merge.

Our letter was four page letter and we had mail merge template setup and letter generated fine.

But while printing we wanted to print first page of company letter pad and other one on normal page. Well if you just print the mail merge generated letters then it consider the entire letter as one document and print them in just one print job.

So we used to get only first page of first letter on company letter page while others are printing normally which is not correct…

Well after much investigation (or should I say trial and error method) we found the solution and following steps to achieve the same..

1. Open Relevant word Document or Letter
2. Alt + F11 to open the Visual Basic Editor.
3. Module -> Insert Module
4. Copy following code and past in there…

Option Explicit
Sub PrintSectionsToSubDoc()
Dim i As Long
For i = 1 To ActiveDocument.Sections.Count
ActiveDocument.PrintOut Range:=wdPrintFromTo, From:="s" & i, To:="s" & i
Next i
End Sub

5. Close the Visual Basic Editor

6. Go to View -> Macros (or Tool-> Macro -> Macros for Office 2K3 ) and double click PrintSectionsToSubDoc()

Cheers,
Mayank 🙂

CRM Sales Order: where is mail merge?

This one just struck me that when creating new mail merge template Sales Order does not appear as entity in the list…

Following article will help you enable mail merge of any CRM entity.

Again note that this is unsupported way to add this sales order entity in to the list as mentioned in steps below…

Step 1: Export Sales Order Entity customization from CRM Environment

Step 2: Open Customization file and then Update (or Add) IsMailMergeEnabled as 1, Save the changes.

Step 3: Import the changed file in to CRM and publish the changes.

If above method does not work then try following unsupported method, connect database and run following query, change entity name as per your requirement..

Hope this helps..

Cheers,
MayankP 🙂

CRM Data Generator

Just came across requirement where we needed to generate lots of data in CRM system..

Microsoft Dynamics CRM Demonstration Tools (for Microsoft Dynamics CRM 4.0) provides lots of functionality and one of them is Generate Bulk Data for any CRM entity with just few clicks…see below steps for the same..

Step 1: Connect to your CRM Server

This article provides details on how to connect your CRM server on CRM Demo tools.

Step 2: Click on Data Generator tab and select relevant entity and then add relevant data against relevant attribute

Step 3: Select quantity and hit generate data button and this will generate this button.

Other then Data generator this tool also provides functionality to
• Change the Dates and/or Times for Data
• Create Dependent Picklists
• Generate E-Mail
• Set Icons for Entities
• Change the CRM Navigation Pane
• Replace Strings in CRM

Hope this helps…

Regards,
MayankP:)