I want to do some spring cleanup and see who in my individual contacts aren't in any group, is there an easy way to do it?
The best solution would be to use search builder like so:
However as Laryn noted there was a bug preventing this from working. I've filed an issue for this, and worked up a fix that hopefully will make it into the next release.
This is a workaround, but it will get what you need with minimal effort. Use the Import Contacts feature. On the first screen, choose Update. Instead of a CSV file as the source, use the SQL query option like so:
SELECT c.ID AS InternalContactID FROM c9.civicrm_contact c WHERE c.contact_type = 'Individual' AND NOT EXISTS ( SELECT 1 FROM c9.civicrm_group_contact c2 WHERE c2.contact_id = c.id) AND NOT EXISTS ( -- don't forget to check smart groups SELECT 1 FROM c9.civicrm_group_contact_cache c3 WHERE c3.contact_id = c.id);
Map the field if it isn't already, and continue to the next screen. Here, you can add the contacts to a group or assign a tag. Click Import Now and you have a "No Group" group that your users can work with.
This technique with Import gives us a full-featured Search Builder for the SQL-savvy.
Nice, I think I'm going to wrap your query into a custom search. and double nice for adding the smart group cache. May 9, 2015 at 17:54
2I've found it a name for the extension, the hardest is done github.com/TechToThePeople/lonesome May 9, 2015 at 18:31
1@Xavier in addition to leaving nice comments, please upvote good posts and accept correct answers to your questions. This is super-important for building the site. Vote early, vote often!– ColemanMay 9, 2015 at 20:22
The easiest is probably to install the extension I just created, thanks to Nicholai magic sql suggestion:
Otherwise, the clunkier way I found is to use the custom search exclude/include and select every group in the exclude fields.
It works, with two issues still - it does include organisations (that are often without a group) - it's a bit of a pita to select all groups when you have more than a handful
Can you create a smart group on the Contact Type = "Individual" and use that in the "Include" section?– LarynMay 8, 2015 at 14:51
good idea, but getting clunkier by the minute ;) May 8, 2015 at 15:00
True. :) I was going to suggest a nice clean Search Builder with "Contact - Group(s) - IS EMPTY (or IS NULL)" but that throws a WSOD...– LarynMay 8, 2015 at 16:21