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);
*This post is locked for comments
I have the same question (0)
