locked
View accounts that are missing contacts in CRM 2011? RRS feed

  • Question

  • Is this possible?  Can't seem to figure it out, trying to view a list that will show accounts with no contacts so I can have my reps try to fill the ones in that are missing but not sure how to do it!

    I have it showing Primary Contact as a column but only 2 accounts of 700 have that.  I would have assumed that the first contact made under an account would be the Primary Contact but I guess not.  I don't even see anywhere that lets you assign one as that!

    Friday, May 6, 2011 2:01 AM

Answers

  • Hello Lee,

    Here's an SQL script for your scenario:

    --Get Accounts with missing contacts
    select accountid,name,* from account where accountid not in 
    
    --Get All Account with child Contacts
    (select distinct parentcustomerid from contact where parentcustomerid is not null and parentcustomeridtype = 1)
    
    Do you need the output in a CRM View / Advanced Find? Or simply a report?


    Cornel Croitoriu - Senior Software Developer & Entrepreneur

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Biz-Forward.comCroitoriu.NET

    Friday, May 6, 2011 7:00 AM
  • Hello Lee,

    The quickest way and easiest to get your results is to use SQL. Here's a startup: http://msdn.microsoft.com/en-us/library/ms174173.aspx

    The CRM database is named something like YourOrganizationName_MSCRM. Good luck.


    Cornel Croitoriu - Senior Software Developer & Entrepreneur

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Biz-Forward.comCroitoriu.NET

    Friday, May 6, 2011 6:39 PM

All replies

  • Hello Lee,

    Here's an SQL script for your scenario:

    --Get Accounts with missing contacts
    select accountid,name,* from account where accountid not in 
    
    --Get All Account with child Contacts
    (select distinct parentcustomerid from contact where parentcustomerid is not null and parentcustomeridtype = 1)
    
    Do you need the output in a CRM View / Advanced Find? Or simply a report?


    Cornel Croitoriu - Senior Software Developer & Entrepreneur

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Biz-Forward.comCroitoriu.NET

    Friday, May 6, 2011 7:00 AM
  • Thanks I know nothing about SQL but that gives me a start hehe.  Output can be wherever, CRM view would be nice since I could export to Excel and work with it but really just need a view that shows me that.

     

    Friday, May 6, 2011 5:58 PM
  • Hello Lee,

    The quickest way and easiest to get your results is to use SQL. Here's a startup: http://msdn.microsoft.com/en-us/library/ms174173.aspx

    The CRM database is named something like YourOrganizationName_MSCRM. Good luck.


    Cornel Croitoriu - Senior Software Developer & Entrepreneur

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Biz-Forward.comCroitoriu.NET

    Friday, May 6, 2011 6:39 PM
  • Lee, try an Advanced Find like this:

    Account Status = Active

    Contact (Parent Customer)

        Last Name Does Not Contain Data (Or whatever field is mandatory on a Contact in your CRM system)

     

    I sometimes have luck with that approach.

     

    The other way I've done it is to do an Advanced Find with Active Accounts, then export to Excel.  Do anther Advanced Find on Active Accounts with a filter of "Contact(Parent Customer) Last Name Contains Data".  Export that to Excel as well.  Copy the Account numbers or names (whichever is unique in your CRM system) into the same column, do an advanced filter, click "Unique Only" and you will get a list of Accounts that don't have Contacts on them.

     

     

     

    Friday, May 6, 2011 7:15 PM
  • There is no supproted way to display the accounts that have no contacts using advanced find because you cannot search child records in advanced find (only parent records), so you could find how many contacts don't have a parent account, but that is not the same as how many accounts don't have a contact. You can also search with advcanced find how many accounts dont have a PRIMARY contact, because that is a simple field on account.

     

    If you want to find how many accounts don't have contacts you need to use a SQL query as suggested. If you want this data to be available through CRM and exportable to excep through the CRM application you'd need to create a custom report using your SQL query.

    Friday, May 6, 2011 8:01 PM
    Moderator
  • You can also use excel to query the database in the event that you don't have SQL installed.  Read through this article to learn more about using sql queries with excel.  Once you get used to it, and it won't take long, you'll be able to use it easily.  You can replace the query referenced in the article with the one Cornel provided

    http://edwardsdna.wordpress.com/2010/09/03/so-you-want-to-create-a-ssrs-custom-report/

    select accountid,name,* from account where accountid not in 
    (select distinct parentcustomerid from contact where parentcustomerid is not null and parentcustomeridtype = 1)

    Regards, Donna

    Monday, May 16, 2011 12:00 AM
  • Is this something Microsoft is going to address?  This seems to be a very basic report that many users would need.  I am in the same boat.  I need this report yet I am not familiar with SQL and would prefer to use CRM to give me this view.
    Wednesday, May 23, 2012 7:59 PM
  • If you want Microsoft to consider this request you can search the Connect website to see if someone else has submitted the request and then vote for it.  If aren't able to find an existing request then you can add the suggestion and get people to vote for it. 

    Regards, Donna

    Wednesday, May 23, 2012 9:49 PM