How to get last annotation in Advanced Find/CRM report RRS feed

  • Question

  • Hi,

    I'm trying to get Cases that have been not updated in X days. So I came until this query that is not retrieving correct results, and I think is due to is not taking last annotation date.

    So, a Case can have multiple Annotations, any before 18/08/2014 (as it's in the filter), so that's why I think I got this Cases in the results.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
      <entity name="incident">
        <attribute name="title" />
        <attribute name="createdon" />
        <attribute name="incidentid" />
        <filter type="and">
          <condition attribute="modifiedon" operator="on-or-before" value="2014-08-18" />
          <condition attribute="statecode" operator="eq" value="0" />
          <condition attribute="statuscode" operator="ne" value="2" />
        <link-entity name="annotation" from="objectid" to="incidentid" alias="aa">
          <filter type="and">
            <filter type="or">
              <condition attribute="createdon" operator="on-or-before" value="2014-08-18" />
              <condition attribute="createdon" operator="null" />

    I have tried to order the Annotattions iwth this without luck:

    <link-entity name="annotation" from="objectid" to="incidentid" alias="ah" link-type='outer'>
     <order attribute="createdon" descending="false" />
          <attribute name="createdon" />
          <attribute name="modifiedon" />   

    How can I accomplish this using Advanced Find or FetchXML?

    Thursday, August 21, 2014 2:29 PM

All replies

  • When you say cases that have not been updated in X days, what criteria means not updated for you? You reference annotations which are notes. Is this what you mean, a case where a note has not been added in the last x days?

    In your first fetchxml code, you will get a list of cases that have annotations created on or before 2014-8-18 even if there are annotations dated later but there must be at least one annotation dated before the 2014-8-18 to be included in the results. There is no point included created on operator null because all CRM records have a value for createdon.

    Adding descending="false" will not help. All that does is to change the order in which the results are displayed, it has no effect on the records that are selected.

    Sunday, August 24, 2014 10:38 PM
  • Hi Feridun,

    thanks for reply, I have finish now this report.

    The criteria that I have to follow is: Case have not been modified in two days AND a note has not been added to it.

    Case.ModifiedOn <= 2 days and Annotation.CreatedOn <= 2days

    Finally I'll be using SQL, as its more complete that FetchXml. This is the query:

    select max(note.createdon) AS LastNoteDate, incident.title, incident.owneridname
    from filteredannotation as note
     right join FilteredIncident as incident on note.objectid = incident.IncidentId
     where incident.modifiedon <= (dateadd(day, -2, getdate())) and incident.statecode = '0' and statuscode != '2'
    group by  incident.IncidentId,  incident.owneridname, incident.title
    order by  incident.title

    Also, I have a filter in the DataSet with an internal report parameter with a default value of: =DateAdd(DateInterval.Day,-2,Now()) to compare with max(note.createdon)

    Monday, August 25, 2014 10:36 AM
  • Glad you sorted this out and using SQL is much easier, I think, than FetchXML. Of course if you have CRM Online then SQL is not an option.
    Tuesday, August 26, 2014 12:59 AM