Convert you custom JSON data into CRM SDK Entity object

on acmemapper, dynamics 365, Json, Entity

Hi there,

During some projects, we had exchanges between several systems and were pushing update from one system to our Dynamics 365 CRM.

The requirement was to retrieve the data send from a system "A" in JSON format, convert it into an entity object and then compare it with the existing record within the CRM in order to perform an update action or not.

Basically :

In this article, we are gonna focus on the step 2 : the transformation from JSON to CRM SDK Entity Object. We will do that in 2 parts :

  1. Modify the JSON structure to be "ready to transform"
  2. Transform the formatted JSON into our favority Entity object.

For the Comparing of 2 records, this is right here !

1. Modify the JSON structure to be "ready to transform"

Now we will focus on the CRM and how we can use this tool to perform actions in the CRM from some data in JSON format.
Assuming you already setup your Visual Studio project with nuget package.
You also have a sample : from JSON to CRM WebApi object

Let's see a sample and i will explain right away what was done :
Here we have the JSON data coming from a random system 

{
  "revenue" : 3873600,
  "name": "3D Printer Purchase - Modified",
  "closedDate": "2016-05-08",
  "purchase": 3,
  "reference": "6634f32e-4be5-e511-80fe-00155d09ab01",
  "client" : "475b158c-541c-e511-80d3-3863bb347ba8"
}

As we can see from the json above, the fields are not using the CRM naming convention, and we obviously have almost all types of "special" field types :

  • revenue is a Money field, in CRM : actualrevenue
  • name is a string (flat) field, in CRM : name
  • closedDate is a Date (flat) field, in CRM : estimatedclosedate
  • purchase is an OptionSet field, in CRM : purchasetimeframe
  • client is a Lookup field, in CRM : parentaccountid

In this sample, the flat fields are just a one to one mapping.
As example : 

{
  "systemA": "name",
  "CRM": "name"
  "system": "fieldProperty",
  ...
}

But the interesting part is related to the nested fields.

Let's now focus on the Money, OptionSet and Lookup fields and see how we can adjust flat value into the proper CRM SDK format.

  • Money should look like var moneyField = new Money(myvalue);

So the mapping for the money fields will be similar to : 

{
  "systemA": "revenue",
  "CRM": {
    "property": "actualvalue",
    "tosubproperty": "Money"
  }
}

Quick explanation, we actually say to the acmemapper that we want to "change" the property name from revenue into actualvalue and in addition add a nested value containing Money and the value of my property from the input.

Result : 

{
    "actualvalue": {
        "Money" : 3873600
    }
}
  • OptionSet should look like : var optionSetField = new OptionSetValue(int); So the mapping for the OptionSet fields will be similar to : 
{
  "systemA": "purchase",
  "CRM": {
    "property": "purchasetimeframe",
    "tosubproperty": "Value"
  }
}

Result : 

{
    "purchase": {
        "Value" : 3
    }
}
  • Lookup should look like : var lookupField = new EntityReference("entity", guid); So the mapping for the OptionSet fields will be similar to : 
{
  "systemA": "client",
  "CRM": {
    "property": "parentaccountid",
    "tosubproperty": "Id",
    "addproperties": {
      "lookup": "account"
    }
  }
}

Here you can see that we've added an extra information "addproperties".
Since a lookup field need an entity name and a guid, we are telling the mapping system that all input json data called client is a lookup of type account.

Result : 

{
    "parentaccountid": {
        "Id": "475b158c-541c-e511-80d3-3863bb347ba8",
        "lookup": "account"
    }
}

The complete mapping will be : 

{
  "$version" : "1.0",
  "opportunity": [
    {
      "systemA": "revenue",
      "CRM": {
        "property": "actualvalue",
        "tosubproperty": "Money"
      }
    },
    {
      "systemA": "name",
      "CRM": {
        "property": "name"
      }
    },
    {
      "systemA": "closedDate",
      "CRM": {
        "property": "estimatedclosedate"
      }
    },
    {
      "systemA": "purchase",
      "CRM": {
        "property": "purchasetimeframe",
        "tosubproperty": "Value"
      }
    },
    {
      "systemA": "client",
      "CRM": {
        "property": "parentaccountid",
        "tosubproperty": "Id",
        "addproperties": {
          "lookup": "account"
        }
      }
    }
  ]
}
Result after the acmemapper effect :
{
  "actualvalue": {
    "Money": 3873600
  },
  "name": "3D Printer Purchase - Modified",
  "estimatedclosedate": "2016-05-08",
  "purchasetimeframe": {
    "Value": 3
  },
  "parentaccountid": {
    "Id": "475b158c-541c-e511-80d3-3863bb347ba8",
    "lookup": "account"
  }
}

2. Transform the formatted JSON into our favority Entity object

In my particular scenario, we develop our custom mapping system called "acmemapper" which is really useful, fast and easy to use.
Please proceeed to : From JSON to CRM WebApi object using acmemapper for more details.

Based on what we know regarding the JSON formatting. We can now take care of the transformation from the JSON object into our Entity object.

Here is the snippets used :
  1. We load the entity metadata and retrieve each field's type (Thank to Fabien for this)
var fields = new Dictionary<string, Dictionary<string, Type>>();

/// <summary>
/// Retrieves the entity fields metadata
/// </summary>
/// <param name="entity">entity name</param>
private void LoadEntity(string entity)  
{
    fields = new Dictionary<string, Dictionary<string, Type>>();
    var request = new Microsoft.Xrm.Sdk.Messages.RetrieveEntityRequest();
    request.RetrieveAsIfPublished = true;
    request.EntityFilters = Microsoft.Xrm.Sdk.Metadata.EntityFilters.All;
    request.LogicalName = entity;

    var result = (RetrieveEntityResponse)this.service.Execute(request);

    this.fields.Add(entity, result.EntityMetadata.Attributes.ToDictionary(x => x.LogicalName, x => GetConversionType(x.AttributeType)));
}

/// <summary>
/// Return the type of field based on the CRM's AttributeTypeCode
/// </summary>
/// <param name="typecode">AttributeTypeCode</param>
private Type GetConversionType(AttributeTypeCode? typecode)  
{
    switch (typecode.Value)
    {
        case AttributeTypeCode.BigInt: return typeof(Int64);
        case AttributeTypeCode.Boolean: return typeof(Boolean);
        case AttributeTypeCode.DateTime: return typeof(DateTime);
        case AttributeTypeCode.Decimal: return typeof(Decimal);
        case AttributeTypeCode.Double: return typeof(Double);
        case AttributeTypeCode.Integer: return typeof(Int32);
        case AttributeTypeCode.Memo: return typeof(String);
        case AttributeTypeCode.Uniqueidentifier: return typeof(Guid);
        default: return typeof(String);
    }
}

The Field variable will contain the following pieces of information : 

fields = [{  
    "entityname",
    [
        {"fieldname", "fieldtype"},
        {"fieldname2", "fieldtype"}
        ...
    ]
}]

A bit of explanation related to the 2 methods above :

  • LoadEntity : which will load the entity metadata and push each field definition in the dictionnary called "Fields".
  • GetConversionType : which is called inside LoadEntity method, this will allow us to strong type our fields with the CRM types definition.

We now have the necessary pieces of information from the CRM to transform / process the JSON data we received from the external system.

A new function will now perform the real mapping between the transformed JSON and an Entity object from the CRM to be back to CRM specific object with which we are familiar to :).

/// <summary>
/// Performing the mapping between each fields received as JSON into an entity Object
/// </summary>
/// <param name="property">property containing the key/value of the field</param>
/// <param name="entity">empty Entity object to push remove the properties to or remove them from</param>
public Entity pushValueToEntityField(KeyValuePair<string, JToken> property, Entity entity)  
{
    try
    { 
        var value = property.Value;
        var key = property.Key;

        if (value.Type == JTokenType.Object)
        {
            if (value["lookup"] != null && value["lookup"].Type != JTokenType.Null)
            {
                if (value["Id"].Type != JTokenType.Null)
                    entity[key] = new EntityReference(value["lookup"].ToString(), value["Id"].ToObject<Guid>());
                else
                    entity[key] = null;
            }
            else if (value["Value"] != null && value["Value"].Type != JTokenType.Null)
                entity[key] = new OptionSetValue(value["Value"].ToObject<int>());
            else if (value["Money"] != null && value["Money"].Type != JTokenType.Null)
                entity[key] = new Money(value["Money"].Value<decimal>());
            else
                entity[key] = Convert.ChangeType(value.Value<object>(), servicebus.cache.GetTypeForEntity(entity.LogicalName, key));
        }
        else
            entity[key] = Convert.ChangeType(value.Value<object>(), servicebus.cache.GetTypeForEntity(entity.LogicalName, key));
    }
    catch (Exception ex)
    {
        throw new Exception(String.Format($"pushValueToEntityField failure : {ex.Message}."));
    }

    return entity;
}

/// <summary>
/// Return the field's type for an attribute linked to an opportunity
/// </summary>
/// <param name="entity">Entity name</param>
/// <param name="attributeName">Attribute name</param>
public Type GetTypeForEntity(string entity, string attributeName)  
{
    if (!this.fields.ContainsKey(entity))
        this.LoadEntity(entity);

    if (this.fields.ContainsKey(entity) && this.fields[entity].ContainsKey(attributeName))
    {
        if (this.fields[entity][attributeName] == null)
            throw new Exception(String.Format("Type of CRM field {0}.{1} is null", entity, attributeName));
        else
            return this.fields[entity][attributeName];
    }
    else
        throw new Exception(String.Format("Can't find field type in CRM for entity and field {0}.{1}", entity, attributeName));
}

A bit of explanation related to the 2 methods above :

  • pushValueToEntityField : for each fields included in the JSON object we initially received, we will convert it into the related Entity attribute object based on the type definition. You can see that we have special condition for CRM specific types such as Lookup, Money or OptionSet fields. The rest is handled by the method GetTypeForEntity explained just below
  • GetTypeForEntity : which is called inside pushValueToEntityField method, will be used for the fields with (let's say) out of the box typing such as Int, Bool, Decimal, String and so on.

It's now time to compare our two records !, read this article.

Quick intermediate sum-up of what we did so far :

  • Retrieved data from a system A with JSON data
  • Creating a one to one field mapping for this JSON with the acmemapper tool in order to have a System A field name linked CRM Entity object field name
  • Retrieved the type of each JSON fields in order to strongly type them during the conversion into Entity attributes
  • Converted the JSON into real Entity object handling Lookups, Money, OptionSets and "normal" fields value.

Conclusion :

You now have the possibility to simply switch from some JSON data into your favorite Entity object from the SDK after doing a proper conversion.

The advantages of using JSON format before going into the conversion for SDK is that it's really flexible data to edit, modify. You are almost allowed to do whatever you want.

From that point, you can perform any actions within your CRM using the SDK you are working with since a long time to finish your project.

Hope this can help,
Happy CRM'in

Clement

Dynamics 365 CRM & Power Platform addict.