3

I'm migrating to a new Drupal 9 install, and after a decade of using CiviCRM, I want to pare down the number of records. If I do a simple find contacts search with no criteria, I get 74144 results. I've also searched for everyone who has given us contributions as well as everyone who opted in to our email list and put them in a separate group. This group, "2021_keep" has 6046 contacts. Next I go to Search Builder and put in the query Group(s) Not In 2021_keep ...AND... Group Status 'Added' Actually, just the first part is my query, the second part gets added after I click to search. Result is 71858 contacts. I would expect the result to be 74144 - 6046 = 68098 contacts. This is kind of important, as I want to delete all of these contacts, who are people that at some point signed a petition we hosted but didn't sign up for the email list, or later opted out of it, etc.

4

When you search for Group(s) Not In 2021_keep, Search Builder may be interpreting it as "contacts who are in some group that's not 2021_keep". That's not what you want.

Here are two three options for finding all the contacts who are not in the group "2021_keep".

Include/Exclude Search

Go to Search > Custom Searches and click on Include/Exclude Search. Set "2021_keep" as the value for "Exclude Group(s)". Click on either one of the radio buttons next to "AND/OR" (it doesn't matter which you choose, since you're not using tags in your search.)

You should be able to delete contacts directly from the search results, or perform various other actions on them.

API v4

If for some reason the Include/Exclude search doesn't satisfy, and you are on a recent version of Civi, you can do your search using API4. You should have access to the API explorer at the url civicrm/api4.

Configure your search as follows:

API explorer configuration

Entity: Contact
Action: get
Select: id
Join: GroupContact
On: group_contact.contact_id = id
On: group_contact.group_id =
On: group_contact.status = "Added" (note the quotes)
Where: group_contact.id IS NULL

You can pre-fill these values by deleting everything in your URL bar to the right of the word "explorer", and replacing it with:
/Contact/get?join=[["GroupContact AS group_contact",false,null,["group_contact.contact_id","%3D","id"],["group_contact.group_id","%3D","1"],["group_contact.status","%3D","\"Added\""]]]&where=[["group_contact.id","IS NULL"]]&select=["id"]&debug=0&checkPermissions=0

Remember to change the Group ID to the id of your "2021_keep" group. When you're finished you can use the "Save" button at the top to save the results as a Smart Group.

Group subtraction (remove from group)

  1. Use the basic "Find Contacts" search with empty search criteria to find all your contacts.
  2. Above the result table, click "All 74144 records", then select the "Group - add contacts" action. On the following screen, create a new group called "NOT in 2021_keep" and click "Add to Group".
  3. Do another "Find Contacts" search. This time, set the groups field to "2021_keep".
  4. Above the result table, click "All 6046 records", then select the "Group - remove contacts" action. On the following screen, select the "NOT in 2021_keep" group you just created. Click "Remove From Group".

Now your "NOT in 2021_keep" group should have 74144 - 6046 = 68098 contacts in it.

3
  • Unfortunately, I don't have "include / exclude" custom search, nor does the API explorer work properly. And yet my site is using 5.27.0, released July 1st 2020, so I don't think that is excessively old? When I select the dropdown by "Required", there is just the options "Optional" or "Required". And the box with the title "On" doesn't appear. I also have some problems with double menu entries, and basically it seems like at some point something with my install screwed up, so why I'm trying to clean up the data and move it over to a new install... – Andrew Ray Jan 29 at 21:45
  • @AndrewRay when you go to Search > Custom Searches, is Include/Exclude Search not listed? What if you go directly to its URL, which should be /civicrm/contact/search/custom?csid=4 ? As for API4, it has been changing fairly fast, so I wouldn't be totally suprised if the necessary features were missing back in July. But it does sound like there's something screwy about your instance in general. – Noah Miller Jan 29 at 21:58
  • Great! The URL worked! Thanks! That search method gives me the expected number of results. Next is studying exactly which tables in the database have contact/group/contribution/activity info, and just copy those, so that hopefully the new instance of Civicrm won't have these weird problems. Presumably, at some point, I screwed up some upgrade. – Andrew Ray Jan 29 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.