Find organizations that don't have employees with an active membership

Question

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?

Answer 1

Does this get you what you want?

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" ] } ] ]

Answer 2

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.