Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Get list of Accounts w...
Microsoft Dynamics CRM (Archived)

Get list of Accounts who don't have a specific product

(0) ShareShare
ReportReport
Posted on by yleclerc 1,547

We use Contracts and Contract Lines in CRM. So when we want to know which Accounts own a specific product, we can easily use Advanced Find in Contract lines and specify which product we are targetting.

Now, what if we want to sell this product to all our Accounts who don't own this product? Is there a way to get this data directly from CRM?

So far, the only solution we found is export to Excel all our Accounts(1). Then export to Excel the list of Accounts who own the product(2). Then we cross-reference both lists in Excel.

Is there another way we could get the data without using Excel?

*This post is locked for comments

  • yleclerc Profile Picture
    yleclerc 1,547 on at
    RE: Get list of Accounts who don't have a specific product

    Thank you Rawish. But there is no direct link between Accounts and Products. The link exists through Accounts->Contracts->Contract Lines->Products.

  • yleclerc Profile Picture
    yleclerc 1,547 on at
    RE: Get list of Accounts who don't have a specific product

    David, I think this is brilliant. Of course it's a 2 step process. But I wasn't expecting any magic here. Marketing List will be the way to go for us.

    Fetch XML is not an option for us as I have to train a group of users with no code knowledge.

  • Verified answer
    David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: Get list of Accounts who don't have a specific product

    I don't think you can do this in one query (either Advanced Find, or FetchXml). The fundamental problem is that you're trying to do a NOT IN query across 2 joins (account to contract, then contract to contractline), and FetchXml can't cope with that. You can find all contracts that don't include a particular product, but the problem is accounts may have more than one contract. If you can be sure that accounts can only have 1 current contract, you could create a FetchXml query to get those contracts, and hence the accounts for those contracts. If this is the case, let us know and I can give an example query. Note that you'd have to execute this FetchMl via a report, not Advanced Find, due to limitations of the Advanced Find UI

    To keep away from Excel, you could using marketing lists:

    1. Create a list for accounts, and add all accounts (or all you may be interested in)
    2. Use the option 'Use Advanced Find to Remove List Members' to use your Advanced Find for all accounts with the product

    The list will then only have the accounts without the product

  • Jarrod Williams Profile Picture
    Jarrod Williams 1,747 on at
    RE: Get list of Accounts who don't have a specific product

    There are two decent ways.  

    1) If you want to stay inside the system, do it with an advanced find but you have to start backwards on those and start with the product and go from there.

    2) The better solution for something like this and the more powerful one when you are talking querying is to do it with a report.  Even a FetchXML report for an Online org is going to be able to do outer joins to get you the information that you need.  

  • Suggested answer
    Rawish Kumar Profile Picture
    Rawish Kumar 13,756 on at
    RE: Get list of Accounts who don't have a specific product

    Hi Yvan,

    first of all forgive me if i didint understand this correctly , but what if you try this:

    Look for Accounts

    related entity :Product

    product name or id doesnt not equal "name or id of the product".

    doing this should give you accounts which doesnt have specific product.

    you can then create a view use that in your marketing list and  target them.

  • Suggested answer
    Sreevalli Profile Picture
    Sreevalli 3,256 on at
    RE: Get list of Accounts who don't have a specific product

    Hi,

    unfortunately we cannot use advance find for outer joins, as you rightly said the only option is excel.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,489 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans