Answers.Body: <p>I finally figured it out:</p>
  <ol>
  <li>Do &quot;With (Optional)&quot; to join in individuals that are active employees of the organization.</li>
  <li>Do another &quot;With (Optional)&quot; to join in active memberships of those individuals.</li>
  <li>Group by the display name of the organization.</li>
  </ol>
  <p>Organizations with an empty membership type for employees can then easily be identified.</p>
  <p><a href="https://i.sstatic.net/LoWa7.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/LoWa7.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-12-10T14:28:58.493Z
Answers.LastActivityDate: 2022-12-10T14:28:58.493Z
Answers.UserDisplayName: Colin Smith
Answers.Body: <p>Does this get you what you want?</p>
  <p><a href="https://i.sstatic.net/aUNSc.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/aUNSc.png" alt="enter image description here" /></a></p>
  <p>Not sure if i can paste here from the export</p>
  <blockquote>
  <p><code>[ [ &quot;SavedSearch&quot;, &quot;save&quot;, { &quot;records&quot;: [ { &quot;name&quot;: &quot;test&quot;, &quot;label&quot;: &quot;test&quot;, &quot;form_values&quot;: null, &quot;mapping_id&quot;: null, &quot;search_custom_id&quot;: null, &quot;api_entity&quot;: &quot;Contact&quot;, &quot;api_params&quot;: { &quot;version&quot;: 4, &quot;select&quot;: [ &quot;id&quot;, &quot;display_name&quot; ], &quot;orderBy&quot;: [], &quot;where&quot;: [ [ &quot;contact_type:name&quot;, &quot;=&quot;, &quot;Organization&quot; ] ], &quot;groupBy&quot;: [], &quot;join&quot;: [ [ &quot;Membership AS Contact_Membership_contact_id_01&quot;, &quot;INNER&quot;, [ &quot;id&quot;, &quot;=&quot;, &quot;Contact_Membership_contact_id_01.contact_id&quot; ], [ &quot;Contact_Membership_contact_id_01.membership_type_id:name&quot;, &quot;=&quot;, &quot;\&quot;Related\&quot;&quot; ] ], [ &quot;Contact AS Contact_RelationshipCache_Contact_01&quot;, &quot;EXCLUDE&quot;, &quot;RelationshipCache&quot;, [ &quot;id&quot;, &quot;=&quot;, &quot;Contact_RelationshipCache_Contact_01.far_contact_id&quot; ], [ &quot;Contact_RelationshipCache_Contact_01.far_relation:name&quot;, &quot;=&quot;, &quot;\&quot;Employer of\&quot;&quot; ], [ &quot;Contact_Membership_contact_id_01.membership_type_id:name&quot;, &quot;=&quot;, &quot;\&quot;Related\&quot;&quot; ] ] ], &quot;having&quot;: [] }, &quot;expires_date&quot;: null, &quot;description&quot;: null } ], &quot;match&quot;: [ &quot;name&quot; ] } ] ]</code></p>
  </blockquote>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-12-02T19:09:32.773Z
Answers.Text: Currently on dmaster at https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/2 but will be flushed later today
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-12-06T03:02:49.380Z
Answers.Text: Unfortunately that doesn't seem to work. It returns zero contacts on my end. To my understanding, the "With (required)" block is looking for memberships of the organization, not the employee.
Answers.UserDisplayName: Colin Smith
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-12-02T19:07:28.853Z
Answers.LastActivityDate: 2022-12-02T19:07:28.853Z
Answers.UserDisplayName: petednz - fuzion
Body: <p>To join, the employers of our members must sign an agreement. I would like to find organizations that have previously signed the agreement (indicated with a custom field), but no longer have any employees who are active members, so that the agreement can be terminated.</p>
  <p>Using SearchKit, I am able to find organizations that have no employees in our database (see image). However, the query misses out on organizations that still have employees, but none of those employees have an active membership. I think this could be done if I could specify in the &quot;With (optional) Contact Related Contacts&quot; join that the related contact must also be the member of a group, but I don't think that's possible. Is there some other way to do this?</p>
  <p><a href="https://i.sstatic.net/7cXv6.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/7cXv6.png" alt="SearchKit screen shot" /></a></p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-12-02T18:52:13.623Z
Comments.Text: Try adding a second 'with entity (required)' so you can bring in your membership data (but note i just tried on dmaster and got an unexpected outcome)
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-12-02T18:54:08.690Z
Comments.Text: actually not sure if that helps. have posted in SK thread at chat.civicrm.org
Comments.UserDisplayName: petednz - fuzion
Tags: <searchkit>
Title: Find organizations that don't have employees with an active membership
ThreadText: # Find organizations that don't have employees with an active membership
  
  (Asked by: username_0 on 2022-12-02)
  
  To join, the employers of our members must sign an agreement. I would like to find organizations that have previously signed the agreement (indicated with a custom field), but no longer have any employees who are active members, so that the agreement can be terminated.
  
  Using SearchKit, I am able to find organizations that have no employees in our database (see image). However, the query misses out on organizations that still have employees, but none of those employees have an active membership. I think this could be done if I could specify in the "With (optional) Contact Related Contacts" join that the related contact must also be the member of a group, but I don't think that's possible. Is there some other way to do this?
  
  [![SearchKit screen shot](https://i.sstatic.net/7cXv6.png)](https://i.sstatic.net/7cXv6.png)
  
  **Tags:** searchkit
  
  > **Comments:**
  > **username_1**: Try adding a second 'with entity (required)' so you can bring in your membership data (but note i just tried on dmaster and got an unexpected outcome)  
  > **username_1**: actually not sure if that helps. have posted in SK thread at chat.civicrm.org  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2022-12-10. Score: 1 | Accepted answer
  
  I finally figured it out:
  
  1. Do "With (Optional)" to join in individuals that are active employees of the organization.
  2. Do another "With (Optional)" to join in active memberships of those individuals.
  3. Group by the display name of the organization.
  
  Organizations with an empty membership type for employees can then easily be identified.
  
  [![enter image description here](https://i.sstatic.net/LoWa7.png)](https://i.sstatic.net/LoWa7.png)
  
  
  ---
  
  ### Answer by username_1 on 2022-12-02. Score: 1
  
  Does this get you what you want?
  
  [![enter image description here](https://i.sstatic.net/aUNSc.png)](https://i.sstatic.net/aUNSc.png)
  
  Not sure if i can paste here from the export
  
  > `[ [ "SavedSearch", "save", { "records": [ { "name": "test", "label": "test", "form_values": null, "mapping_id": null, "search_custom_id": null, "api_entity": "Contact", "api_params": { "version": 4, "select": [ "id", "display_name" ], "orderBy": [], "where": [ [ "contact_type:name", "=", "Organization" ] ], "groupBy": [], "join": [ [ "Membership AS Contact_Membership_contact_id_01", "INNER", [ "id", "=", "Contact_Membership_contact_id_01.contact_id" ], [ "Contact_Membership_contact_id_01.membership_type_id:name", "=", "\"Related\"" ] ], [ "Contact AS Contact_RelationshipCache_Contact_01", "EXCLUDE", "RelationshipCache", [ "id", "=", "Contact_RelationshipCache_Contact_01.far_contact_id" ], [ "Contact_RelationshipCache_Contact_01.far_relation:name", "=", "\"Employer of\"" ], [ "Contact_Membership_contact_id_01.membership_type_id:name", "=", "\"Related\"" ] ] ], "having": [] }, "expires_date": null, "description": null } ], "match": [ "name" ] } ] ]`
  
  > **Comments:**
  > **username_1**: Currently on dmaster at https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/2 but will be flushed later today  
  > **username_0**: Unfortunately that doesn't seem to work. It returns zero contacts on my end. To my understanding, the "With (required)" block is looking for memberships of the organization, not the employee.  
  
  ---
  
  