This question came from a user today so I thought I’d document how you can create this type of query using Cobalt Intelligent Query. The scenario in question is can I search for orders that contain a specific product but not another product. With Cobalt Intelligent Query you can create this type of query using a little trick that involves nested join conditions. One thing to note, however, is that by default the primary key attribute on the Order Product (salesorderdetail) is not searchable so in order to create this query you will need to customize the Order Product entity first.
1. Make the Primary Key on Order Product Searchable
Goto Customizations -> Customize the System -> Entities -> Order Product -> Fields -> SalesOrderDetailID and select searchable then save and publish your customizations
2. Create the Nested Join Condition Query
There’s a limitation in Advanced Find that won’t let you add the same related entity more than once in your query.
However, with a little trickery you can get around this issue by nesting a link to the parent entity and then linking back to the child entity. Using this trick you can create the following query in Advanced find to get what you’re looking for in this scenario.
That’ll do it. Once you’ve installed and enabled Cobalt Intelligent Query in your Dynamics 365 organization the above query would return all orders that contain the product ‘Cobalt Migration Dynamics’ but don’t contain the product ‘Membership Dynamics’. Any orders that contain both products or only the Membership Dynamics product would not appear in your list. You can download Intelligent Query from our site or install it directly in your Dynamics 365 online tenant from AppSource.