Capitalise your lead and contacts names consistently in CDS

Leads and contacts within your common data service (CDS) environment can come from multiple sources. Some common sources include:

  • Imports from Excel spreadsheets
  • Data integrations
  • Manual user entry

All of this could result in your names being inconsistently captured in formatting/capitilisation. For example,your contact or lead could be captured with the following first names:

  • "Bruce"
  • "BRUCE"
  • "bruce"
  • " BRUCE "

This becomes a challenge when you are looking to use these details for personalisation in text messages, surveys, emails and so forth.
As they say, prevention is better than cure! The solution I used to deal with this involved using an Azure logic app with:

  • CDS Trigger
  • Execute Javascript function
  • CDS Update

overview

Unfortunately the 'Execute Javascript function' is currently not available in Microsoft Flow, hence why I used a logic app.
The detailed steps of the logic is app is:

  • When a lead is created

s1

  • Create a variable array of the fields we want to convert and;
  • Loop through the field values in the array, and handle NULL values, trim any spaces and convert the casing to a consistent format

s2

  • Update the values back to the lead record, references the array values from the previous step

s3

The good feature of the Javascript function is that you can map as many fields as you want in the one call and not have to replicate it each time for each field value you want to convert. (Another reason why I went with logic apps and not Flow).

Below is my lead as I created it:

before

..and after the Logic App ran :)

after

As this trigger is server side based, this solution handles the various sources data could be captured into the environment. Note, you might need to consider performance and cost implications especially if you are about to do a huge load or data migration/import!
Code in the Javascript function below

var fields =[ workflowContext.trigger.outputs.body.firstname ,workflowContext.trigger.outputs.body.lastname];

fields.forEach(function(part, index) {
    var txt = fields[index];
    if(txt === null) txt="";
    txt=txt.trim();
    if(txt.length >1){
        txt = txt.charAt(0).toUpperCase() +txt.slice(1).toLowerCase();
    }
    else{
        txt = txt.toUpperCase();
    }
  fields[index] = txt;
});

return fields;