{Code Tip} Get record ID using FetchXml for any entity record based on MAX or MIN field value

I had written some posts a while back on Aggregate FetchXml queries here:

https://dynamicsofdynamicscrm.com/2014/07/23/aggregate-fetchxml-queries-for-dynamics-crm-20112013/

https://dynamicsofdynamicscrm.com/2015/10/01/code-tipaggregate-fetchxml-queries-code-for-dynamics-crm/

Recently in a project somebody referred to my query to first get the highest opportunity estimated revenue and then used a second query for getting the record id by passing a condition for highest revenue returned via the first aggregate query.

There is a lot simpler approach and single query which can be used for scenarios like:

“Get the recordid for the record where field is maximum across all records”

or

“Get the recordid for the record where field is minimum across all records”

Here are some examples below with Opportunity and estimated revenue on CRM online trial data:

Opportunity with highest estimated revenue

<fetch top=“1” >

  <entity name=“opportunity” >

    <attribute name=“name” />

    <attribute name=“estimatedvalue” />

    <filter type=“and” >

      <condition attribute=“estimatedvalue” operator=“not-null” />

    </filter>

    <order attribute=“estimatedvalue” descending=“true” />

  </entity>

</fetch>

Output is as below:

Opportunity with lowest estimated revenue

<fetch top=“1” >

  <entity name=“opportunity” >

    <attribute name=“name” />

    <attribute name=“estimatedvalue” />

    <filter type=“and” >

      <condition attribute=“estimatedvalue” operator=“not-null” />

    </filter>

    <order attribute=“estimatedvalue” />

  </entity>

</fetch>

Output is as below:

However, in practical scenarios it is always good to do a comparison with second highest or second lowest Opportunity estimated revenue(even more or can filter by some id fields/etc.).

Top 2 opportunities with highest estimated revenue

Output i

<fetch top=“2” >

  <entity name=“opportunity” >

    <attribute name=“name” />

    <attribute name=“estimatedvalue” />

    <filter type=“and” >

      <condition attribute=“estimatedvalue” operator=“not-null” />

    </filter>

    <order attribute=“estimatedvalue” descending=“true” />

  </entity>

</fetch>

Output is as below:

Bottom 2 Opportunity with lowest estimated revenue

<fetch top=“2” >

  <entity name=“opportunity” >

    <attribute name=“name” />

    <attribute name=“estimatedvalue” />

    <filter type=“and” >

      <condition attribute=“estimatedvalue” operator=“not-null” />

    </filter>

    <order attribute=“estimatedvalue” />

  </entity>

</fetch>

Output is as below:

Hope it helps and Happy CRMing!

ABOUT THE AUTHOR:

Twitter: https://twitter.com/msdynamicsblog
LinkedIn: https://www.linkedin.com/in/deepesh-somani-00296932

Google Play Store:

https://play.google.com/store/apps/details?id=com.dynamicsofdynamicscrm.msdynamicsblog&hl=en