Download on Github

Create a FetchXml based field in Dynamics CRM

Table of contents

How it works

FetchXmlCalculatedField allows to create a readonly field based on a Fetch Xml results.


Configuration

1. import the solution FetchXmlCalculatedField.

2. Go to advanced seach and search for "FetchXml Calculated Field Configurations" entity and create a new record

3. Fill in Configuration Name and FetchXml Fields, then fields that appear after the Xml analysis and save

4. Copy configuration Id

5. Go to desired form, and add any text field, and search for FetchXmlCalculatedField in controls Tab Paste Configuration Id in the parameter then save and publish.

6. Give read permission to all users for "FetchXml Calculated Field Configuration" Entity.


FetchXml Calculated Field Configuration Entity

  • Name : The configuration name
  • Configuration Id : (Read only) Guid of the configuration
  • FetchXml : FetchXml request. #id parameter takes the current record id (See samples section for more details)
  • Aggregation : Appears after The fetchXml analysis
    • First Record : Gets only the first record of the fetchXml results
      If more than one attribute are defined, the result will concatenate all the defined attributes using "Concat attributes separator"
    • Records Count : Gets the number of fetchXml results
    • Sum : Gets the sum of fetchXml results.
      This option appears only if all fetchXml attributes are numbers(float, integer or decimal) or currencies.
      Mixed types (number and currency) are not allowed
    • Average : Gets the average of fetchXml results.
      If more than one attribute are defined, the average will be calculated on the sum of all the defined attributes
      This option appears only if all fetchXml attributes are numbers(float, integer or decimal) or currencies.
      Mixed types (number and currency) are not allowed
    • Concatenate : Gets the concatenation of fetchXml results.
      If more than one attribute are defined, the result will concatenate all the defined attributes using "Concat attributes separator"
  • Default Currency : Defines the currency to use for the sum or average
    This option appears only if all fetchXml attributes are currencies.
  • Concat attributes separator : Separator used for the concatenation of attributes (more than one attribute defines)
  • Concat lines separator : Separator used for the concatenation of lines (more than one result retrieved)

Samples

Sample 1 : Opportunities Count 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> 
    <entity name="opportunity"> 
    <attribute name="opportunityid" /> 
        <order attribute="name" descending="false" /> 
        <filter type="and"> 
            <condition attribute="parentaccountid" operator="eq" uitype="account" value="#id" /> 
        </filter> 
    </entity> 
</fetch>
Result :

Sample 2 : Last Opportunity Status 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> 
    <entity name="opportunity"> 
        <attribute name="name" /> 
        <attribute name="statuscode" /> 
        <order attribute="createdon" descending="false" /> 
        <filter type="and"> 
            <condition attribute="parentaccountid" operator="eq" uitype="account" value="#id" /> 
        </filter> 
    </entity> 
</fetch>
Result :

Sample 3 : Opportunities Sum 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> 
    <entity name="opportunity"> 
        <attribute name="actualvalue" /> 
        <order attribute="name" descending="false" /> 
        <filter type="and"> 
            <condition attribute="parentaccountid" operator="eq" uitype="account" value="#id" /> 
        </filter> 
    </entity> 
</fetch>
Result :

Sample 4 : All Opportunities 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> 
    <entity name="opportunity"> 
        <attribute name="name" /> 
        <attribute name="createdon" /> 
        <order attribute="createdon" descending="false" /> 
        <filter type="and"> 
            <condition attribute="parentaccountid" operator="eq" uitype="account" value="#id" /> 
        </filter> 
    </entity> 
</fetch>
Result :

Sample 5 : Opportunities Average 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> 
    <entity name="opportunity"> 
        <attribute name="actualvalue" /> 
        <filter type="and"> 
            <condition attribute="parentaccountid" operator="eq" uitype="account" value="#id" /> 
        </filter> 
    </entity> 
</fetch>
Result :


Solution Downloads

Dynamics CRM Solution

FetchXmlCalculatedField Solution 1.0.0.0