CiviCRM Community Forums (archive)

Ultimately, I want a smart group here, but I cannot figure out how to approach this.

I have contacts who are board members and therefore members of a group "board members"

they have assistants who are not members of that group, but have a relationship of "assistant to" the board members.

How do I do a search that returns the board members and their assistants? Or even just the assistants--I could AND that group with the "Board Members" group to get the complete mailing list.

If I search for people with the relationship "assistant to" then it returns everyone with such a relationship; not limited to current board members, so that is no good.

I'm not following. I already have a group for the board members, so I do not need a smart group there. I have tried creating a smart group of all assistants, but how do I make them limited to the board members?

In SQL, this is relatively easy (relationship_type_id 13 is "has assistant")

/* identify board members and their assistants */

select cc.display_name, cc.organization_name, crt.name_a_b, cc2.display_name
from civicrm_contact cc
join civicrm_relationship cr on cr.contact_id_a=cc.id
join civicrm_contact cc2 on cr.contact_id_b = cc2.id
join civicrm_relationship_type crt on cr.relationship_type_id = crt.id
join civicrm_group_contact cgc on cgc.contact_id=cc.id
join civicrm_group cg on cgc.group_id=cg.id
where cr.relationship_type_id = 13
and cg.name = 'Board of Directors (P)';
But then I need to pull together all the email addresses and mailing addresses etc. which would be easier to obtain if this set were defined as a group in Civi vs me hand-coding a report for what should be a very common sort of need of users, I would think.