Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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 namefrom account awhere a.statecode = 0 andnot exists(select *from contact cwhere c.parentaccountid = a.accountidand c.statecode <> 0);
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 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.
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">
<attribute name="name" />
<attribute name="accountid" />
<order attribute="name" descending="false" />
<condition attribute="statecode" operator="eq" value="0" />
<link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer">
<condition attribute="scheduledstart" operator="last-x-days" value="90" />
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
That helped, thanks!
Business Applications communities