Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All 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