Organisation relationship search

Question

My organisation is primarily (but not solely) a network of other organisations, so on Civi we have lots of "organisation" contact types, and lots of individual contacts. Some of these individuals are representative or contact person of their organisation to our organisation. I use the relationship tool to connect individuals to organisations as respresentatives (I made a new relationship type), and that works really well.

Some organisations haven't nominated someone to be a rep, or we haven't got them into CiviCRM properly yet (we're still slowly setting up). What I'm struggling to work out is how to run a search of organisations which don't yet have that relationship type with anyone. Is this possible anyhow?

Thanks.

Answer 1

You can use this SQL if you have access to the database directly. Replace the "5" with the actual id of the relationship type you're interested in. If you don't know the id you can see it if you go to admin - customize - relationship types and hover over the view link then you'll see the id in the url in the browser status bar.

select c.display_name from civicrm_contact c left join civicrm_relationship r on (r.contact_id_a=c.id AND r.relationship_type_id=5) where c.contact_type='Organization' and r.id IS NULL;

Depending on which direction you've defined the relationship you may need to replace contact_id_a with contact_id_b above.

I think there's a bug in the CiviReport - Contact Reports - Relationship Report otherwise you might be able to do it from there, setting contact type A to be Organization and relationship to be "Is Empty", but it doesn't seem to recognize the latter properly.