locked
Custom View: Accounts with no Contacts RRS feed

  • Question

  • Hi,

    I'm trying to create a custom view with the SDK to show all accounts with no contacts associated to it. I'm having some problems creating the QueryExpression to retrieve those accounts.
    Could someone help me with that ?

    Thanks for your help
    Sunday, April 12, 2009 10:36 PM

Answers

  • Hi,

    This is not straight forward, since there is no support for "not in sub-queries" in MS CRM.

    One way to manage this, without doing any programming, is to use the "Use advanced find to Remove Members" in marketing list.

    1. Create a Marketing list, and populate it with all active accounts
    2. Select Manage Members; and select "Use advanced find to Remove Members"
    - In advanced find select active account, linked to contacts, from the search list, select "Remove from Marketing List".
    3. And now you have a list of all accounts without contacts.

    (or do similar approach with SDK Retrieve Multiple etc...)


    A thought for maintaining this, LIVE;
    If you create a work flow on contact that increment/decrement a custom field (new_contactcounter) in the account form, you will have this field updated automatically, and you can use this in your query for the future.

    Morten
    • Marked as answer by Jim Glass Jr Monday, April 13, 2009 4:16 PM
    Monday, April 13, 2009 7:12 AM

All replies

  • Hi,
    Do you want a query expression code to find out accounts having primary contact id as null ?

    QueryExpression query = new QueryExpression();

    query.EntityName = "account";

    ColumnSet columns = new ColumnSet();
    columns.Attributes = new string[] { "accountid", "name" };
    query.ColumnSet = columns;


    query.Criteria = new FilterExpression();
    query.Criteria.FilterOperator = LogicalOperator.And;

    ConditionExpression condition1 = new ConditionExpression();
    condition1.AttributeName = "primarycontactid";
    condition1.Operator = ConditionOperator.Null;

    query.Criteria.Conditions = new ConditionExpression[] { condition1 };

    Regards,
    Nishant Rana


    http://nishantrana.wordpress.com
    Monday, April 13, 2009 7:03 AM
  • Hi,

    This is not straight forward, since there is no support for "not in sub-queries" in MS CRM.

    One way to manage this, without doing any programming, is to use the "Use advanced find to Remove Members" in marketing list.

    1. Create a Marketing list, and populate it with all active accounts
    2. Select Manage Members; and select "Use advanced find to Remove Members"
    - In advanced find select active account, linked to contacts, from the search list, select "Remove from Marketing List".
    3. And now you have a list of all accounts without contacts.

    (or do similar approach with SDK Retrieve Multiple etc...)


    A thought for maintaining this, LIVE;
    If you create a work flow on contact that increment/decrement a custom field (new_contactcounter) in the account form, you will have this field updated automatically, and you can use this in your query for the future.

    Morten
    • Marked as answer by Jim Glass Jr Monday, April 13, 2009 4:16 PM
    Monday, April 13, 2009 7:12 AM