CiviCRM Community Forums (archive)

We're working with a nonprofit client that has Organization memberships. They also capture Individuals and relate them as Employees of the Organization.

What the client would like to do is an advanced search that would list all of the Organization Memberships and then all the Individuals who are related to those Organizations with "Employee of". We've tried a variety of searches as well as Search Builder and can't seem to get it to work.

Has anyone needed anything like this? It seems like we would need to find the Organization Members first and then perform a second search that looks at the first one to get the Individuals. We'll probably have to do this as a MySQL query, but any thoughts or hacks within CiviCRM would be fabulous.

Thanks!

Hi Donald,
Thanks so much for the response. We hadn't come across the mysql queries on the Wiki (http://wiki.civicrm.org/confluence/display/CRMDOC/Useful+SQL+queries for others' reference).

We'll work on this and will post the solution soon.

Thanks!

Yes, yes!

Actually, I have some mysql queries that will produce some of these.

Unfortunately, right now the simplest thing  to do if you have end users trying to pull the information is to make a custom field that reproduces the information.

Here's for individuals and households

DROP TABLE IF EXISTS relationshipa;
DROP TABLE IF EXISTS relationshipb;
DROP TABLE IF EXISTS all_relationships;
CREATE  TABLE relationshipa as
 SELECT r.contact_id_a AS contact_a,r.id AS relationship_id,r.relationship_type_id AS rel_type,c.id AS contact_id,c.sort_name AS namea
 FROM civicrm_relationship AS r, civicrm_contact AS c
 WHERE  r.contact_id_a=c.id  AND r.relationship_type_id=7 ;

 
CREATE  TABLE relationshipb as
 SELECT s.contact_id_b AS contact_b,s.id AS relationship_id,s.relationship_type_id AS rel_type,d.id AS contact_id,d.sort_name AS nameb
 FROM civicrm_relationship AS s, civicrm_contact AS d
 WHERE  s.contact_id_b=d.id AND s.relationship_type_id=7 ;


CREATE  TABLE all_relationships as
SELECT h.relationship_id AS r_id_a,h.rel_type AS rel_type_a,j.relationship_id AS r_id_b,h.namea AS name_a,j.nameb AS name_b
FROM relationshipa AS h,relationshipb AS  j
WHERE  h.relationship_id=j.relationship_id;


SELECT k.r_id_a AS r_id_a,k.r_id_b AS r_id_b,k.name_a as name_a,k.name_b AS name_b,k.rel_type_a AS reltype,
                             l.id AS reltype_id,l.name_a_b AS rolenamea,l.id AS reltype_id,l.name_b_a AS rolenameb
FROM  all_relationships AS k,civicrm_relationship_type AS l
WHERE k.rel_type_a=l.id
order by k.name_b


obviously change lines 7 and 13 for the relationship you want.

Brian - This feature should work as you've described - assuming that you've set up the Membership Type with the approporiate relationship - and that relationships is established between the member organization and the individuals. You can verify your setup by creating a relationship (e.g. Employee of) of that type between a member org and an individual. Then checking the Memberships tab under that individual. It should show a "non-editable" membership.

If you've got things setup correctly and not getting the expected behavior, please replicate on the demo and post your results here.

thx!
dave

Ok, ok, ok. Perhaps I checked my brain at the door.

I just played around some more with the demo, and created a second membership type with identical settings, but used the reverse relationship. Initially I used "employee of." In the second membership type I used "employer of" -- just to see if it was my logic that was messed up. And it was.

However, in my defense, the description for the relationship type field is confusing. It reads:

Select relationship type for this membership type. EXAMPLE: Select 'Household Member is' for memberships where the direct member is a Household and you want all Household Members to be automatically granted memberships.
I equated "household member is" with "employee of" because both were describing the individual, and I wanted the same scenario described in the description-- all employees/members of an organization/household are considered members.

So sorry for the false alarm.

For the sake of the early posters in this thread --
I believe that setting this up correctly will address the original posters (and my) dilemma. When I go to advanced search now and do a search for individuals, with a membership type of the category desired, I receive all the employees of the organization which is a member of that type.

One other comment --
The relationship type field can't be edited through the CiviCRM interface. But it can be changed by getting into the MySQL db and looking at the civicrm_membership_type table-- relationship_type and relationship_direction fields. If you did what I did, you can switch the data in the relationship_direction field from a_b to b_a (or vice versa). I've tested it on mine and that fixes things.

Sort of. It fixes things moving forward, but not for existing data. Anyone with insight on how I take my 1000+ pre-existing membership records and populate the relationships down to the individuals of org, please don't hesitate to chime in!

thanks,
Brian

Sorry for all the posts, but I answered my own question.
Yes, the cron job does repopulate all the related records, and thus self-corrects the relationship mistake. Excellent!
-Brian