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. – Xavier May 9 '15 at 17:54
-
2I've found it a name for the extension, the hardest is done github.com/TechToThePeople/lonesome – Xavier May 9 '15 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! – Coleman May 9 '15 at 20:22
The easiest is probably to install the extension I just created, thanks to Nicholai magic sql suggestion:
https://civicrm.org/extensions/lonesome-find-contacts-without-group
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? – Laryn - CEDC.org May 8 '15 at 14:51
-
-
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... – Laryn - CEDC.org May 8 '15 at 16:21