locked
CRM 2015 Online, FetchXML aggregate does not return row id RRS feed

  • Question

  • Hi,

    I have the following fetchXml.  The problem is it returns 3 columns, myEntityId, maxdate, and contactid.  However, myEntityId is Null for all rows.  Any solution?

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
      <entity name="myEntity">  
        <attribute name="createdon" aggregate="max" alias="maxdate" />
        <attribute name="contactlookup" groupby="true" alias="contactid" />
        <link-entity name="contact" from="contactid" to="contactlookup" alias="aa">     
          <filter type="and">
            <condition attribute="myCheckbox" operator="ne" value="1" />
          </filter>
        </link-entity>   
      </entity>
    </fetch>

    Wednesday, April 13, 2016 2:56 AM

Answers

  • An aggregate query only returns data for fields that either have an aggregate expression (createdon in this query), or are grouped by (contactid). myEntityId is neither.

    I don't think you can get all this in one query; you'll need a second query to get the myEntityId of records which match the contactid and createdon. Also note that it is theoretically possible that you'd have 2 or more myEntity records with the same contactid and max createdon, so you'd need to decide which record(s) to use.

    Another option would be to return all the myEntity records, sorted by contactid and createdon (desc), then pick out the records you need in the results


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by chicago bq Friday, April 15, 2016 2:00 PM
    Thursday, April 14, 2016 5:54 AM
    Moderator

All replies

  • what you are trying to do exactly?

    My blog: www.crmanswers.net - CRM Theme Generator

    Wednesday, April 13, 2016 7:25 AM
  • Get the latest createdon for each contactid in this myEntity.  myEntity is associated with Contact by contactlookup.  In myEntity, each contact could have many createdon dates.  The fetchXml gets the latest createdon date for one contact.  Meanwhile, I hope it returns myEntityId as well.
    Wednesday, April 13, 2016 2:49 PM
  • An aggregate query only returns data for fields that either have an aggregate expression (createdon in this query), or are grouped by (contactid). myEntityId is neither.

    I don't think you can get all this in one query; you'll need a second query to get the myEntityId of records which match the contactid and createdon. Also note that it is theoretically possible that you'd have 2 or more myEntity records with the same contactid and max createdon, so you'd need to decide which record(s) to use.

    Another option would be to return all the myEntity records, sorted by contactid and createdon (desc), then pick out the records you need in the results


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by chicago bq Friday, April 15, 2016 2:00 PM
    Thursday, April 14, 2016 5:54 AM
    Moderator
  • Thanks, "you'll need a second query to get the myEntityId of records which match the contactid and createdon. " is the answer.  I implemented the logic in SSIS.  I just have to Merge Join one source in FetchXml above with another source in myEntity itself.  Map contactid to contactLookup, and map maxdate to createdon. That's it. Thanks.
    Friday, April 15, 2016 2:07 PM