locked
Advanced Find: Accounts with no Contacts RRS feed

  • Question

  • Hello folks,

     

    Can anyone tell me if it is possible to produce an advanced that retrieves accounts that have no contacts?

    As near as I can tell, this cannot be done in CRM 3.0.

    Can anyone tell me if it will be possible in CRM 4.0?

     

    Thank you,

    TimDarius

    Friday, November 16, 2007 8:29 PM

Answers

  • It's not possible in CRM 3.0, and I can't see it being possible in CRM 4.0.

     

    Saturday, November 17, 2007 11:36 AM
    Moderator

All replies

  • It's not possible in CRM 3.0, and I can't see it being possible in CRM 4.0.

     

    Saturday, November 17, 2007 11:36 AM
    Moderator
  • There are ways to acheive the same aim without using Advanced Find, rather by using CRM's integration with Excel. This may allow you to meet your requirements...

    • Start by creating an Advanced Find for accounts without any filter criteria then use the "Edit Columns" button to include any additional fields.
    • Then click on the "Find" button.
    • From the results page, click on the "Export to Excel" button and select "Dynamic Worksheet".
    • Once the data loads in Excel, right-click anywhere in the results and select "Edit Query..."
    • When Microsoft Query loads, click on the "SQL" button. This should show the existing SQL of your Advanced Find. It should look something like this (except the hash signs which I have added).
    • Code Block
      select account.name ,account.primarycontactidname ,account.telephone1 ,account.accountid  from FilteredAccount as account ########### order by account.name asc

       

       

    • Insert the following code in place of the red hashes in the code above then click "OK":
    • Code Block
    •  
       /* returns all active accounts that do not have any associated contacts */
      WHERE account.statuscode = 1
      AND 0 =  (
        SELECT COUNT(*)
        FROM dbo.FilteredContact AS contact
        WHERE contact.parentcustomerid = account.accountid
        AND contact.statuscode = 1
        )
    • You will get a warning: "SQL Query can't be represented graphically. Continue anyway?" Click on "OK"
    • The data grid in SQL Query will now show your results.
    • Click on File > Return Data to Excel
    • You can now save your Excel spreadsheet and potentially even load it up as a Report.

    If you wanted to get a bit more clever, you could also add a field to the Excel spreadsheet which uses Excel's HYPERLINK function to link directly to the right account (code below assumes account name in column "A" and account GUID in column "E", eg:

    Code Block

    =HYPERLINK("http://crmserver:5555/sfa/accts/edit.aspx?id={" & E2 & "}","Click here to view " & A2)

     

     

    Wednesday, November 21, 2007 11:39 AM
  • Thanks for your contribution Greg.

     

    Together with customers I serve we have thought of the option to extend the Advanced Find with a SQL-button which would show the actual SQL it would fire onto the database. With the possibility to change that SQL and save it in the view parameters, this would create a very powerfull option.

     

    What about this and could this be added to Titan somewhere next month?

     

    Regards,

     

    Frank

     

    Thursday, December 6, 2007 2:06 PM
  • Amazing discovery dude, too good
    Thursday, June 11, 2015 3:13 PM