Microsoft Dynamics CRM

{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=estimatedvalueoperator=not-null/>

    </filter>

    <order attribute=estimatedvaluedescending=true/>

  </entity>

</fetch>

Output is as below:

clip_image001[4]

Opportunity with lowest estimated revenue

<fetch top=1>

  <entity name=opportunity>

    <attribute name=name/>

    <attribute name=estimatedvalue/>

    <filter type=and>

      <condition attribute=estimatedvalueoperator=not-null/>

    </filter>

    <order attribute=estimatedvalue/>

  </entity>

</fetch>

Output is as below:

clip_image002[4]

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=estimatedvalueoperator=not-null/>

    </filter>

    <order attribute=estimatedvaluedescending=true/>

  </entity>

</fetch>

Output is as below:

clip_image003[4]

Bottom 2 Opportunity with lowest estimated revenue

<fetch top=2>

  <entity name=opportunity>

    <attribute name=name/>

    <attribute name=estimatedvalue/>

    <filter type=and>

      <condition attribute=estimatedvalueoperator=not-null/>

    </filter>

    <order attribute=estimatedvalue/>

  </entity>

</fetch>

Output is as below:

clip_image004[4]

Hope it helps and Happy CRMing!

ABOUT THE AUTHOR:

clip_image001

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s