locked
It is posible to write this query with FetchXml RRS feed

  • Question

  • Hi, I have to write a report with these conditions:

    Case has not been modified in two days(modifiedOn) .

    That includes the notes also (will have to check createdOn and modifiedOn)

    I've write the query in SQL, but getting a lot of trouble trying to write it in FetchXml, would any one will be able to help me?

    select i.Title, an.CreatedOn, an.ModifiedOn
    
    from incident i left join Annotation an on i.IncidentId = an.ObjectId
    
    where i.StateCode = '0' and i.ModifiedOn <= '2014-08-17'
    
    and (
    ( an.CreatedOn is null or an.CreatedOn <= '2014-08-17')
    and
    ( an.ModifiedOn is null or an.ModifiedOn <= '2014-08-17')
    
    )order by i.Title
    This is what I've done, but is not taking into account conditions for annotations.
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
      <entity name="incident">
        <attribute name="title" />
        <attribute name="ticketnumber" />
        <attribute name="createdon" />
        <attribute name="aux_account" />
        <attribute name="incidentid" />
        <order attribute="title" descending="false" />
    
    <filter type="and">
    	   <condition attribute="modifiedon" operator="on-or-before" value="2014-08-15" />
    	   <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    
    
    <link-entity name="annotation" from="objectid" to="incidentid" alias="ad" link-type='outer'>
    	<attribute name="createdon" />
    	<attribute name="modifiedon" />
    
    	<filter type="and">
    		<filter type="or">		
    		        <condition entityname='ad' attribute="createdon" operator="on-or-before" value="2014-08-15" /> 
    			<condition entityname='ad' attribute="createdon" operator="not-null" />
    	        </filter>
    
    		<filter type="or">
    			<condition entityname='ad' attribute="modifiedon" operator="on-or-before" value="2014-08-15" />
    			<condition entityname='ad' attribute="modifiedon" operator="not-null" />
    	        </filter>
            </filter>
    
       </link-entity>
    
    
     </entity>
    </fetch>


    Tuesday, August 19, 2014 11:24 AM

All replies

  • use this condition:-

    <condition attribute="modifiedon" value="2" operator="last-x-days"/>


    Regards Faisal

    Tuesday, August 19, 2014 1:07 PM
  • Hi Faisal,

    thanks for your reply, but that operator is giving me records that have been modified within two days, the opposite that I want.

    Tuesday, August 19, 2014 1:15 PM
  • Just a try. It might not work:-

    <condition attribute="modifiedon" value="2" operator="olderthan-x-days"/>


    Regards Faisal

    Tuesday, August 19, 2014 1:41 PM
  • Another way to do this in a report is to add a new Report Paramater and give it a default value of 2 days before today using datediff, then use this parameter in your FetchXML, so the fetch only needs to look for records before that date.

    Pete

    Tuesday, August 19, 2014 1:48 PM
  • yep, I'll have to use a parameter, however I wanted to test first as it is easier like this.

    The problem is that my FetchXml is not working as it should.

    Tuesday, August 19, 2014 1:51 PM