Using Virtual Entities to query metadata

June 14, 2018September 13, 2018 Natraj Yegnaraman dynamics365, opensource, tricks, virtualentity

After my previous post, I continued to explore virtual entities to see what real life problems I can solve using them. One problem I could think of was metadata. How awesome would it be, if I can use Advanced Find to query entity and attribute metadata, or visualise them as a normal entity! It is not a dream anymore. I have developed an open-source solution to do this.

Here is some of the sample queries:

Query all attributes of type customer

Query by Attribute Type

Query by Attribute Type Results

Query all mandatory attributes

Query All Required.png

Query All Required Results.png

Query by Attribute Type and Entity

Query by Attribute Type and Entity.png

Query by Attribute Type and Entity Results.png

Query all Many to Many intersect entities

Query MM Entity.png

Query MM Entity Results

Query entities that have quick create enabled

Query Quick Create

Quick Create Results

You can open the entity and see more details.

Entity Form

You can open the attribute and view more details as well.

Attribute Form

All this is awesomeness is possible using the power of virtual entities. There are two virtual entities that you can query. They are called Entity and Attribute.

VE Solution

You can download the source code and managed solution from https://github.com/rajyraman/Metadata-Virtual-Entity/releases.

This step is important

After importing the managed solution, change the data source for the attribute entity from “Entity Datasource” to “Attribute Datasource”. You have to do this from the Customization area and not from the managed solution.

Change Datasource.png

This is because by default, the system does not allow relationships between two virtual entities that have different datasources. This exception is shown when you try to do this.

Solution Exception

Stack Trace.png

In order to workaround this exception, you keep the data source same for the “Entity” virtual entity (parent) and “Attribute” (child) virtual entity, create the relationship and then change to the right datasource. Hence, the managed solution has the datasource set to “Entity Datasource” for the “Attribute” virtual entity, which has to be changed manually after importing the solution.

I hope this solution would be really useful for administrators. Please let me know any feedback on the post or on GitHub issues.

Sourcecode: https://github.com/rajyraman/Metadata-Virtual-Entity

Managed Solution: https://github.com/rajyraman/Metadata-Virtual-Entity/releases/latest

Virtual Entities for tracking recently used items

May 19, 2018 Natraj Yegnaraman dynamics365, plugin, tricks

Virtual entities is a powerful feature that can be used not only to bring data from external sources, but also from inside Dynamics CRM/Dynamics 365 Customer Engagement.

Jason Lattimer already has a post (https://jlattimer.blogspot.com.au/2017/12/creating-custom-virtual-entity-data.html) that goes through how to setup the custom datasource/data provider. So, go and read that first as it has all the screenshots and I would be duplicating the content, if I go through the steps again.

Gotcha 1:

There is a exception when you create the datasource.

Datasource Creation Error

You can simply ignore this and refresh the Plugin Registration tool.

Gotcha 2: If you don’t want the user the open up an individual record, you don’t have to implement Retrieve message. It is optional. Since, I just want a collated entity, I did not register any plugin for the Retrieve.

Data Provider

Gotcha 3: You have to open up the newly created Data Provider entity, and enter the external name. If you don’t enter this, you will be unable to create the data source, as it will always error out.

Data Source Primary Key Attribute

Create New Data Source

Data Source List

Objective: MRU items should be accessible from Advanced Find. As an Administrator, I would like to query this data, and see metrics around user participation, entity usage, activity by week/month/year etc.

This is the Most Recently Used area.

Export all attachments using LINQPad

January 18, 2018January 18, 2018 Natraj Yegnaraman crm2016, dynamics365, linqpad, tricks

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

Using Chrome DevTools to debug Bot Framework

June 21, 2017 Natraj Yegnaraman bots, chrome, javascript, tricks

When debugging the Bot Framework it is trivial to debug if you are developing in C#. It was bit of pain for me to do the same is node, as JavaScript is not my everyday language. But, after ~~researching~~ googling about this, I can say that it is really easy in node as well.

Below are the version details that I tried this on:

node: 8.1.2
npm: 5.0.3
OS: Windows 10
Chrome: Canary 61.0.3137.0
Bot Framework Emulator: 3.5.29

Below is my package.json for a simple “Hello World” bot.

{
  "name": "echo-bot",
  "version": "0.1.0",
  "license": "MIT",
  "description": "Echo bot example",
  "scripts": {
    "start": "node --inspect app.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "dependencies": {
    "botbuilder": "^3.8.4",
    "dotenv": "^4.0.0",
    "restify": "^4.3.0"
  }
}

Since I am developing this in Visual Studio Code, this is my tasks.json

{
    // Use IntelliSense to learn about possible Node.js debug attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "type": "node",
            "request": "launch",
            "cwd": "${workspaceRoot}",
            "runtimeExecutable": "npm",
            "runtimeArgs": [
                "start"
            ],
            "name": "Launch Program"
        }
    ]
}

Now, to run the bot application in VSCode using Ctrl+F5 or typing “npm start” in the command prompt.

To debug the bot in Chrome DevTools, type “chrome://inspect/#devices“. Then click the “Open dedicated DevTools for Node” link. The page should be like this once you do this.

Chrome DevTools to debug Bot Framework.png

Chrome will now display the node application that it can detect in the Remote Target area. Click in the inspect link to open the DevTools. Now, set your breakpoint and use the emulator to send a message.

Debug botframework source

In the screenshot above you can see that I am already debugging using Chrome DevTools and debugger is waiting in the breakpoint that I set.

Reference:

https://nodejs.org/en/docs/inspector/

User not found error

June 16, 2017 Natraj Yegnaraman crm2016, tricks, unsupported

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

May 25, 2017 Natraj Yegnaraman crm2016, dynamics365, serversidesync, tricks, troubleshooting, xrmtoolbox

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

Install FetchXML Builder from XrmToolBox store, if you don’t have it already
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 Builder
Next step is to open this view in FetchXML Builder
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 criteria

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:

Executing large FetchXML with WebAPI

January 15, 2017 Natraj Yegnaraman crm2016, dynamics365, javascript, tricks

You can easily execute fetchxml in WebAPI using the “fetchXml” query parameter. But this “GET” method won’t work, if the fetchxml is too big. In this case, you have to use the “POST” method to execute the fetchxml.

Sample Request Header:

Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Content-Type: multipart/mixed;boundary=batch_contactfetch

Sample Request Body:

--batch_contactfetch
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://[CRM URL]/api/data/v8.2/contacts?fetchXml=<fetch count="10" ><entity name="contact" ><attribute name="fullname" /></entity></fetch> HTTP/1.1
Content-Type: application/json
OData-Version: 4.0
OData-MaxVersion: 4.0

--batch_contactfetch--

Sample Code:

var req = new XMLHttpRequest();
req.open("POST", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/$batch", true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "multipart/mixed;boundary=batch_contactfetch");
req.onreadystatechange = function() {
    if (this.readyState === 4) {
        req.onreadystatechange = null;
        if (this.status === 200) {
            var response = JSON.parse(this.response.substring(this.response.indexOf('{'),this.response.lastIndexOf('}')+1));
			console.log(response.value);
        } else {
            Xrm.Utility.alertDialog(this.statusText);
        }
    }
};

var body = '--batch_contactfetch\n'
body += 'Content-Type: application/http\n'
body += 'Content-Transfer-Encoding: binary\n'
body += '\n'
body += 'GET ' + Xrm.Page.context.getClientUrl()+'/api/data/v8.2/contacts?fetchXml=<fetch count="10" ><entity name="contact" ><attribute name="fullname" /></entity></fetch> HTTP/1.1\n'
body += 'Content-Type: application/json\n'
body += 'OData-Version: 4.0\n'
body += 'OData-MaxVersion: 4.0\n'
body += '\n'
body += '--batch_contactfetch--'

req.send(body);

Request Screenshot

Fetch Post Request.png

Response Screenshot

fetch-post-response

Dreaming in CRM

My thoughts on Dynamics CRM/365, JavaScript, C# and Software Development. Sometimes, I post when I am half-asleep

tricks