The myriad of options when trying to implement a simple field selection in Microsoft Dynamics 365 CRM

If anyone has ever taken a Microsoft exam in either Dynamics 365 or, going back further, Dynamics CRM, you'll be familiar with the concept of a question which gives you a couple of sentences about a situation and then some possible solutions to sort it out. Some of these are sensible but, if you've been in this situation before, you'll be familiar with the voice in your head shouting "What! They'd all work!"; obviously you don't actually say it as you'd get kicked out as you're not allowed to speak but you get the gist.

The whole idea behind this is that some of the solutions are more suitable than others and, in the description of the issue, there'll be a couple of little nuggets which are designed to steer you down a path and, as professional consultants, we should know what the best route is. I mention this because I've had a similar situation at work which highlights that, although this sounds annoying and trivial, there are reasons behind it. Let me explain what's happened and, hopefully, you'll see where this is going...

A customer of ours added an option to the Account table to store 'Account Type'; things like 'Limited Company', 'Private Individual', 'Selling Agent', etc. For their go live, we need to import their data which consists of about 10,000 records and, in this import, there's five options for this field. They periodically need to extract this data into a 3rd party system but, for this field, the 3rd party system needs a numeric code for this instead of the text value; so 'Limited Company' is 50, 'Private Individual' is 40 and so on. We'll be filling in the Account Type field and need to automatically calculate this code.

In terms of how to achieve this, we have lots of options. As a bit of fun, try and come up with some and compare it to my list below (but no peeking!) and then, in a Microsoft exam style, we can work out which is the best method to use.

I came up with seven options:

1. Add a new field along with a business rule to fill this field in.
2. Add in a calculated field to fill this value in.
3. Add a new field and use JavaScript to fill in the value.
4. Add a new field and use Power Automate to populate the value.
5. Add a new field and use a real-time workflow to fill in the value.
6. Add a new field and use a plugin to populate the value.
7. Update the option set so the label is what the user sees but the value is the numeric one that the 3rd party system needs. 

 So, for these options (and you might have come up with more - if that's the case, please leave a comment as I'd love to know) some will work, some will sort of work and others won't. Let's have a look and see why I'm going to remove some of these options.

Business Rule

The brief says there's five values needed in the option set. Business rules are allowed to go down to five levels so, whilst this would work, the moment the customer needs a sixth value in there, we've got a problem.

JavaScript

The brief says that the initial data will be imported. As JavaScript only runs on the form, it wouldn't fill this value in unless a user went into each of the 10,000 records, triggered the script and then saved the record. A very long-winded method so not suitable.

Power Automate

If we import these records in one go, the 5,000 record daily limit would kick in and only fill this field in for half of the records. We could do this over a couple of days but that's not ideal.

Plugin

No. Just no. We don't need that level of complexity for something so simple.

Update the option set value

The extract to the 3rd party system isn't well defined so, if we've got to use the 'Export to Excel' method, this value won't be easily available so this method wouldn't work.

Real-time Workflow

At some point in the future, whether it's one year, two years or five years, we're going to lose this feature so, whilst it would work right now, we can't guarantee it'll work long term.

So, of the seven options, that's six of them gone. It just leaves the calculated field. In the past, I've also had some problems with these where the data is unavailable for the output method but I'd be 99% sure it would work here. However, what I'm actually going to do is a mixture of two methods so, even though one has been removed, it may still have a use. We’ll also have a backup ready as well.

Firstly, as the system has no data in it right now, it's a simple task to update the value for the option set. If it turns out we don't use Excel as the export method, our problems are solved with this one solution. So I'm going to do this.

I'm also going to add a calculated field to work this out as this was the only method I hadn't highlighted a problem with. However, if we hit that 1% edge case where it's not suitable, I'll need a back up to switch to.

For this, if it's needed, I'll use Power Automate. Yes, I'll need to add a simple field which will need to be updated and Power Automate will hit the 5,000 record limit again but I'd probably just add in a legacy workflow to copy the calculated field to the simple one, run it against all the records then remove it - by which time, Power Automate will be in place to keep it updated.

I also wanted to use this to highlight the importance that professional consultants can play in your project. Somebody without a full understanding of these methods, and their pitfalls, might pick one of the unsuitable options. Whilst this wouldn’t be the end of the world, you could spend a lot of time writing, say, the JavaScript function only to realise that it’s unsuitable. You could then move on to one of the other methods which, again, could result in lost time. It also shows that while things may look fairly simple of the surface, a lot of thought and consideration goes into the methods that end up in your system.