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 #77. This method is an improvement on #77 because it removes the need to perform the two FindValue() function calls to lookup the guids of the connection roles which gives us a performance boost. So instead we dynamically generate a fragment of the fetch-xml so that it can be injected into the main fetch-xml query. 

This scenario is still using the original business requirement that an Opportunity record should not progress to the Develop stage without the appropriate Stakeholders being assigned to the opportunity record.

North52 Decision Suite Solution

The North52 Decision Suite 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 Qualify
• Next it populates the list of acceptable stakeholders in the second FetchXML query using the SetParams() and AppendFormat() functions**
• It then executes the FetchXML query that checks if there is at least 1 of each type of necessary Stakeholder connected to the Opportunity
• If sufficient approved Stakeholders are found it allows the Opportunity Stage to progress to the Develop stage

**See Note at end of page         

North52 Decision Suite 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

Formula

Iftrue (FindValueFD( 'GetCurrentStageName', 'stagename', '?',true)= 'develop' and
    
        FindCountFD('CountConnections', 
                  'connectionroleid', 
                  '0',
                  true,
                  SetParams(AppendFormat('<condition attribute="name" operator="eq"
                                         value="{0}" />', 'Decision Maker,Champion') ),
                    true
                ) != 2,                          
        
  'You cannot go to the Develop stage without at least 1 Decision Maker and 1 Champion stakeholer! 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@" />
    </filter>
  </entity>
</fetch>

FetchXML : CountConnections

<fetch count="50" distinct='true' >
  <entity name="connectionrole" >
    <filter type="or" >
    {0}
    </filter>
    <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@" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

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). 

Note 2 : AppendFormat('<condition attribute="name" operator="eq" value="{0}" />', 'Decision Maker,Champion')   

The AppendFormat function takes 2 parameters, a base string and a second string of comma, separated values.

 In this example it will create the following :

  <condition attribute="name" operator="eq" value="Decision Maker">
  <condition attribute="name" operator="eq" value="Champion"> 

This is an example of a truly dynamic Fetch XML where the actual query is built on-the-fly by whichever parameters are passed into it.  

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.