Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Display a count of contact's marketing lists

Answered Display a count of contact's marketing lists

  • Monday, 7 July, 2008 7:37 AM
     
     
    Sign In to Vote
    0

     

    Hello,

     

    Apologies in advance if this is already answered, however I haven't been able to locate the answer.

     

    In Microsoft CRM 3.0, is it possible to display:

    (a) the number of active marketing lists to which a contact belongs?, and

    (b) the number '0', if a contact does not belong to any active marketing lists?

     

    The context in which I'd like to use it is this: I've created an Advanced Find which shows all contacts created in the last 7 days. It would be helpful to include a column which displays how many (if any) marketing lists they belong to.

     

    An existing post (Advanced find - Searching for Contacts not on a Marketing List) suggests that calculating a count may be possible, but I don't know how to create the field.

     

    Thank you in advance for any assistance

     

    - Sarah

     

     

     

All Replies

  • Monday, 7 July, 2008 7:52 PM
    Owner
     
     Answered
    Sign In to Vote
    0

     

    Sarah,

     

    Customize the Advanced Find form is not supported. You may develop an extra aspx page for it.

     

    Jim

     
  • Tuesday, 8 July, 2008 12:20 AM
     
     
    Sign In to Vote
    0
    Thank you, Jim.

    Could this be achieved in a custom report instead?
     
  • Tuesday, 8 July, 2008 1:13 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

     

    Yes, you could achieve this in a custom report.  Here's a query you could use in a report:

     

    Select FilteredContact.fullname, Count(FilteredListMember.listid) as "#_lists"
    From FilteredContact Inner Join
    FilteredListMember on FilteredContact.contactid = FilteredListMember.entityid
    Group By FilteredContact.fullname

     
  • Tuesday, 8 July, 2008 3:05 AM
     
     
    Sign In to Vote
    0
    Excellent, thank you!
     
  • Friday, 22 August, 2008 11:38 AM
     
     
    Sign In to Vote
    0

    I have a related question, which I am not sure about:

     

    I would like to make an advanced find, that shows a list of accounts having more than 4 contacts associated with them. After I have showed the list, I would like to run a workflow on these Accounts.

     

    Is that possible via advanced find to make this "count" like criterias?

     

    If not, any clue how to solve my requirement?

     

    /H

     

     
  • Friday, 22 August, 2008 2:07 PM
    Moderator
     
     
    Sign In to Vote
    0

    advanced find doesn't do calculations and aggregate functions.  Here's a simple SQL query of the contact entity that will do it:

    Code Snippet

    select *

    from

    (select accountid, accountidname, count(contactid)as count

    from FilteredContact

    group by accountidname, accountid)as contactcount

    where count >=4

     

     

     

     
  • Tuesday, 26 August, 2008 7:49 AM
     
     
    Sign In to Vote
    0

    Thanks Joel,

    I see you point ... but the problem as I see it is that I want quickly to select the contacts where the above conditions is true, but after this I want to run a workflow on these records.

     

    I thought the easiest way to do this was to save an advanced view, choose it eg. monthly and then run the workflow.

     

    So is it possible to upload the above SQL query into a saved advanced find?

     

    If no, have you any suggestions on how to build the feature (I must admit I am not a developer, but only an CRM consultant).

     

    Thanks in advance.

     

    /Henrik

     
  • Tuesday, 26 August, 2008 1:54 PM
    Moderator
     
     
    Sign In to Vote
    0

    I also am not a programmer either, but I know that this could be done with a plug-in or callout.  You would need to add a field called Contact count to the account, and then have a plugin or callout that calculates the number of contacts associated with the account.

     

    You also could do this with workflow--you can have a workflow on contact that says when a contact is created, increment a contact count field on the associated account, and when a contact is deleted or deactivated, decrement the contact count on the related account record.

    This would work as new contacts are created, but you would need to do something to get the count of existing contacts at accounts.  If you manually calculated this for existing accounts, the workflow could maintain/update the counts and maintain data integrity.