Create a Better Authority Matrix and Approvals Process in MS Dynamics CRM

In 2018 I was suddenly pushed into the world of Microsoft Dynamics and in 2019 I joined a company as their developer. My new employers use the Opportunity entity for their pipeline and approvals process and had gone to an external company to provide an authority matrix solution. The solution is based around Javascript and, as a result, this third party company could charge a four figure fee whenever the authority matrix required updating.

If the following requirements sound familiar, you may want to read this article to find out how I overhauled said Authority Matrix solution. A colleague who joined just after I did said that the java-based, third-party, money-spinning method in use was the only way she'd seen it done before in other places.

The Requirement

This is grossly over-simplified, but let's imagine we have several sales regions. We'll stick with four and call them:

• District North
• District South
• District West
• District East

Each Region consists of a number of Business Units from a Dynamics entity. We'll say 12 and call them:

• District North Area 1
• District North Area 2
• District North Area 3
• District South Area 1
• District South Area 2
• District South Area 3
• District West Area 1
• District West Area 2
• District West Area 3
• District East Area 1
• District East Area 2
• District East Area 3

Each of the 12 Business Unit's sales opportunities must first be approved by the Business Unit Managing Director at an Approval to Bid gateway stage. Depending on the value of the contract, up to 5 tiers of approval might then be required ranging from Regional Director of Operations all the way to CEO of the company. We also have both direct sales and broker sales so this further complicates the matrix.

An Opportunity should not be able to proceed past the gateway stage until full approval is obtained.

The Money-Spinner

The solution I inherited used Javascript to determine whether the opportunity could proceed. Each approver clicks on a restricted option set field to record their decision. Sitting above this is one final option set field that is set to mandatory in a "Gateway" stage of the Business Process Flow so that the opportunity cannot proceed unless it is set to Yes, however the field has been made invisible to all users through Javascript and therefore requires some form of automated completion.

The code managing this field had some very longwinded logic along the lines of:

If Business Unit begins with District North*;

  write 'IsNorth' to a special 'ShortCode' field;
 

If value is less than 200k and Approver1Decision = Yes

  Then GatewayApproval = Yes
 

If Value is less than 400k and Approver1Decision = Yes and Approver2Decision = Yes

  Then GatewayApproval = Yes

If Value is less than 750k and Approver1Decision = Yes and Approver2Decision = Yes and Approver3Decision = Yes

  Then GatewayApproval = Yes

blah blah blah, you get the picture

  Else Gateway Approval = No

If Business Unit begins with District West*:

  write 'IsWest' to the 'ShortCode' field;

If value is less than 250k and Approver1Decision = Yes

  Then GatewayApproval = Yes

If Value is less than 500k and Approver1Decision = Yes and Approver2Decision = Yes

  Then GatewayApproval = Yes

blah blah blah, this is getting tedious

  Else Gateway Approval = No

 

If Business Unit begins with District South*:

...okay that's enough of that...

 Else Gateway Approval = No

 

At the same time, Business Rules were set up to make the Opportunity Form look all lovely. These went along the lines of:

If ShortCode = IsNorth and Est.Final Value <200k;

 Approver1Decision is Visible

 Approver1Name is Visible

 Approver1DecisionDate is Visible

 Approver2Decision is Hidden

 Approver2Name is Hidden

 Approver2DecisionDate is Hidden

 ...oh never mind, this is more boring than the last one...

To complete the trinity, there was a set of Workflows to notify approvers that there was an opportunity requiring their go ahead. Once again, these had logic coded around the authority matrix that went:

If RequestApproval = Yes & Business Unit = District North Area 1

 Send an email to F.Bloggs

If Approver1Decision = Yes & ShortCode = IsNorth & EstFinalValue > 200k & EstFinalValue < 400k

 Send an email to J.Doe

If Approver2Decision = Yes & ...ah jeez here we go again...

 

 

So if the authority matrix were to undergo a change (to an approval limit for example) this had to be carefully inserted to three places or you might actually render it impossible to proceed with an opportunity. A new Business Unit or sales region was a scenario that haunted my nightmares.

Since we were using 17 inconsistently named Business Units, many more than four sales regions and had two such decision gateways, you can imagine the acres and acres of code that had to be read through to correct anything that had become obsolete.

In-House Solution

If the above sounds familiar, and you want to turn this into a system that anyone with a copy of the authority matrix in front of them can update in seconds from the front end, then here is my replacement. I am assuming readers of this will be experienced enough developing in MS Dynamics for me to simply refer to the elements being used without explaining every last detail.

1. Javascript

The cynic in me questioned the motivations behind all that Javascript and wanted everything possible brought out into the light, but there were two aspects to the code I approved of and kept.

Firstly, the logic based around the Business Unit that completes a 'ShortCode' field (i.e. "IsNorth", "IsSouth", "IsWest", "IsEast"). This provided an elegant solution for other things such as customising the Opportunity form by making sections appear and disappear.
All those hardcoded monetary limits had to go, though, so I stripped the code back to just the ShortCode aspect. I've tinkered enough with code to be able to delete, copy and amend so when I was finished I had also made it concatenate the word "Broker" if that particular sales channel was ticked, e.g. "IsWestBroker". This javascript function has an OnEvent trigger whenever someone changes the Business Unit lookup in the Opportunity form.

Secondly, there was a function that automatically recorded the user's name and a datestamp whenever a tier was approved.

I deleted the javascript that made the GatewayApproval field invisible and the function that wrote to it. This turned out to be a very serendipitous move because for some reason the script said:

If all approvals have been obtained;

 set the field to Yes;

Else set it to No.

 

This seems innocent enough except that the option set for the field in question only had Yes or Blank as options.

How had this worked all that time? All I know is that it decided, after working for literally years, to fail on my watch when Unified Interface came in. It was sheer luck I had noticed it the day before. It's as though Unified Interface is less forgiving of shoddy Javascript.

So anyway... at this point we have the first building block of the solution, which is a field in the Opportunity entity that identifies the sales region and sales channel with a simple codeword.

2. Business Unit Entity

I added a new field to the Business Unit entity called Managing Director and made it a lookup to the User entity. I'm really surprised it wasn't there already to be honest.

Now we have:

1. A code that identifies the sales region and sales channel
2. A means of identifying the Managing Director of each of our 12 Business Units via a 1:N relationship from User to Business Unit and a 1:N relationship from Business Unit to Opportunity

3. New Entity: 'Hierarchies'

The sensible way of describing an Authority Matrix is in a single SQL table or a spreadsheet or something, not as a lengthy piece of prose beginning "Once upon a time..." that you translate to three different languages and hide in three different locked filing cabinets. I therefore created a Hierarchy entity that had two fields for every tier in our approvals process. These are:

• T1 Approver Name (Lookup to User entity)
• T1 Upper Limit (currency)
• T2 Approver Name (Lookup to User entity)
• T2 Upper Limit (currency)
• T3 etc.

A typical record in this entity would look like:

Hierarchy Name: District North

T1 Approver Name: <blank>	T1 Approver Limit: £200k
T2 Approver Name: J. Doe	T2 Approver Limit: £400k
...	...
T5 Approver Name: D. Bigman	T5 Approver Limit: £999,999,999

The reason the T1 Approver Name field is blank is that the T1 Approver is one of the 12 Managing Directors and not an approver from one of the 4 sales regions. Including this field will give us flexibility further down the line, though. The principle we are going to stick to with our limits is: If the value of the Opportunity is less than this limit, it stops here.

Now our building blocks are:

1. A code that identifies the sales region and sales channel
2. A link to whichever of the 12 MDs is responsible for the opportunity
3. Our Authority Matrix in a single place, in an intuitive table/picklist type format and updateable in the front end via a form same as any other record

 

4. Opportunity: Link to Hierarchy

In the Opportunity entity I created a new lookup relationship to the Hierarchies table.

I then created a Business Rule that looked at the ShortCode field and says:

If ShortCode = "IsNorth" then HierarchyLookup = "District North"

Else if Shortcode = "Is South" then...

etc. etc. with an   Else if ShortCode Ends in "Broker"   thrown in there. Broker sales may well use a regional approver instead of the Business Unit Managing Director.

Now we have:

1. A code that identifies the sales region and sales channel
2. A link to whichever of the 12 MDs is responsible for the opportunity
3. Our Authority Matrix in a single table
4. An automated means of selecting the correct approvals hierarchy from the picklist of records, based on someone entering/updating the Business Unit

5. Opportunity: Gateway Governance Field

I now created a new field in the Opportunity entity called 'Gateway Governance'. This is a calculated whole number field and is the keystone for our solution. It's the most important field yet, I think.

The basic calculation is:

If Entity:Opportunity HierarchyLookup is blank then set value to 5 (this just acts as a failsafe)

Else If Entity:Opportunity Est.Final Value is less than Entity:Hierarchy T1 Approver Limit then set value to 1

Else If Entity:Opportunity Est.Final Value is less than Entity:Hierarchy T2 Approver Limit then set value to 2

Else If Entity:Opportunity Est.Final Value is less than Entity:Hierarchy T3 Approver Limit then set value to 3

etc.

I did state above that all of this is a simplification and in fact our calculation also sometimes involves an override based on the profit margin reaching a certain percentage. This means additional fields in the Hierarchies entity and doesn't really need to complicate this article, but I wanted to mention that with calculations we can set the value of our GatewayGovernance field to the same value as a field in a related entity by setting the value to, for example,

'hierarchy.new_marginoveride'   with the '.' being key to it working.

I only bring it up in case readers are thinking "that wouldn't work for our matrix; it's not sophisticated enough".

At this point I tested the model and was crestfallen to receive seemingly random errors. Sometimes it would work and other times it would not. As I pondered and experimented I discovered that my mistake was that I looked up the maximum value for a currency field and used that as the upper limit for the last tier (something like 900,000,000,000,000.00) and it was causing some sort of overload in the calculation. Don't do this.

So, now we have:

1. A code that identifies the sales region and sales channel
2. A link to whichever of the 12 MDs is responsible for the opportunity
3. Our Authority Matrix in a single table
4. An automated means of linking to the correct approvals hierarchy
5. A field that simply states "This is a 1 tier opportunity" or "This is a 4 tier opportunity" without having to reference the Business Unit or Est. Final Value in some epic piece of hardcoded logic

6 Opportunity: Business Rules

I could finally delete the catalogue of business rules that made the approval fields on the opportunity main form appear and disappear. I was almost afraid to because someone had clearly spent many hours deciding that "if the ShortCode is IsNorth and the Est.FinalValue is less than 200k etc." (too much time to have been able to give names to any of the Conditions or Actions apparently).

I replaced them with a single rule that did take a long time to create, but is completely generic because it just says "if Gateway Governance says this is a 1 tier opportunity then show the three tier 1 fields and hide the rest.." and so on. It won't ever need updated unless there is a major change such as the introduction of a 6th tier.

I also created two business rules to replace the Javascript that governed the final GatewayApproval field and that made it invisible to users while it was in the Business Process Flow.

We're almost there, with:

1. A code that identifies the sales region and sales channel
2. A link to whichever of the 12 MDs is responsible for the opportunity
3. Our Authority Matrix in a single table
4. An automated means of linking to the correct approvals hierarchy
5. A single governance field clearly stating how many tiers of approval are needed
6. A set of business rules working in sync because they all look at the GatewayGovernance reference field and all they need to know is that this opportunity required X number of approvals

7 Opportunity: Workflows

Lastly, the horror that was the workflows. All keyed to a particular Business Unit and with hardcoded Email To: fields and approval limits. There was a suite of them for each approval tier. Tier 1 is triggered by someone putting 'Yes' in a Request Gateway Approval field.

Tier 2 is triggered by Approver 1 adding a positive response to his/her decision field and so on.

These were replaced by a single workflow for each tier, still using the same triggers. The new workflow logic for Tier 1 says:

Change to Request Gateway Approval...

If Request Gateway Approval = Yes then

    If Hierarchy.T1 Approver Name = <blank>

    then Email to: Business Unit.Managing Director

    Else Email to: Hierarchy.T1 Approver Name

 

Tier 2 logic looks like:

Change to Approver1Decision...

If Approver1Decision is Approved;Approved with comments AND GatewayGovernance >1 then

    Email to: Hierarchy.T2 Approver Name

And that's my solution. There are a couple of other 'If's involved along the line due to the intricacies of what influences how high up our approvals go, but everything works and only exceptional changes involve editing anything more than the matrix itself or a business rule.

Apologies for the lack of screenshots. I'll try and get some, but I'll need to redact them carefully since they are from my place of employment.

If this is helpful, inspires you, or even saves you exorbitant fees every time you need your own Microsoft Dynamics Authority matrix edited, then please consider rewarding me with a drink or contributing towards the cost of keeping this website running.