SBX - Search With Button

SBX - Forum Post Title

CRM FetchXML Query to retrieve all Accounts that do not have active Contacts

Microsoft Dynamics CRM Forum

Zibba360 asked a question on 8 Apr 2016 9:35 AM
My Badges

Question Status

Verified

I am using CRM 2015 on prem. I have a windows scheduled task that triggers an exe app which the queries CRM and trigger an Action Workflow. Now on my exe app, I want to modify my fetchxml to retrieve only Accounts that  are due for renewal in the next 60 days and in the past 30 days. It should then filter further and return only the Accounts that do not have active Contacts. (An Account may have multiple contacts, if none of them are active, give me that Account for further processing). Frequency is time period for renewal e.g Monthly, Annually, etc...

Here is my FetchXML:

<fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0">
<entity name="account">
<attribute name="accountid"/>
<attribute name="wib_name"/>
<attribute name="createdon"/>
<order descending="false" attribute="wib_name"/>
<link-entity name="contact" alias="ac" to="accountid" from="parentaccountid" link-type="outer"/>
<filter type="and">
<filter type="or">
<filter type="and">
<condition attribute="contact_renewaldate" value="60" operator="next-x-days"/>
<condition attribute="statecode" value="0" operator="eq"/>
<condition attribute="contact_renewal_frequency" value="908840003" operator="eq"/>
<condition entityname="ac" attribute="statecode" operator="ne" value="0"/>
</filter>
<filter type="and">
<condition attribute="contact_renewaldate" value="30" operator="last-x-days"/>
<condition attribute="statecode" value="0" operator="eq"/>
<condition attribute="contact_renewal_frequency" operator="in">
<value>908840002</value>
<value>908840001</value>
<value>908840004</value>
</condition>
<condition entityname="ac" attribute="statecode" operator="ne" value="0"/>
</filter>
</filter>
</filter>
</entity>
</fetch>

My SQL: Note that it doesn't include the 60 days - 30 days period but it does bring only Accounts that do not have active Contact

select name
from account a
where a.statecode = 0 and
not exists(
select *
from contact c
where c.parentaccountid = a.accountid
and c.statecode <> 0);

Reply
Aiden Kaskela (MVP) responded on 8 Apr 2016 12:13 PM
My Badges
Suggested Answer

Hi Zibba,

If you want accounts with no contacts, you can accomplish that easily by adding a left-join condition to your Fetch expression. I wrote up some details on that here: community.dynamics.com/.../451055

Hope this helps! I'd appreciate if you'd mark this as Answering your question.

Thanks,

 Aiden

Reply
Zibba360 responded on 11 Apr 2016 1:37 AM
My Badges

Hi Aiden,

Thanks for the reply. I don't only want the Accounts with no Contacts. Also the Accounts that have Contacts but all those Contacts are Inactive (None of the Account's Contacts is Active). For example, Account named Contoso has 3 Contacts in Joe, John, James. All of these Contacts have been Deactivated. Now that this Account has no Active Contact, bring me that Account in the FetchXML query so that we can appoint a consultant to manage this Account. If James is Activated again, do not bring me this Account in the query as James is managing this Account, even though Joe and John are still Inactive.

I appreciate any help.

Thanks

Reply
Aiden Kaskela (MVP) responded on 11 Apr 2016 6:55 AM
My Badges
Verified Answer

Hi Zibba,

My suggestion will work for that query in FetchXml. Use a left join to account, with a statecode filter on the contact link. Then put a contactid null condition on the parent. Here's an example I wrote in the linked post - it will give you active accounts that don't have an appointment in the last 90 days. Your query would be nearly identical except you're linking to contacts (bolded the relevant parts):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition entityname='appointment'
              attribute='activityid'
              operator='null'/>
    </filter>
    <link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer">
      <filter type="and">
        <condition attribute="scheduledstart" operator="last-x-days" value="90" />
      </filter>
    </link-entity>
  </entity>
</fetch

If you want to run the same query in the advanced find, you can do it with a tool I helped write cobalt Cobalt Intelligent Query - free at http://www.cobalt.net/cobaltintelligentquery

Thanks,

  Aiden

Reply
Zibba360 responded on 11 Apr 2016 8:58 AM
My Badges

Hi Aiden,

That helped, thanks!

Reply
Aiden Kaskela (MVP) responded on 11 Apr 2016 6:55 AM
My Badges
Verified Answer

Hi Zibba,

My suggestion will work for that query in FetchXml. Use a left join to account, with a statecode filter on the contact link. Then put a contactid null condition on the parent. Here's an example I wrote in the linked post - it will give you active accounts that don't have an appointment in the last 90 days. Your query would be nearly identical except you're linking to contacts (bolded the relevant parts):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition entityname='appointment'
              attribute='activityid'
              operator='null'/>
    </filter>
    <link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer">
      <filter type="and">
        <condition attribute="scheduledstart" operator="last-x-days" value="90" />
      </filter>
    </link-entity>
  </entity>
</fetch

If you want to run the same query in the advanced find, you can do it with a tool I helped write cobalt Cobalt Intelligent Query - free at http://www.cobalt.net/cobaltintelligentquery

Thanks,

  Aiden

Reply
Aiden Kaskela (MVP) responded on 8 Apr 2016 12:13 PM
My Badges
Suggested Answer

Hi Zibba,

If you want accounts with no contacts, you can accomplish that easily by adding a left-join condition to your Fetch expression. I wrote up some details on that here: community.dynamics.com/.../451055

Hope this helps! I'd appreciate if you'd mark this as Answering your question.

Thanks,

 Aiden

Reply

SBX - Two Col Forum

SBX - Migrated JS