Was looking at V9 tonight.. BTW, you know it’s out there, right? If not, go ahead and create a trial – that’ll be V9.
Either way, I wanted to test a feature many of us were waiting for which is, basically, the ability to use Advanced Find to answer the same old question: what are all the accounts that have no opportunities? And you can replace those two entity names with just about any combination of related entities.
It did not take long to realize that it is there now! Although it did take a bit longer to realize that it’s not fully there. I will explain – see below.
In V9, there are two types of filters we can choose for the related entities: “Contains Data” and “Does Not Contain Data:
“Contains Data” provides the same filter behavior we had in the earlier versions, so there is nothing different there. “Does Not Contain Data”” is what allows us to answer the kind of questions I mentioned above. BUT there is a catch:
If I try to use “Does Not Contain Data” filter, all my filters on the related entity will be deleted. In other words, I can find all accounts that do not have ANY opportunities. But, for example, I cannot find those accounts that do not have opportunities in the current fiscal year, and that’s, likely, a more realistic question.
Which means we can certainly use this new functionality but only to a limited extent. For the more advanced search scenarios, it seems we may still have to utilize custom FetchXml just like we used to do it in the earlier versions: http://www.itaintboring.com/dynamics-crm/two-most-ignored-features-of-fetchxml/
So, is it possible to retrieve the list of accounts which have no opportunities?
It’s possible if there are no conditions. But, if you want to add a condition on the opportunities(accounts which have no opportunities in the current fiscal), then, it seems, it’s still not possible through the advanced find. You’ll need to customize FetchXml (there is a link in the post), and, then, you can either create a view using that fetch or you can use it in the SSRS report.
Like I said.. it’s almost there (but not quite, yet)
How about using the ‘Contains Data’ filter on related entity and then filter out the related records using related entity filters?
I think contains data won’t be different from pre-v9. It’ll be a cross join of the main entity with the related entity + filters.. which means you won’t be able to do “not exists”, and that’s the point.
The other option is https://appsource.microsoft.com/en-us/product/dynamics-365/cobalt.369337d7-2c59-4a6c-bcd5-529cf74d93e1?tab=Overview