.jpg)
A tale as old as time. Or rather a requirement as old as CRM. Sentence casing in names is an obvious requirement every time you have a Contact type of form. But what happens if your users do not follow the simple rules around it?
So, what is this ghost story all about?
By default, your Dataverse OOTB contact table will have columns such as First Name, Last Name, Middle Name etc.
When end users are going into your Model-Driven App, the rules of sentence casing should apply. Uppercase first letter and lowercase for the others. Oh, and no random leading or trailing spaces, right? These all look like simple rules.
However, this is not the CRM reality. We often find ourselves with data hygiene issues even with the simplest things such as sentence casing. Of course, things are exacerbated with other dependencies whether it is integration requirements for these fields, official documents looking unprofessional etc.
How scary will this be?
The good news is we do not have to worry about spaces as these are removed automatically before saving to Dataverse. (Try it in your Model-Driven/ Dynamics 365 App, pretty cool).
When it comes to sentence casing though, we need to intervene and guide users. Sure, you can add a tooltip or a recommendation for the column to be populated in a specific format. However, that does not guarantee users will follow it.
Instead, we can use an automation to help us safeguard the data input process. Whilst there are may ways to do this, from low code to pro dev, let’s look at a Power Automate flow.
Who you gonna call for help?
There is a way to create essentially a data transformation flow. This will mean that regardless of what the user originally saves in your Model-Driven App/ Dynamics 365 instance, you can trigger the sentence casing flow and do things right.
Let’s start building our automated flow.
We add our Flow Name: “Sentence Casing Formatting Flow” and choose our trigger. This is the good old When a row is added, modified or deleted for Dataverse. Click Create.
Let’s start setting up. Our Change type is perfectly ok with Added or Modified.
For context, this solution is for the Avenge 365 app. Many articles based on it such as:
Automating Checklists in Dynamics 365/ Model-Driven Apps
Building Complex Forms and Views in Power Pages
Back to our flow though. The Table name is Avengers (our Contact table for Avenge 365) and for ease, Scope is Organization. Please adjust to your own security model.
For housekeeping, let’s rename the trigger step to be clear about what we are doing.
The next ingredient in this step is to specify this flow is triggered only for the First Name column Thus, we add the logical name firstname added in Select columns.
Our job does not end here. We do not want to get stuck in a pesky infinite loop with this flow. So what do we do? We add a Trigger Condition of course.
To do that, go to the 3-dot ellipsis of the trigger step and click on Settings.
In the Trigger Conditions at the bottom the Settings, click on +Add and add the following:
@not(equals(triggerOutputs()?['body/firstname'], concat(toUpper(substring(triggerOutputs()?['body/firstname'], 0, 1)), toLower(substring(triggerOutputs()?['body/firstname'], 1)))))
Explaining the JSON forces behind it
Our JSON condition checks whether the value of the firstname column (from the trigger output) is not equal to a modified, sentence-cased version of itself.
Here’s how it works step by step:
substring (triggerOutputs()?['body/firstname'], 0, 1) extracts the first letter of the firstname.
toUpper(...) converts that first letter to uppercase.
substring (triggerOutputs()?['body/firstname'], 1) extracts the rest of the letters in firstname.
toLower(...) converts all those remaining letters to lowercase.
concat(...) combines the modified first letter with the modified remaining letters to complete our sentence casing journey.
equals(..., ...) checks if the original firstname value is equal to the newly constructed sentence cased string (with the first letter uppercase and the rest lowercase).
not(...) negates the result of the equals function. So, it returns true if the original firstname value is NOT equal to the newly constructed sentence cased string.
In plain English, our trigger condition will check that we are not triggering it for sentence-cased Avengers name such as Thor. However, we do want to trigger it if accidentally we entered THor, tHor, THoR etc. And voila, the infinity (Stone) loop has been avoided!
This JSON Trigger Condition as you can imagine spills the beans on how we will sentence case through concatenation as well as using toUpper and toLower.
Now, click Done and go back to your main flow screen.
The next step we will use is the Update a row Microsoft Dataverse Action.
As always, good flow housekeeping by updating the title of the step. What we need here is the Table name again (Avengers – our Contact table). Then, we add the Row ID which we select from Add dynamic content as Contact. Quick reminder here, we are essentially getting the Row ID from the previous trigger.
The final step is to add our expression for the sentence casing to update the First Name value.
As foresaid earlier, this is:
concat(toUpper(take(triggerOutputs()?['body/firstname'], 1)), toLower(skip(triggerOutputs()?['body/firstname'], 1)))
At this point, we already explained our expression wonders. Concat, toUpper and toLower are our main ingredients, and they work quite well for longer strings with a bit more manipulation.
We are ready to click on Save and enjoy the fruits of our labor!
Remember, you can use this logic for other kinds of names e.g. Last Name, or columns which need such sentence casing logic e.g. a street name.
Comments