mjhjmtu

We're using CiviCRM to primarily build a network of activist groups around the country. There are "area organisers" and people they are linked with. We are using the relationship function to link people together.
It would be really helpful to be able to search for people who haven't yet been linked to an area organiser (because there isn't one yet in their locality), but I can only work out how to search for relationships between specific individuals. Is there a way to search for everyone who doesn't yet have a relationship with anyone else in the database?

One way is to export two lists to Excel and then use the power of Excel to find the missing ones.

1. Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.


2. From the Actions dropdown export that to CSV.


3. Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".


4. From the Actions dropdown export that to CSV.


5. Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
   https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
   

There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.

Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.

While this isn't an ideal fix, it's a quick one if you know SQL:

• Create a new group called "Contacts without relationship".


• Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".


• Run this SQL statement:
  
SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL) ORDER BY cc.id;

  

Replace the "3" above with YOUR group ID before you run it.

Now, everyone without a relationship will be in your group.

Screenshot for group ID (click to zoom):