Blogs

How to get direct count using Fetch XML capabilities

Inkey, February 27, 2024 3137 Views

FetchXML in Dynamics 365 is a query language used to retrieve data from the system. It is a powerful and flexible tool that allows users to define complex queries to retrieve specific data from Dynamics 365 entities.

An aggregate function into FetchXML queries, we can enhance data retrieval capabilities in Dynamics 365. Whether we can calculate averages, count records, or find maximum or minimum values, FetchXML’s versatility allows us to gain deeper insights into your data.

• Avg (Average): The Avg function is used to calculate the average value from a set of records. This can be particularly useful when you need to analyze numerical data across multiple records.
• Count: Count allows you to retrieve the total number of records based on certain criteria. This is handy for generating reports or understanding the volume of data that meets specific conditions.
• Max (Maximum): The Max function retrieves the maximum value from a set of records. This is useful when you want to identify the highest value within a particular field.
• Min (Minimum): Conversely, the Min function helps you find the minimum value from a set of records. This can be valuable for identifying the smallest value within a specific field.

In Microsoft Dynamics 365, it can also be employed to efficiently determine the count of records that meet specific criteria. In this blog post, we’ll explore how to leverage FetchXML capabilities to directly obtain record counts in Dynamics 365.

Get records count directly using FetchXML:

To directly obtain a record count using FetchXML, you can structure your query to include the <aggregate> element with the aggregate=”count” attribute.

In FetchXML, the <aggregate> attribute is used to perform aggregate functions on attributes (fields/columns) of the queried data. Aggregate functions allow you to retrieve summarized information about your data, such as the sum, average, minimum, maximum, or count of a particular attribute.

Here’s a basic example:

Below is one FetchXML of the contact entity with some conditions.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="new_contact">
    <attribute name="new_contactid" />
    <attribute name="new_name" />
    <attribute name="createdon" />
    <order attribute="new_name" descending="false" />
    <filter type="and">
      <condition attribute="new_name" operator="not-null" />
      <condition attribute="new_date" operator="not-null" />
    </filter>
  </entity>
</fetch>

If we need to get the only the record count which is filtered by condition, then as per the below example, we can enhance and customize to find out the total count of records.

The attributes “returntotalrecordcount” is used to indicate whether the result should include the total number of records that match the specified criteria, regardless of the number of records returned in the current result set.

<fetch returntotalrecordcount="true" aggregate="true">
  <entity name="new_contact">
    <attribute name="new_name" alias="NameGroup" groupby="true" />
    <attribute name="new_date" alias="MaxDate" aggregate="max" />
    <attribute name="new_date" alias="MinDate" aggregate="min" />
    <attribute name="new_number" alias="Number" aggregate="sum" />
    <attribute name="new_contactid" alias="Count" aggregate="count" />
    <filter type="and">
      <condition attribute="new_name" operator="not-null" />
      <condition attribute="new_date" operator="not-null" />
    </filter>
  </entity>
</fetch>

After using the fetchXML, we can get results like below.

Conclusion:

Pros:

• Accuracy Gauge: We can efficiently retrieve the count of 50,000 records with speed and accuracy.

Cons:

• Limitation: If the count exceeds 50,000, the system will generate an error message to notify the user.
• Complexity: Incorporating aggregate functions can increase the complexity of FetchXML queries, making them more challenging to write and understand, especially for users unfamiliar with these functions.
• Limited to Numeric Fields: Aggregate functions like Avg, Max, and Min are primarily applicable to numeric fields. Users may find limitations when attempting to apply these functions to non-numeric data.

Know the number of records in any Dataverse entity or table.
https://www.inkeysolutions.com/entity-record-counter

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator