locked
Can I create a view in MS CRM that gives me all accounts that have zero contact? RRS feed

  • Question

  • Hi,

    Can I create a view in MS CRM that gives me all accounts that have no or zero contact associated? Thanks

    Thursday, May 13, 2010 10:57 AM

Answers

  • Correct, neither FetchXml nor QueryExpression can perform that query.  The only way to query that would be to use a LINQ query from accounts with a subquery of contacts with a count.  This would bring all of the contacts back to the client and perform that count and calculations from there.  The code would be simple but the query incredibly non-performant.  For  a small deployment that probably would not be much of a problem.  Check out the LINQ capabilities in the latest 4.0.0012 SDK.

    Shan McArthur
    www.shanmcarthur.net

    Friday, May 14, 2010 3:39 AM
    Moderator

All replies

  • Hi  try this.

    Go to Advanced Find.

    1) Create  "New View"

    2) Select "Account" in Look For Dropdown

    3) in  criteria, select  go to related entites ==> Contacts(Parent Customer).  Here Select  Parent Customer Doesn't Contain Data. Critera will  now look like  below

        Contacts  (Parent Customer)

                         Parent Customer Doesn't Contain Data


    Muhammad Ali Khan
    http://malikhan.wordpress.com
    Thursday, May 13, 2010 11:04 AM
  • Hi,

    you can create a new view in Account entity and set filter criteria as "Primary Contact" Does not Contain Data


    Mahain http://mahenderpal.wordpress.com
    • Proposed as answer by Aarch Thursday, May 13, 2010 11:12 AM
    Thursday, May 13, 2010 11:08 AM
    Moderator
  • Hi Hassan,

    you need to use Maketing list to get the releated result, check following steps

    1. create new account marketing list (let's call it "TESTACCOUNTLIST)

    2. add all accounts (using adavaced find) and click ok, so your marketing list have got all system account now.

    3. Use Remove member option (using adavaced find)

             Advanced find will be somethink like as follows (just add the relationship and you do not need to add any condition in this advanced find)

               Look For Account 

                   Contacts(Parent Customer)

     this will return all the accounts which have got contacts, click Okay and then system will remove all accouns which have got contacts from yout marketing list

     

    4. Now you got marketing list which have only account which does not have contact record

    5. Now you can do advanced find as follows

             Look For Account 

             Marketing List Equals "TESTACCOUNTLIST"

    6. This should give you desired result.

     

    Other option is to direcly Run custom query on database or create your own customer CRM Report for this

    Hope this helps..

     

    Thursday, May 13, 2010 11:37 AM
  • Thanks for the quick responses. I have tried all above mentioned ways but not succeeded. Please any body translate that query in to crm advance find query

     

    SELECT * FROM accountbase where accountid NOT IN (SELECT accountid FROM contactbase WHERE accountid IS NOT NULL)

     

    Actually this above mentioned query returns me the desired result. I have tried that

    Contacts  (Parent Customer)

                         Parent Customer Doesn't Contain Data 

    way. But this doesn't return any record.


    Thursday, May 13, 2010 4:46 PM
  • Not returning any records could be the right answer.  Just to be sure, go to the database and query:

    select count(*) from accountbase a left join contactbase c on c.accountid = a.accountid where c.accountid is null

    I don't think the "doesn't contain data" approaches will work; fetchxml does NOT do outer joins. 

    Thursday, May 13, 2010 6:10 PM
  • Correct, neither FetchXml nor QueryExpression can perform that query.  The only way to query that would be to use a LINQ query from accounts with a subquery of contacts with a count.  This would bring all of the contacts back to the client and perform that count and calculations from there.  The code would be simple but the query incredibly non-performant.  For  a small deployment that probably would not be much of a problem.  Check out the LINQ capabilities in the latest 4.0.0012 SDK.

    Shan McArthur
    www.shanmcarthur.net

    Friday, May 14, 2010 3:39 AM
    Moderator