2

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

2
  • 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)
    – petednz - fuzion
    Commented Dec 2, 2022 at 18:52
  • actually not sure if that helps. have posted in SK thread at chat.civicrm.org
    – petednz - fuzion
    Commented Dec 2, 2022 at 18:54

2 Answers 2

1

Does this get you what you want?

enter image description here

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

2
  • Currently on dmaster at dmaster.demo.civicrm.org/civicrm/admin/search#/edit/2 but will be flushed later today
    – petednz - fuzion
    Commented Dec 2, 2022 at 19:09
  • 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. Commented Dec 6, 2022 at 3:02
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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