FetchXML Tutotorial

FetchXML Distinct in Dynamic Crm

FetchXML Distinct
Here Laxmikant has explained with an example, FetchXML Distinct in Dynamic Crm.

The DISTINCT statement is used to return only distinct (different) values.
Inside a entity, a attribute often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
FetchXML Distinct Syntax

<fetch mapping="logical" distinct="true" version="1.0">
  <entity name="entity_name">
    <attribute name="attribute_name1" />
    <attribute name="attribute_name2" />
  </entity>
</fetch>

Below is a show all records from the “Customers” entity
fetchxml_distict

FetchXML Example Without DISTINCT
Key Point:
cr685_customers:It is a logical name of entity.
cr685_country:It is a logical name of attribute/field.

<fetch mapping="logical" version="1.0" >
    <entity name="cr685_customers" >
        <attribute name="cr685_country" />
    </entity>
</fetch>

Result

<result>
    <cr685_country>India</cr685_country>
    <cr685_customersid>{67D05991-A5B5-E911-A97A-000D3AF06590}</cr685_customersid>
  </result>
  <result>
    <cr685_country>US</cr685_country>
    <cr685_customersid>{F2E6CEDA-A5B5-E911-A97A-000D3AF06590}</cr685_customersid>
  </result>
  <result>
    <cr685_country>India</cr685_country>
    <cr685_customersid>{89DD15A9-AAB5-E911-A97A-000D3AF06590}</cr685_customersid>
  </result>
  <result>
    <cr685_country>India</cr685_country>
    <cr685_customersid>{006C75BF-AAB5-E911-A97A-000D3AF06590}</cr685_customersid>
  </result>
  <result>
    <cr685_country>India</cr685_country>
    <cr685_customersid>{1919A1DF-AAB5-E911-A97A-000D3AF06590}</cr685_customersid>
  </result>

Now, let us use the DISTINCT keyword with the above fetchxml query and see the result.
FetchXML DISTINCT Examples
The following fetchxml query selects only the DISTINCT values from the “Country” attribute in the “Customers” entity:

<fetch mapping="logical" distinct="true" version="1.0" >
    <entity name="cr685_customers" >
        <attribute name="cr685_country" />
    </entity>
</fetch>

Result:

<result>
    <cr685_country>India</cr685_country>
  </result>
  <result>
    <cr685_country>US</cr685_country>
  </result>

The following fetchxml Query lists the number of different (distinct) customer countries:

<fetch mapping="logical" aggregate="true" version="1.0" >
    <entity name="cr685_customers" >
        <attribute name="cr685_country" alias="COUNT_DISTINCT_Country" aggregate="countcolumn" distinct="true" />
    </entity>
</fetch>

Result

<result>
    <COUNT_DISTINCT_Country formattedvalue="2">2</COUNT_DISTINCT_Country>
  </result>

Any question and suggestion related to this article please comment me. Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *