Scenario Overview

Note : This Article is for pre version 9 of CRM , since then Microsoft has given the Process Stages its own Entity. Note : This Article is for pre version 9 of CRM , since then Microsoft has given the Process Stages its own Entity.

This article is a follow on from xRM Formula #76. However in this instance the business requirement is to make sure there are at least 1 Decision Maker and 1 Champion stakeholder assigned to the Opportunity before it can be progressed to the Develop stage.

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • A formula of type Validation is created
  • It is configured so that it executes each time the Process Stage field changes on the Opportunity entity
  • On execution it runs a FetchXML query that checks the Process Stage is Develop
  • It then uses another FetchXML query that checks if there is at least 1 Stakeholder with a role of Decision Maker and 1 Stakeholder with a role of Champion connected to the Opportunity
  • If sufficient approved Stakeholders are found it allows the Opportunity Stage to progress to the Develop stage                    

N52 Formula Manager Steps

The following set of steps outline how to create this Formula

  • Create a new formula of type Validation, mode Server Side, event: Create & Update
  • Set the Source Entity to Opportunity
  • Set the Source Property to Process Stage
  • Copy and paste the formula below into the N52 Formula 
  • Create the 2 FetchXML queries and copy in the code into them
  • Click Save
  • You are now ready to test


Iftrue (FindValueFD( 'GetCurrentStageName', 'stagename', '?',true)= 'develop'  and
                   SetParams(  FindValue('connectionrole', 
                                'name', 'Decision Maker', 
                                'connectionroleid', '?',true),
                                    'connectionroleid', '?',true)
                      ) ,
                ) != 2,                          
  'You cannot go to the Develop stage without at least 1 Decision Maker and 1 Champion  stakeholder! being assigned to this Opportunity'

FetchXML : GetCurrentStageName

<fetch count="50" >
  <entity name="processstage" >
    <attribute name="stagename" />
    <filter type="and" >
      <condition attribute="processid" operator="eq" value="@processid@" />
      <condition attribute="processstageid" operator="eq" value="@stageid@" />

FetchXML : CountConnections

<fetch count="50" distinct='true' >
  <entity name="connectionrole" >
    <filter type="or" >
      <condition attribute="connectionroleid" operator="eq" value="{0}" />
      <condition attribute="connectionroleid" operator="eq" value="{1}" />
    <link-entity name="connection" from="record2roleid" to="connectionroleid" >
      <link-entity name="opportunity" from="opportunityid" to="record1id" >
        <filter type="and" >
          <condition attribute="opportunityid" operator="eq" value="@opportunityid@" />

Note 1 :  The Fetch XML 'CountConnections' uses the distinct keyword to make sure only 1 record of each type is counted. 
This way if 4 Decision Makers and 17 Champions are assigned to the Opportunity then the Fetch XML will still return a value of 2 (At least 1 Champion and 1 Decision Maker was found).

Wizard - FindValueFD

Please see below the wizard you can use to create the FindValueFD() function call used in this formula. 

Note you will need to create the Fetch Xml GetCurrentStage beforehand. 

Did you know?

North52 provides Automated Testing for Dynamics 365

TestShield® is a unique solution that helps you automatically test your End to End Dynamics 365/CRM and Power Automate processes.  It is built on the proven and robust North52 Business Process Activities rules engine and offers the ability to repetitively test and report on your key business processes.

TestShield's key features:

  • Fully integrated with Dynamics 365
  • Easy to use by BA's, Testers, and Developers
  • Advanced test data generation and management
  • Codeless advanced testing reduces test creation time
  • Test and validate across all layers with a single test

Learn more about TestShield