How Can I Find All Accounts Who Have Not Modified Any Activity in Last One Month Using FetchXMl

Question

Is it possible to find all accounts who has not modified any activity in last one month in CRM. I have tried one but i am not sure whether it is correct or not. can anybody please help me. I am not sure whether it is possible or not.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="account">
<attribute name="name" />
<attribute name="primarycontactid" />
<attribute name="telephone1" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
<link-entity name="activitypointer" from="regardingobjectid" to="accountid" alias="au" link-type="outer">
  <filter type="and">
    <condition attribute="modifiedon" operator="olderthan-x-months" value="1" />
  </filter>
</link-entity>

Answer 1

I'm afraid that it is not possible to get such kind of fetch xml. You will have to implement your logic in 2 steps:

1. Retrieve all accounts.

2. Remove all the accounts that have activities that were modified during last month.

Answer 2

I think the best way to do this without making multiple queries is to create a custom field on the account entity and then have a plugin that populates that field whenever an activity related to the account is updated. That will make your query a lot easier and work in CRM.

Answer 3

I guess your problem here is that you will only get Accounts that have an associated activity. While your query also needs to return accounts that have no associated activities as well.

What you need is a Left Outer Join. This is only available in 2013 so, if you are upgrading any time soon, you will be able to add the following condition to your query to get the required results.

<condition entityname='account' attribute='regardingobjectid' operator='null'/>