Views:

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 #78.  This enhanced scenario is the original business requirement plus now we must take into consideration the Budget value of the Opportunity before deciding what stakeholders are required based on the following rules,

  • Budget < $100,000  - Requires: Decision Maker & Champion stakeholders
  • Budget > $100,000  - Requires: Decision Maker, Champion & Economic Buyer stakeholders

This method is a further enhancement  of #78 it because retrieves the required Stakeholders from xCache therefore separating the business rule (i.e. formula) completely from the configuration data. Like #78 we dynamically generate a fragment of the fetch-xml so that it can be dynamically injected into the main fetch-xml query. 


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
  • SmartFlow function is used to allow us to execute multiple actions in the formula
  • When executed it first checks if the Stage of the Opportunity is not currently Develop. If it is the formula exits.
  • We set the list of required stakeholders depending on the budget in the Opportunity
  • We build the FetchXML fragment using the xCache values and AppendFormat() function
  • We determine how many stakeholders should be be present on the record
  • Then we count how many actual stakeholders are on the Opportunity
  • Finally we check if the actual count equals the required count and if not display an error message

North52 Formula            

North52 xCache Records

N52 Formula Manager Steps

The following set of steps outline how to create this Formula

  • Create the xCache records for the FetchXML and your separate Stakeholders list
  • 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

SmartFlow(
    
    Iftrue(FindValueFD( 'GetCurrentStageName', 'stagename', '?',true) != 'develop', Break()),
    
    SetVar('StakeholderList',if([opportunity.budgetamount]  > 100000,
                                  xCacheGetGlobal('Stakeholders_Greater100K'),  
                                  xCacheGetGlobal('Stakeholders_Less100K'))),
    
     SetVar('DynamicFetchFragment',
             AppendFormat(xCacheGetGlobal('Stakeholders_FetchXML'),
             GetVar('StakeholderList'))),                              
  
     SetVar('NumOfRequiredStakeholders',CountCharacters(GetVar('StakeholderList'),',') + 1),
                                
   SetVar('StakeHolderCountActual',
           FindCountFD('CountConnections', 
                       'connectionroleid', 
                       '0',
                        true,
                        SetParams(GetVar('DynamicFetchFragment')),      
                        true)),
    
    iftrue(GetVar('NumOfRequiredStakeholders') != GetVar('StakeHolderCountActual'), 
      
      StringFormat('The Develop stage requires the following stakeholers: {0}', 
                    GetVar('StakeholderList'))) 
    
   )

FetchXML : GetCurrentStageName

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