2

Hoping for some Search Kit advice / guidance.

I have a search that brings in various contacts as outlined below:

  • Contact 1 (Individual) - the volunteer - has associated custom data and a relationship to
  • Contact 2 (Organisation) - has a relationship to
  • Contact 3 (Service Manager)

I can bring in the first two without issues but don't seem to bring in the 3 contact when I start with the individual as contact 1 (as no relationship to 3 I suspect).

However if I flip it and start with above contact 2 (the organisation) I am able to bring in all the contacts necessary and filter contact 3 to current user only - however, I need to display custom data associated with contact 1 which I can't do when I start the search with contact 2 as the main contact.

Is there a Search Kit guru that can help as I could just miss something obvious, added the SQL for my queries below.

Unable to bring in the service manager (to filter)

SELECT `a`.`id` AS `id`, `a`.`display_name` AS `display_name`, `Volunteer__1`.`type_of_volunteer_64` AS `Volunteer_.Type_of_volunteer:label`, `Volunteer__1`.`vol_start_date_128` AS `Volunteer_.Vol_Start_Date`, GROUP_CONCAT(DISTINCT `far_contact_id_2`.`display_name` SEPARATOR "") AS `GROUP_CONCAT_Contact_RelationshipCache_near_contact_id_01_far_contact_id_display_name`, GROUP_CONCAT(DISTINCT `near_contact_id_3`.`display_name` SEPARATOR "") AS `GROUP_CONCAT_Contact_RelationshipCache_far_contact_id_01_near_contact_id_display_name`
FROM civicrm_contact a
INNER JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_near_contact_id_01` ON `a`.`id` = `Contact_RelationshipCache_near_contact_id_01`.`near_contact_id` AND `Contact_RelationshipCache_near_contact_id_01`.`near_relation` = "Volunteer of"
INNER JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_far_contact_id_01` ON `a`.`id` = `Contact_RelationshipCache_far_contact_id_01`.`far_contact_id` AND `Contact_RelationshipCache_far_contact_id_01`.`near_relation` = "Service Manager for"
LEFT JOIN `civicrm_value_volunteer__15` `Volunteer__1` ON `a`.`id` =  `Volunteer__1`.`entity_id`
LEFT JOIN `civicrm_contact` `far_contact_id_2` ON `Contact_RelationshipCache_near_contact_id_01`.`far_contact_id` =  `far_contact_id_2`.`id`
LEFT JOIN `civicrm_contact` `near_contact_id_3` ON `Contact_RelationshipCache_far_contact_id_01`.`near_contact_id` =  `near_contact_id_3`.`id`
WHERE (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
ORDER BY `a`.`sort_name` ASC
LIMIT 50
OFFSET 0

Able to filter

SELECT `a`.`id` AS `id`, `a`.`display_name` AS `display_name`, `near_contact_id_1`.`display_name` AS `Contact_RelationshipCache_near_contact_id_01.near_contact_id.display_name`, `far_contact_id_2`.`display_name` AS `Contact_RelationshipCache_near_contact_id_01.far_contact_id.display_name`, `far_contact_id_3`.`display_name` AS `Contact_RelationshipCache_near_contact_id_02.far_contact_id.display_name`, `Contact_RelationshipCache_near_contact_id_01`.`near_contact_id` AS `Contact_RelationshipCache_near_contact_id_01.near_contact_id`, `Contact_RelationshipCache_near_contact_id_01`.`relationship_id` AS `Contact_RelationshipCache_near_contact_id_01.relationship_id`, `Contact_RelationshipCache_near_contact_id_01`.`orientation` AS `Contact_RelationshipCache_near_contact_id_01.orientation`, `Contact_RelationshipCache_near_contact_id_02`.`near_contact_id` AS `Contact_RelationshipCache_near_contact_id_02.near_contact_id`, `Contact_RelationshipCache_near_contact_id_02`.`relationship_id` AS `Contact_RelationshipCache_near_contact_id_02.relationship_id`, `Contact_RelationshipCache_near_contact_id_02`.`orientation` AS `Contact_RelationshipCache_near_contact_id_02.orientation`
FROM civicrm_contact a
INNER JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_near_contact_id_01` ON `a`.`id` = `Contact_RelationshipCache_near_contact_id_01`.`near_contact_id` AND `Contact_RelationshipCache_near_contact_id_01`.`near_relation` = "Service Manager is" AND `Contact_RelationshipCache_near_contact_id_01`.`far_contact_id` = "39260"
LEFT JOIN `civicrm_relationship_cache` `Contact_RelationshipCache_near_contact_id_02` ON `a`.`id` = `Contact_RelationshipCache_near_contact_id_02`.`near_contact_id` AND `Contact_RelationshipCache_near_contact_id_02`.`near_relation` = "Volunteer is"
LEFT JOIN `civicrm_contact` `near_contact_id_1` ON `Contact_RelationshipCache_near_contact_id_01`.`near_contact_id` =  `near_contact_id_1`.`id`
LEFT JOIN `civicrm_contact` `far_contact_id_2` ON `Contact_RelationshipCache_near_contact_id_01`.`far_contact_id` =  `far_contact_id_2`.`id`
LEFT JOIN `civicrm_contact` `far_contact_id_3` ON `Contact_RelationshipCache_near_contact_id_02`.`far_contact_id` =  `far_contact_id_3`.`id`
WHERE (`a`.`is_deleted` = "0")
ORDER BY `a`.`sort_name` ASC
LIMIT 50
OFFSET 0

As per Aidan's answer below - achievable with a later version of CiviCRM (I was on 5.49)

1 Answer 1

3

When you add Contact 3, select the entry 'Contact Related Contacts' that is indented under 'Contact Related Contacts' so that the relationship is to Contact 2 not Contact 1.

If you filter by 'related' you can see the options more clearly:

enter image description here

6
  • I have no indented option there. Maybe I just need a newer version? Sep 8, 2022 at 11:45
  • The screenshot was from dmaster currently on 5.54.alpha1 but that feature has been around for a while.
    – Aidan
    Sep 8, 2022 at 12:10
  • The normal indentation is 20px but with Shoreditch theme, it's reduced to 10px. On my big screen, I barely see it.
    – samuelsov
    Sep 8, 2022 at 15:36
  • I thought I should be able to follow the above but missing something. goal is to have who ever views this to see "the local group they manage" and therefore "all the contacts related to their local group". It sounds like your answer got Rebecca what she needed but something does not make sense to me
    – petednz - fuzion
    Sep 13, 2023 at 6:32
  • and even if i did, would i then be able to 'add them to an event' or am i better fixing up drupal 9 VBO do this this via Views?
    – petednz - fuzion
    Sep 13, 2023 at 6:34

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.