While working with Microsoft Dynamics CRM we wanted to make query or advanced find to list down all the account which does not have any opportunity associated to it. But in the dynamics CRM with current or earlier version we cannot write down the simple advanced find query which list down the record does not associate to it.
With upcoming update of Microsoft Dynamics 365 CRM, you can perform the Search on that criteria as well. You can design this query in the advanced find.
Query: Get all the account which does not have any opportunities.
1. Open advance find and select account entity and in filter select opportunitie in related entities, as show below.
2. You will notice that a new attribute has been added, by which you can apply additional filter on opportunities.
3. Here you need to select the “Does Not contains data”.
4. Now click on result.
5. It will list down all the records which does not have any opportunities as shown
6. Fetch XML: let see what modification has been done in the fetch XML
<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="opportunity" from="parentaccountid" to="accountid" notinvalue="DoesNotContain" link-type="outer" alias="af" /> <filter type="and" isnotinfilter="true"> <condition entityname="opportunity" attribute="parentaccountid" operator="null" /> </filter> </entity> </fetch>
7. As you can see that it has an addition filter attribute is added.
Query: Get all accounts contains opportunities.
1. Similarly, you can get all the accounts which have opportunities associated to it.
2. Here you need to apply filter criteria as contains data; as shown below.
3. It will list down all the account records with opportunities as shown below.
Hope this helps while doing advanced find or designing fetch Xml query.