Answers.Body: <p>When you search for <code>Group(s) Not In 2021_keep</code>, Search Builder may be interpreting it as &quot;contacts who are in some group that's not 2021_keep&quot;. That's not what you want.</p>
  <p>Here are <del>two</del> three options for finding all the contacts who are not in the group &quot;2021_keep&quot;.</p>
  <h3>Include/Exclude Search</h3>
  <p>Go to Search &gt; Custom Searches and click on Include/Exclude Search. Set &quot;2021_keep&quot; as the value for &quot;Exclude Group(s)&quot;. Click on either one of the radio buttons next to &quot;AND/OR&quot; (it doesn't matter which you choose, since you're not using tags in your search.)</p>
  <p>You should be able to delete contacts directly from the search results, or perform various other actions on them.</p>
  <h3>API v4</h3>
  <p>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 <code>civicrm/api4</code>.</p>
  <p>Configure your search as follows:</p>
  <p><a href="https://i.sstatic.net/vBjgH.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/vBjgH.png" alt="API explorer configuration" /></a></p>
  <p>Entity: Contact<br />
  Action: get<br />
  Select: id<br />
  Join: GroupContact<br />
  On: group_contact.contact_id = id<br />
  On: group_contact.group_id = <br />
  On: group_contact.status = &quot;Added&quot; (note the quotes)<br />
  Where: group_contact.id IS NULL</p>
  <p>You can pre-fill these values by deleting everything in your URL bar to the right of the word &quot;explorer&quot;, and replacing it with:<br />
  <code>/Contact/get?join=[[&quot;GroupContact AS group_contact&quot;,false,null,[&quot;group_contact.contact_id&quot;,&quot;%3D&quot;,&quot;id&quot;],[&quot;group_contact.group_id&quot;,&quot;%3D&quot;,&quot;1&quot;],[&quot;group_contact.status&quot;,&quot;%3D&quot;,&quot;\&quot;Added\&quot;&quot;]]]&amp;where=[[&quot;group_contact.id&quot;,&quot;IS NULL&quot;]]&amp;select=[&quot;id&quot;]&amp;debug=0&amp;checkPermissions=0</code></p>
  <p>Remember to change the Group ID to the id of your &quot;2021_keep&quot; group. When you're finished you can use the &quot;Save&quot; button at the top to save the results as a Smart Group.</p>
  <h3>Group subtraction (remove from group)</h3>
  <ol>
  <li>Use the basic &quot;Find Contacts&quot; search with empty search criteria to find all your contacts.</li>
  <li>Above the result table, click &quot;All 74144 records&quot;, then select the &quot;Group - add contacts&quot; action. On the following screen, create a new group called &quot;NOT in 2021_keep&quot; and click &quot;Add to Group&quot;.</li>
  <li>Do another &quot;Find Contacts&quot; search. This time, set the groups field to &quot;2021_keep&quot;.</li>
  <li>Above the result table, click &quot;All 6046 records&quot;, then select the &quot;Group - remove contacts&quot; action. On the following screen, select the &quot;NOT in 2021_keep&quot; group you just created. Click &quot;Remove From Group&quot;.</li>
  </ol>
  <p>Now your &quot;NOT in 2021_keep&quot; group should have 74144 - 6046 = 68098 contacts in it.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-29T21:45:29.770Z
Answers.Text: 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...
Answers.UserDisplayName: Andrew Ray
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-29T21:58:14.317Z
Answers.Text: @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.
Answers.UserDisplayName: Noah Miller
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-29T22:09:48.903Z
Answers.Text: 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.
Answers.UserDisplayName: Andrew Ray
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-29T19:58:30.410Z
Answers.LastActivityDate: 2021-01-29T22:26:59.633Z
Answers.LastEditDate: 2021-01-29T22:26:59.633Z
Answers.UserDisplayName: Noah Miller
Body: <p>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, &quot;2021_keep&quot; has 6046 contacts.
  Next I go to Search Builder and put in the query
  <code>Group(s) Not In 2021_keep ...AND... Group Status 'Added'</code>
  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.</p>
  
Tags: <contacts><search-builder><advanced-search>
Title: Descrepancies in search result counts
ThreadText: # Descrepancies in search result counts
  
  (Asked by: username_0 on 2021-01-29)
  
  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.
  
  **Tags:** contacts, search-builder, advanced-search
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-01-29. Score: 4 | Accepted answer
  
  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](https://i.sstatic.net/vBjgH.png)](https://i.sstatic.net/vBjgH.png)
  
  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.
  
  > **Comments:**
  > **username_0**: 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...  
  > **username_1**: @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.  
  > **username_0**: 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.  
  
  ---
  
  