Overview

This example shows how you can define FetchXML in one place (xCache) and reuse them in your formulas. 

To demonstrate this functionality, we will create a formula that references a FetchXML query that is stored in a xCache record.

North52 Decision Suite

The North52 Decision Suite solution works like this:

• A xCache record is created that stores a FetchXML query that finds all the open Opportunities of an Account
• A formula is created that counts up the result of the FetchXML and displays this count on the form of an Account
• A Quick Button is added to an Account form that will trigger the formula when clicked
  

xCache

• Navigate to Settings > N52 xCache
• Click New
• For Category enter FetchXML
• For Base Key enter FetchXML_Accounts_with_open_Opportunities
• Scroll to Value (secured), copy and paste the following FetchXML:

  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="opportunity">
      <attribute name="name" />
      <attribute name="customerid" />
      <attribute name="estimatedvalue" />
      <attribute name="statuscode" />
      <attribute name="opportunityid" />
      <order attribute="name" descending="false" />
      <filter type="and">
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="parentaccountid" operator="eq" uitype="account" value="{0}" />
      </filter>
    </entity>
  </fetch>

• Click Save
  

Formula

• Navigate to Settings > N52 Formula
• Click New
• Leave Source Entity at Account
• Set Formula Type to ClientSide - Calculation
• Select Classic Editor
• Click Create
• Change the name to Show how many Account with open Opportunities
• In the Editor, copy and paste the following formula:

  Smartflow(
  
    SetVar('AccountswithOpenOpportunities',
      FindRecordsFetchXml( StringFormat(xCacheGetGlobal('FetchXML_Accounts_with_open_Opportunities'), [account.accountid] ))), 
  
    SetFormNotification('Number of open Opportunities : ' +   FindECCount(GetVar('AccountswithOpenOpportunities')),'INFO')
  
  )

• Click Save
  
• Note : You can add the xCache function by selecting it from the Source tab, then expanding the xCache node, followed by FetchXML
  

Quick Button

Please see the article here for instructions on how to add a Quick Button to a form. 

Testing 

To test, click the button and you will see the form notification display the amount of open Opportunities for this Account.