Aggregate function with FetchXML

Question

I need to fetch the entity who has the max date in a certain field. I tried the code below with Stunnware but it gives me an error that the MAX function is invalid.

  <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true'>
    <entity name='field1'>
      <attribute name='field2' />
      <attribute name='field3' />
      <attribute name='field4' />
      <order attribute='field1' descending='false' />
      <link-entity name='contact' from='field1' to='otherfield' alias='ac'>
          <filter type='and'>
          <condition attribute='field5' operator='eq' value='123456' />
           </filter>
      </link-entity>
     <link-entity name='secondentity' from='field2' to='otherfield' visible='false' link-type='outer' alias='a_6c61a84be522e31194080050569c4325'>
         <attribute name='date' alias='maxdate' aggregate='max' />
      </link-entity>
   </entity>
   </fetch>

Can you help point me to the mistake i'm doing ?

score 4 · Accepted Answer · answered Apr 08 '14 at 15:25

4

It turned out that it will not work:

There has been several problems in my query:

1- According to Paul Way's reply, my fetch xml was missing aggregate="true"

2- Aggregate functions won't work with Order attribute

3- If I'm going to retrieve attributes while using the aggregate function I have to groupby them and add an alias

4- Aggregate function MAX cannot be applied on Date types.

So my other solution is to retrieve all the dates in descending order and then I will use the first entity retrieved.

answered Apr 08 '14 at 15:25

user3340627

3,023
6
35
80

Please read your answer again. i used max and min fuctions today for date fields working great. Then you tell point 4.? – Siddique Mahsud Nov 19 '14 at 12:52
Are you using CRM 2011 or 2013 ? – user3340627 Nov 19 '14 at 15:14
Point 4 is applicable on CRM 2011 and not 2013 as mentioned by the user "Flo" below. – user3340627 Nov 20 '14 at 08:44

score 1 · Answer 2 · answered Apr 08 '14 at 14:41

Here's a good example: http://msdn.microsoft.com/en-us/library/gg309565.aspx

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
    </entity> 
</fetch>

Should be something like this for your above XML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true">
    <entity name="myentity">
        <attribute name="personname" />
        <order attribute="personname" descending="false" />
        <link-entity name="mysecondentity" from="personid" to="secondpersonid" visible="false" link-type="outer" alias="aa">
            <attribute name="date" alias='date_max' aggregate="max" />
        </link-entity>
    </entity>
</fetch>

Thank you for your quick reply. I tried the code above but I still got an error "Not Found". I have another link-entity in my original code, I've updated my post now with my full fetch xml with the update you suggested. I tried to run the code without the aggregate it returned results. — user3340627, Apr 08 '14 at 15:04

score 1 · Answer 3 · answered Aug 17 '14 at 13:53

I just got had the same experience. To make a long story short: SUM, AVG, MIN and MAX are not possible for date fields in CRM 2011. In CRM 2013 MIN and MAX are working for date fields.

So in 2011, the only way is to either select everything and do the work on your own or select the data, order by it and set pagesize to 1, to get just the max or min value.

Aggregate function with FetchXML

3 Answers3