January 2021 – Power Platform & Dynamics 365

Total Number of Record : RetrieveTotalRecordCount

A very common request by customer is to get total number of record in D365 system for a given entities. It is pretty straight forward for D365 On Prem, you can do a count on SQL DB. But for D365 online you either need to rely on XrmToolBox fetch counter(which can still be used) or aggregrate function or even on DES database. However we do have a simple way now : RetrieveTotalRecordCount

I have all the entities in my system stored in a text file. The C# code to get count of all the entities will be like this:

            string[] entitylist = File.ReadLines(@"D:\entitylist.txt").ToArray();
            var client = new CdsServiceClient(connectionString);// I am using .NET core. Change it to var client = new CrmServiceClient(connectionString);           
            RetrieveTotalRecordCountRequest req = new RetrieveTotalRecordCountRequest
            {
                EntityNames = entitylist
            };
            EntityRecordCountCollection m = ((RetrieveTotalRecordCountResponse)client.Execute(req)).EntityRecordCountCollection;
            long count = 0;
            foreach (var i in m)
            {
                Console.WriteLine(i.Key + " =" + i.Value);
                count += i.Value;
            }
            Console.WriteLine(@$"Count ={count}");

I was having around 600 entitites with 200 million records in total and it took 2 second to get the result. So pretty sure its doing SQL count underneath.

If you are using POSTMAN – just use below url

GET {{webapiurl}}/RetrieveTotalRecordCount(EntityNames=[‘contact’,’account’])

and it ifs JS then just change pass the same request as above using webAPI.

function GetTotalCount(executionContext)
{
	var formContext = executionContext.getFormContext();
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/RetrieveTotalRecordCount(EntityNames="+JSON.stringify(['contact','account']) +")", true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function() {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
                for (var i = 0; i < results.EntityRecordCountCollection.Count; i++) {
                    var value = results.EntityRecordCountCollection.Values[i];
                    alert(results.EntityRecordCountCollection.Keys[i] + " = "+ value);
                }
            } else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }
    };
    req.send();
}

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/retrievetotalrecordcount?view=dynamics-ce-odata-9

We still dont have a way to apply a filter and then get the count directly. Hoping to get this feature in future.

Importing Resolved Cases using OOB Import in D365

So recently I recieved a query that importing cases record with Completed status wasnt working using OOB import. This was for data migration and its pretty common to migrated closed cases for historic purposes.

So D365 does allow importing inactive record for certain entity – like account.

Account

Below is an example of sample :

If you see the Audit however, you can see that it is a two step process – where record gets created in active stage and then gets deactivated.

Case

Lets consider incident entity now. To reoslve a case from UI, you need to fill these details. This in turn creates a case resolution entity record which then closes the cases. So to reoslve a case we need to import Case Resolution entity.

Please note that cases can be imported in cancelled state directly – it is only the resolve status that isn’t allowed to be imported directly.

When we to import below file, the same behaviour isn’t repeated:

However, the import partially succeed with proper error message:

To resolve the imported cases, we need to import similar file to this entity. What is will do is create a similar file in below format and import it.

D365 does NOT provide a template for Case Resolution so you need to import it as a CSV.

This will then resolve the two cases Incident 8 and Incident 11. You can add any other requirted field like Actual End Date – all those will work fine.

Please note that Dynamics is not considering the status reason field while importing and it just takes the default status reason field for given status.

Qualify Lead using Web API with Existing Contact

If you want to qualify a lead using WebAPI, the message is Microsoft.Dynamics.CRM.QualifyLead.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/qualifylead?view=dynamics-ce-odata-9

If you dont want to create account/contact but in turn link it with existing customer – you can use something like this:

POST https://<org>.crm.dynamics.com/api/data/v9.0/leads(<leadid>)/Microsoft.Dynamics.CRM.QualifyLead

BODY:

{
   "CreateAccount":false,
   "CreateContact":false,
   "CreateOpportunity":true,
   "OpportunityCustomerId":{"@odata.type":"Microsoft.Dynamics.CRM.contact","contactid":"34289935-9A54-EB11-A812-000D3A8B898C"},
   "Status":-1,
   "SuppressDuplicateDetection":true
}

I was however not able to pass both account and contact – so if the lead has already ‘Existing Account’ field filled and you pass the contact in webAPI call, then you will get both account and contact filled in the opportunity record.

function qualifyLeadwithContact(leadId, contactid, clientUrl) {
    try {
        var request = "leads(" + leadId.replace("}", "").replace("{", "") + ")/Microsoft.Dynamics.CRM.QualifyLead";
        var postdata = {
			"CreateAccount":false,
			"CreateContact":false,
			"CreateOpportunity":true,
			"OpportunityCustomerId":{"@odata.type":"Microsoft.Dynamics.CRM.contact","contactid":contactid},
			"Status":-1,
			"SuppressDuplicateDetection":true
				};
        var req = new XMLHttpRequest();
        req.open("POST", clientUrl + "/api/data/v9.0/" + request, true);
        req.setRequestHeader("Accept", "application/json");
        req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
        req.setRequestHeader("OData-MaxVersion", "4.0");
        req.setRequestHeader("OData-Version", "4.0");

        req.onreadystatechange = function () {

            if (this.readyState == 4) {
                req.onreadystatechange = null;

                if (this.status == 200 || this.status == 204) {
                    var result = JSON.parse(this.response);

                } else {
                    var error = JSON.parse(this.response).error;
                }
            }
        };
        req.send(JSON.stringify(postdata));

    } catch (e) {
        throwError(functionName, e);
    }
}

Power Automate | Data Operation | Compose vs Variable

Lets discuss ‘compose’ component under Data Operation in Power Automate. The official doc describe it as “Use the Data Operation – Compose action to save yourself from having to enter the same data multiple times as you’re designing a cloud flow. “

So basically you can store the input here something like this:

The main question that arises is why to use Compose when we have “Initialize Variable” component allready there. Here is how Initilize variable work:

The main differencei s ‘Performance’. So while variable can be setup multiple times, the Compose is more like a constant which can only be initialized once. This give a performace boost when using Compose compared to variable.

Just a run over a big array and doing same operation shows the differnece in the performance.

So if you are intializing a value just once and dont wish to modify it later – use compose. Use variable only when data modification is needed.

Dynamics 365 Data Migration for Audit Field

The one common question that customer ask when migrating a system to D365 is about these audit field – how can we populate these values.

These are D365 audit field and its not straight forward to fill them with.

CreatedOn

This is most commanly used field since most of the system like to maintain the actual created on date of record rather than the migrated one and that is why we have OOB support for this. There are two way to fill this:

OOB way of overridencreatedon
Plugin

D365 does provide a OOB to fill in the value in this field – but to fill this in we need to map overriddencreatedon field. Once a value is mapped to this field, it in turn populate CreatedOn. While the actual createdon date goes to OverriddenCreatedOn – as the decription of the field says :

CrmServiceClient client = new CrmServiceClient(connectionString);
Entity contact = new Entity("contact")
{
   Attributes = new AttributeCollection
   {
       new KeyValuePair<string, object>("lastname", "override created date"),
       new KeyValuePair<string, object>("overriddencreatedon", DateTime.Now.AddYears(-5))                    
   }
};
var res = client.Create(contact);

If however you use a pre-create/update plugin then the value createdon can be directly populated.

var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
if (PluginExecutionContext.MessageName.ToLower() == "create")
{
      target.Attributes["createdon"] = DateTime.Now.AddYears(-5);
}

CreatedBy, ModifiedBy

Coming to CreatedBy, ModifiedBy. There are 2 ways to fill in these values:

Impersonation
Plugin

So if you fill in the CallerId field of your client object as GUID of the user then these values can be set to that user.

CrmServiceClient client = new CrmServiceClient(connectionString);
client.CallerId = Guid.Parse("{1DBE94FB-BC4E-EB11-A813-000D3A9BF8FB}");// David So
Entity contact = new Entity("contact")
{
    Attributes = new AttributeCollection
    {
        new KeyValuePair<string, object>("lastname", "User Impersonation")
    }
};
var res = client.Create(contact);
client.CallerId = Guid.Parse("{8AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}");//Renee Lo
client.Update(new Entity("contact")
{
    Id = res
});

Please note that the Created By(delegate) and Modified By(delegate) is the actual user on whose name the service object was created. We cannot overwrite these using application running outside CRM – like Console or SSIS.

The other way is to create a pre-create/update plugin and fill in the values.

    var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
    if (PluginExecutionContext.MessageName.ToLower() == "create")
    {
        target.Attributes["createdby"] = new EntityReference("systemuser", Guid.Parse("{1DBE94FB-BC4E-EB11-A813-000D3A9BF8FB}"));//David So
        target.Attributes["modifiedby"] = new EntityReference("systemuser", Guid.Parse("{3BBE94FB-BC4E-EB11-A813-000D3A9BF8FB}")); //Eric Gruber
        target.Attributes["createdonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{8AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Renee Lo
        target.Attributes["modifiedonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{C6EBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Mollly Clark
    }
    if (PluginExecutionContext.MessageName.ToLower() == "update")
    {
        target.Attributes["createdby"] = new EntityReference("systemuser", Guid.Parse("{4AEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Jeff Hay
        target.Attributes["modifiedby"] = new EntityReference("systemuser", Guid.Parse("{B3BD94FB-BC4E-EB11-A813-000D3A9BF8FB}")); //Alicia Thomber
        target.Attributes["createdonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{B7BD94FB-BC4E-EB11-A813-000D3A9BF8FB}"));//Amy Albert
        target.Attributes["modifiedonbehalfby"] = new EntityReference("systemuser", Guid.Parse("{6CEBAA01-BD4E-EB11-A813-000D3A9BF8FB}")); //Karen Berg
    }

Same thing happens even on pre-update :

Do note thast plugin allow us to override even the delegate field however it isnt ideal to do that. Also updating modified by and modified on is also not encouraged since these value are suppose to updated by users modifying it and programtically updating it can cause loss of this information.

ModifiedOn

The last one is ModifiedOn – The only way is the plugin route. Create a pre-update/pre-create plugin and fill in the propertybag with required valu and you will be good to go.

var target = this.PluginExecutionContext.InputParameters.ContainsKey("Target")
                               ? this.PluginExecutionContext.InputParameters["Target"] as Entity
                               : null;
if (PluginExecutionContext.MessageName.ToLower() == "create")// or update
{
      target.Attributes["modifiedon"] = DateTime.Now.AddYears(-4);
}

A table summarizing these is as follow:

Field	Out Of Box	Pre Create/Update Plugin	Impersonation
CreatedOn	OverriddenCreatedOn	Yes	NA
CreatedBy	No	Yes	Yes
ModifiedOn	No	Yes	NA
ModifiedBy	No	Yes	Yes
CreatedOnBehalfBy	No	Yes	No
ModifiedOnBehalfBy	No	Yes	No

Using .NET Core/MSAL.NET to consume D365 Web API

We can use .NET Core and MSAL.NET library to connect to D365 WebAPI. Following package need to be installed for MSAL.NET library

We are using below configuration for our registered app

Below is the sample code for the getting top 10 accounts. We are using Interactive Mode to get the token :

using Microsoft.Identity.Client;
using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;

namespace D365ConnectionUsingMSAL
{
    class Program
    {
        static async Task Main(string[] args)
        {
            string serviceUri = "https://xxxxxxxx.crm.dynamics.com/";
            string apiVersion = "9.1";
            string crmapiUrl = $"{serviceUri}/api/data/v{apiVersion}/accounts";
            string clientId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            string tenantId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

            var publicClient = PublicClientApplicationBuilder
                .Create(clientId)
                .WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
                .WithRedirectUri("http://localhost")
                .Build();

            var scope = new string[] { "https://xxxxxxxxx.crm.dynamics.com/.default" };
            AuthenticationResult result = await publicClient.AcquireTokenInteractive(scope).ExecuteAsync();
            using (HttpClient httpClient = new HttpClient())
            {
                httpClient.Timeout = new TimeSpan(0, 2, 0); 
                httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", result.AccessToken);
                var data = await httpClient.GetAsync("https://orgb73380c8.crm.dynamics.com/api/data/v9.1/accounts?$select=name,address1_city&$top=10");
                if (data.StatusCode == System.Net.HttpStatusCode.OK)
                {
                    Console.WriteLine(await data.Content.ReadAsStringAsync());
                }
                else
                {
                    Console.WriteLine($"Some thing went wrong : {data.StatusCode} ");
                }
                Console.ReadLine();
            }
        }
    }
}

We can also use below if you have client secret for the registered app.

var confidentialClient = ConfidentialClientApplicationBuilder.Create(clientId: clientId)
.WithClientSecret(secret)
.WithAuthority(new Uri($"https://login.microsoftonline.com/{tenantId}"))
.Build();