Export all attachments using LINQPad

I was playing around with LINQPad today and wrote this C# code to export all attachments from CRM. You can customise the query to export only certain attachments if required. You could also modify the code to gather the output location from the user, instead of asking them to choose between “My Documents” or “Desktop”. This could also be potentially written as an XrmToolBox tool.

I executed the code in LINQPad v5.26 and Dynamics CRM 2016 OnPremise 8.1 environment. I tried to retrieve the attachment using LINQ, but decided to use normal QueryByAttribute with paging for performance reasons.

Util.RawHtml("
<h4>Choose an output path</h4>
").Dump();
var folders = new List<Environment.SpecialFolder> { Environment.SpecialFolder.Desktop, Environment.SpecialFolder.MyDocuments };
folders.ForEach(x => new Hyperlinq(() => DumpFiles(Environment.GetFolderPath(x)), x.ToString()).Dump());

void DumpFiles(string selectedFolder)
{
	Util.ClearResults();
	new Hyperlinq(selectedFolder).Dump("Chosen output path");
	var progress = new Util.ProgressBar("Writing files: ").Dump();
	progress.HideWhenCompleted = true;
	var retrieveQuery = new QueryByAttribute("annotation")
	{
		ColumnSet = new ColumnSet("documentbody","filename"),
		PageInfo = new PagingInfo{ Count = 500, PageNumber = 1 }
	};
	retrieveQuery.AddAttributeValue("isdocument", true);
	var resultsDc = new DumpContainer().Dump($"Results");
	EntityCollection results;
	int totalRecordCount = 0;
	do
	{
		results = ((RetrieveMultipleResponse)this.Execute(new RetrieveMultipleRequest { Query = retrieveQuery })).EntityCollection;
		var files = results.Entities.Cast<Annotation>();
		totalRecordCount += results.Entities.Count;
		resultsDc.Content = $"Completed Page {retrieveQuery.PageInfo.PageNumber}, Files: {totalRecordCount}";
		int fileNumber = 0;
		foreach (var f in files)
		{
			fileNumber++;
			var fileContent = Convert.FromBase64String(f.DocumentBody);
			File.WriteAllBytes(Path.Combine(selectedFolder, f.FileName), fileContent);
			progress.Caption = $"Page {retrieveQuery.PageInfo.PageNumber} - Writing files: {fileNumber}/{retrieveQuery.PageInfo.Count}";
			progress.Percent = fileNumber * 100 / retrieveQuery.PageInfo.Count;
		}
		retrieveQuery.PageInfo.PageNumber++;
		retrieveQuery.PageInfo.PagingCookie = results.PagingCookie;
	} while (results.MoreRecords);
	resultsDc.Content = $"{totalRecordCount} files saved.";
}

LINQPad Annotation Export User Input.png

LINQPad Annotation Export.png

Advertisements

Cancelling save event based on the result of async operation

EDIT (06/02/2018):  Tanguy reported a scenario where the original code did not work when the user did a “saveandclose” instead of “save”. I have updated the code to handle this scenario. The uses the jQuery library on the parent frame to do the deep clone, but you could very well do the same using lodash clonedeep so that you don’t have to rely on CRM’s jQuery to do the job.

When you want to cancel a save event in CRM Dynamics 365 Customer Engagement, you use “preventDefault()”  to block the save operation. This works when you block the operation based on the information that is currently on the form/page, but it does not work, if you want to block the save based on the result of an async operation.

In this contrived example, I would like to block the save of the current form, if there exists an user with “homephone” field set to 12345. The async operation is performed by “retrieveMultipleRecords” which returns a Promise.

The code below does not work

Xrm.Page.data.entity.addOnSave((e)=>{
	Xrm.WebApi.retrieveMultipleRecords('systemuser','$select=fullname,jobtitle,homephone').then(x=>{
		console.log(`DataXml OnSave: ${Xrm.Page.data.entity.getDataXml()}`);
		if(x.entities.some(x=>x.homephone == '12345')){
			e.getEventArgs().preventDefault();
			console.log('User with homephone 12345 exists. Save blocked.');
		}
	});
});

Result

Notice the the save event completed and form’s load event fired even though preventDefault ran. The “jobtitle” field that I modified also succeeded, when I expected it to not succeed.

Async Save block does not work

In order to block the save, you’ll have to restructure the code little differently, like the one below. Block save before async operation and explicitly call save, when your criteria for save is met and use closure variable to keep track of whether to save or not.

Working code

Xrm.Page.data.entity.addOnSave((()=>{
	let isSave = false;
	var uiClone = parent.jQuery.extend(true, {}, Xrm.Page.ui);
	var entityClone = parent.jQuery.extend(true, {}, Xrm.Page.data.entity);

	var closeHandler = ()=>{
		console.log('local. close blocked.');
	};

	var saveHandler = (ev)=>{
			console.log('local. save blocked.');
			Xrm.WebApi.retrieveMultipleRecords('systemuser','$select=fullname,jobtitle,homephone').then(x=>{
				isSave = !x.entities.some(x=>x.homephone == '12345');
				if(isSave){
					Xrm.Page.data.entity.save = entityClone.save;
					Xrm.Page.ui.close = uiClone.close;
					if((typeof ev === 'string' && ev === 'saveandclose') ||
						(ev.getEventArgs && ev.getEventArgs() && ev.getEventArgs().getSaveMode() === 2)){
						console.log('saveandclose');
						entityClone.save('saveandclose');
					}
					else{
						console.log('save');
						entityClone.save();
					}
				}
				else{
					console.log('User with homephone 12345 exists. Save blocked.');
				}
			});
	};

	return (e)=>{
		var eventArgs = e.getEventArgs();
		console.log(`DataXml OnSave: ${Xrm.Page.data.entity.getDataXml()}`);
		console.log(`Save Mode: ${eventArgs.getSaveMode()}`);
		if(isSave) {
			console.log('proceed to save');
			Xrm.Page.data.entity.save = entityClone.save;
			Xrm.Page.ui.close = uiClone.close;
			return;
		}
		else{
			Xrm.Page.data.entity.save = saveHandler;
			Xrm.Page.ui.close = closeHandler;
			if(eventArgs.getSaveMode() !== 2){
				eventArgs.preventDefault();
			}
			saveHandler(e);
		}
	}
})());

Result

Console Log Save Blocked

I have tested this only in Chrome on Dynamics 365 Online v9. Hope this is useful.

Bug: Appointment organizer not being set

This bug was one the toughest for me to identify and implement a workaround as

  • It happens only one time in each session
  • Quick glance at the form during create does not provide any clues regarding the bug

The bug is this: When a new appointment is saved, the organizer field is not set even though it shows the field being populated during form load and save.

Here is how the appointment form looks when you create a new appointment:

Organizer

The organizer field appears to be populated, but it is not. This is the dataxml:

<appointment>
	<instancetypecode>0</instancetypecode>
	<prioritycode>1</prioritycode>
	<scheduledstart>2017-07-07T11:30:00</scheduledstart>
	<ownerid type="8" name="Natraj Yegnaraman">{40327227-A570-E611-80FA-005056A6A11C}</ownerid>
	<scheduleddurationminutes>30</scheduleddurationminutes>
	<isalldayevent>0</isalldayevent>
	<scheduledend>2017-07-07T12:00:00</scheduledend>
	<statuscode>5</statuscode>
	<transactioncurrencyid name="Australian Dollar" type="9105">{F0B901CE-7692-E211-82F1-0050569B4EC3}</transactioncurrencyid>
	<isbilled name="">false</isbilled>
	<ismapiprivate name="">false</ismapiprivate>
	<attachmenterrors name="">0</attachmenterrors>
	<isworkflowcreated name="">false</isworkflowcreated>
</appointment>

Compare this with the dataxml when the organizer field is really populated:

<appointment>
	<instancetypecode>0</instancetypecode>
	<organizer>
		<activityparty>
			<partyid type="8" name="Natraj Yegnaraman">{40327227-A570-E611-80FA-005056A6A11C}</partyid>
		</activityparty>
	</organizer>
	<prioritycode>1</prioritycode>
	<scheduledstart>2017-07-07T11:30:00</scheduledstart>
	<ownerid type="8" name="Natraj Yegnaraman">{40327227-A570-E611-80FA-005056A6A11C}</ownerid>
	<scheduleddurationminutes>30</scheduleddurationminutes>
	<isalldayevent>0</isalldayevent>
	<scheduledend>2017-07-07T12:00:00</scheduledend>
	<statuscode>5</statuscode>
	<transactioncurrencyid name="Australian Dollar" type="9105">{F0B901CE-7692-E211-82F1-0050569B4EC3}</transactioncurrencyid>
	<isbilled name="">false</isbilled>
	<ismapiprivate name="">false</ismapiprivate>
	<attachmenterrors name="">0</attachmenterrors>
	<isworkflowcreated name="">false</isworkflowcreated>
</appointment>

Also, notice that the Organizer field does not have a person icon next to the name, like the Owner field.

This is fix I implemented to workaround the issue:

	var organizer = Xrm.Page.getAttribute('organizer').getValue();
	if(Xrm.Page.ui.getFormType() === 1 && !organizer){
		Xrm.Page.getAttribute('organizer').setValue(Xrm.Page.getAttribute('ownerid').getValue());
	}

The script basically runs on form save provided it is a new record, and copies the Owner lookup value to Organizer lookup, if the Organizer lookup does not contain any value.

The reason the Organizer field is important is because, without this being set, that appointment won’t show up in the appointment creator’s Outlook  (even though they created it and they are also the owner).

This issue is happening in 8.1.0 and has been possibly fixed in 8.2.0.

Bug: Email replies with default signature

There appears to be a bug that affects this email replies when:

  • The email was created in CRM2015 and org was upgraded to CRM2016 OR the email was received from an external party AND
  • The user replies to the email from CRM2016/Dynamics 365

Email Signatures were added to CRM in 8.1. This feature enables an user to quickly setup their email signature(s). The user also has the capability to setup an email signature as default, so that it is automatically inserted into the email body. The bug is in the functionality.

When the user creates a new email, CRM always inserts div tag with id signature.

Email Signature New Email

Why does it insert this tag even though no signatures have been setup yet? It serves as a marker to know where to insert the signature in the reply: whether in the top or in the bottom.

When the user replies to an email which has a div#signature, then everything is fine. The signature is appended to the top of the email.

Email Signature div id signature exists

This is because when CRM sees that the user is trying to reply to an email which has a div#signature, it modifies the id to “oldsignature”, and appends a new div#signature to the DOM.

Email Signature oldsignature div.png

If the incoming email does not have div#signature in the DOM, CRM does not insert any div#signature to the reply at all. Even if you manually click, the “Insert Signature” button in the command bar, it inserts the signature to the bottom of the email which is not correct.

Email Signature in the bottom of reply.png

Resolution: Nothing at the moment, till MS fixes this issue.

User not found error

Word of warning: This change is unsupported as it involves editing the CRM database directly. Use it at your own risk.

I encountered a weird error in CRM, that basically said that the user cannot be found. When I checked the event log, this appeared to be the underlying exception:

Exception type: CrmException
Exception message: No Microsoft Dynamics CRM user exists with the specified domain name and user ID
at Microsoft.Crm.Authentication.Claims.AuthenticationProvider.GetOrganizationId(ClaimsPrincipal principal)
at Microsoft.Crm.Authentication.Claims.AuthenticationProvider.Authenticate(HttpApplication application)
at Microsoft.Crm.Authentication.AuthenticationStep.Authenticate(HttpApplication application)
at Microsoft.Crm.Authentication.AuthenticationPipeline.Authenticate(HttpApplication application)
at Microsoft.Crm.Authentication.AuthenticationEngine.Execute(Object sender, EventArgs e)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

But, the user was indeed there from before and all I did was reactivate a long dormant user. My initial suspicion was that this had something to do with AD. I ran the following query to check the SID of the user against the MSCRM_CONFIG database:

select c.FriendlyName,b.FullName,d.AuthInfo,b.DomainName
from SystemUserOrganizations a
inner join [ORG_MSCRM].dbo.SystemUser b on a.CrmUserId=b.SystemUserId
inner join Organization c on c.Id=a.OrganizationId
inner join SystemUserAuthentication d on d.UserId=a.UserId
where b.DomainName='CONTOSO\powerm'

This gave me the SID of the user when the user was first created in CRM. I next ran this command in to get the current SID of the user:

wmic useraccount where name='powerm' get sid

The SID did not match to the one already in CRM, so I had to update it in the MSCRM_CONFIG database:

update d
set d.AuthInfo='W:[SID OUTPUT FROM THE DOS COMMAND PROMPT]'
from SystemUserOrganizations a
inner join [ORG_MSCRM].dbo.SystemUser b on a.CrmUserId=b.SystemUserId
inner join Organization c on c.Id=a.OrganizationId
inner join SystemUserAuthentication d on d.UserId=a.UserId
where b.DomainName='CONTOSO\powerm'

Once I updated this, the user was able to login with the same AD account.

Server Side Sync: View for Synced and Not-Synced Appointments

If you decide not to use the CRM App for Outlook, syncing emails, contacts and appointments using just Server Side Sync and Forward Mailbox can be really painful sometimes, especially when you are troubleshooting why something is not being synced. The “Server-Side Synchronization Monitoring” Dashboard provides some assistance to the Administrator, but when you want to dig into the details, I often end up using “FetchXML Builder” or “LinqPad” to query the TraceLog entity. Below is the fetchxml I use

<fetch top="50" >
  <entity name="tracelog" >
    <attribute name="tracestatus" />
    <attribute name="tracedetailxml" />
    <attribute name="tracelogid" />
    <attribute name="machinename" />
    <attribute name="tracecode" />
    <attribute name="traceactionxml" />
    <attribute name="traceparameterxml" />
    <attribute name="traceparameterhash" />
    <attribute name="errortypedisplay" />
    <attribute name="modifiedon" />
    <attribute name="text" />
    <attribute name="level" />
    <attribute name="collationlevel" />
    <filter>
      <condition attribute="tracestatus" operator="eq" value="0" />
    </filter>
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

Another common scenario that I troubleshoot is, why an appointment is not synced to Outlook after it was created in CRM. The first thing to check in this case is the user’s sync filter. I use the XrmToolBox tool “Sync Filter Manager” to check what the user’s sync filter for appointment. After confirming that the fetchxml picks up the appointment that has to be synced, we can now proceed to stage 2 of troubleshooting.

The field that is key in this scenario is called “GlobalObjectId“. This field in the appointment entity will be set, after the appointment is synced to the user’s Outlook. Unfortunately, this field is marked as not searcheable, which means it cannot be used in Advanced Find.

Global ObjectId.png

But, we can still create view that shows the synced appointments and appointments that have not been synced, using FetchXML Builder. Below are the steps

  1. Install FetchXML Builder from XrmToolBox store, if you don’t have it already
  2. Save an existing view into a new view on appointment entity. Don’t worry about the filters yet as we will update this using FetchXML BuilderSave a new view.png
  3. Next step is to open this view in FetchXML BuilderOpen View.png
  4. Update the fetchxml and save the view. Confirm the user’s sync filter for appointment using the Sync Filter Manager and make sure the new fetchxml criteria match with the user’s sync filter criteriaSave View.png

Synced Appointments – FetchXML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
  <entity name="appointment" >
    <attribute name="subject" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="regardingobjectid" />
    <attribute name="prioritycode" />
    <attribute name="activityid" />
    <attribute name="instancetypecode" />
    <attribute name="location" />
    <order attribute="createdon" descending="true" />
    <filter type="and" >
      <condition attribute="scheduledstart" operator="not-null" />
      <condition attribute="instancetypecode" operator="neq" value="2" />
      <condition attribute="scheduledend" operator="not-null" />
      <condition attribute="globalobjectid" operator="not-null" />
    </filter>
    	<link-entity name="activityparty" from="activityid" to="activityid" alias="ad" >
      <filter type="and" >
        <condition attribute="participationtypemask" operator="ne" value="9" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Not Synced Appointments – FetchXML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
  <entity name="appointment" >
    <attribute name="subject" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="regardingobjectid" />
    <attribute name="prioritycode" />
    <attribute name="activityid" />
    <attribute name="instancetypecode" />
    <attribute name="location" />
    <order attribute="createdon" descending="true" />
    <filter type="and" >
      <condition attribute="scheduledstart" operator="not-null" />
      <condition attribute="instancetypecode" operator="neq" value="2" />
      <condition attribute="scheduledend" operator="not-null" />
      <condition attribute="globalobjectid" operator="null" />
    </filter>
    	<link-entity name="activityparty" from="activityid" to="activityid" alias="ac" >
      <filter type="and" >
        <condition attribute="participationtypemask" operator="ne" value="9" />
      </filter>
    </link-entity>
  </entity>
</fetch>

You should now be able to use these new view from the grid area.

Synced Appointments.png

If you try to use the view from Advanced Find, you will get this error.Advanced Find.png

This means you have to use FetchXML Builder to update you fetchxml, if any changes are required in the future. If you want to add more columns to the view, you’ll have to use “View Designer” to do that, as you would not be able to use Advanced Find to do this, because of this error.

I hope this post will help you troubleshoot future appointment sync issues.

Tools used:

  1. LinqPad
  2. Dynamics CRM LinqPad Driver
  3. XrmToolBox
  4. FetchXML Builder
  5. View Designer
  6. Sync Filter Manager

CRM Upgrade – Network name cannot be found error

When you follow the “lift and shift” upgrade method i.e. backup the old CRM database, restore it in the target environment and run deployment manager to import the organisation database, upgrade should be relatively easy. But, recently I experienced an issue that was such a pain to troubleshoot, I thought I will share this experience so that it will be helpful to others.

Below is the error I got when I import the organisation database using Deployment Manager.

Deployment Manager Error

This is what is captured in the deployment manager log (%AppData%\Roaming\Microsoft\MSCRM\Logs)

14:36:39|   Info| CrmAction execution time; UpgradeDatabaseAction; 00:04:00.9426595
14:36:39|   Info| Executing Install action: Microsoft.Crm.Tools.Admin.InstallSqlClrHelperAction
14:36:39|  Error| System.Exception: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallSqlClrHelperAction —> System.IO.IOException: The network name cannot be found.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite, Boolean checkHost)
   at Microsoft.Crm.Setup.Database.Helpers.CopyDatabaseFile(String sourcePath, String sourceFileName, Boolean isBuildEnvironment, String sqlServerName, String destinationFilePath, String destinationFileName, String& copyDestinationPath)
   at Microsoft.Crm.Tools.Admin.InstallSqlClrHelperAction.Do(IDictionary parameters)
   at Microsoft.Crm.Setup.Shared.CrmAction.ExecuteAction(CrmAction action, IDictionary parameters, Boolean undo)
   — End of inner exception stack trace —, Error, AbortRetryIgnore, Option1
14:37:38|   Info| InputResult: Retry

It looks like the database server name is wrong. But it is not. I used “Process Monitor” to troubleshoot the error. Here is what “Process Monitor” picked up.

Process Monitor Error

As you can see, the underlying error is a caused to lack of permissions to copy a dll file to a UNC path pointing to the database server.

Cause: McAfee Antivirus was blocking copy of any dlls through UNC path.

Resolution: Turn off “On Access Scanner” and “Access Protection” features of McAfee Antivirus during the organisation import.

In my case the KB article for SqlServerPathOverrides caused me to waste more time. I still had the same issue even after creating the network share, as McAfee was blocking all dll copy operations to UNC paths.