Monday, 7 July, 2008 7:37 AM
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
Monday, 7 July, 2008 7:52 PMOwner
Customize the Advanced Find form is not supported. You may develop an extra aspx page for it.
Tuesday, 8 July, 2008 12:20 AMThank you, Jim.
Could this be achieved in a custom report instead?
Tuesday, 8 July, 2008 1:13 AMModerator
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 AMExcellent, thank you!
Friday, 22 August, 2008 11:38 AM
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?
Friday, 22 August, 2008 2:07 PMModerator
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 accountid, accountidname, count(contactid)as count
groupby accountidname, accountid)as contactcount
Tuesday, 26 August, 2008 7:49 AM
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.
Tuesday, 26 August, 2008 1:54 PMModerator
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.