1

Got a Civi newbie question.

I'm looking to use a Contact Reference custom field as a foreign key in a many-to-one entity relationship, but I don't see how to build the search query properly in SearchKit.

Imagine that I've got this setup:

  • Regional offices as Organization subtypes.
  • Local offices as Organization subtypes. Since a local office can belong to only 1 regional office, I create a custom field for Local offices that references the parent Regional office.
  • Individual type contacts can be members of local offices. Again since Individuals can only belong to 1 local office, I create a custom field for Individuals referencing the Local office.

When viewing a Regional Office contact, I want to display a tabular report showing the list of Local offices and a count of the number of members (Individuals) belonging to that Local office.

Viewing contact: Regional office = US East Coast

Local office Number of members
New York 1,245
Boston 837
Philadelphia 469

In quasi-SQL I would write something like:

SELECT local_office.name, COUNT(member.id) as NumberOfMembers
FROM civicrm_contact regional_office
LEFT JOIN civicrm_contact local_office ON local_offices.fk_regional_office_id = regional_office.id
LEFT JOIN civicrm_contact member ON member.fk_local_office_id = local_office.id
WHERE regional_office.contact_sub_type = "RegionalOffice"
AND regional_office.id = <URL.id>
ORDER BY NumberOfMembers DESC

I have searched the forums and documentation but haven't found a clear answer.

In this post, Aidan seems to indicate this is not possible with Contact Reference custom fields: "Lack of searchability is one of the downsides to using Contact References." https://civicrm.stackexchange.com/a/35646/15346

I don't want to use Civi's Relationships because they're more suited to many-to-many entity relationships and would require overhead to ensure the fk relationships stay as many-to-one.

What's the best way to achieve this without resorting to Relationships? Thanks


Civi 5.62.1 Drupal 10

1 Answer 1

2

You'll be able to do this if you upgrade to 5.63. In that version, custom "EntityReference" fields are fully supported as joins. See https://github.com/civicrm/civicrm-core/pull/26192

FYI I recommend you check your custom fields and ensure you are using the new "EntityReference" field type and not the old "ContactReference" type which is not as well supported.

5
  • Thanks. I'll test it out on the new version.
    – Justin657
    Aug 21, 2023 at 17:18
  • This solution works as long as every Local Office has at least 1 member. However if a Local Office has 0 members, it drops off the list because the join query starts with the members (contact type = Individual) then joins on Local Office. Not ideal but I can use it. Ideally I'd be looking to start the query with Local Offices, then left join on Members, which would give me all the Local Offices with their member count even those with no members. I created a demo query here: dmaster.demo.civicrm.org/civicrm/admin/search#/edit/24
    – Justin657
    Aug 24, 2023 at 7:22
  • I see, what you need is for #26192 to be extended so it picks up the reverse as well as the forwards version of the join. Would you be able to sponsor a couple hours dev time to make that happen?
    – Coleman
    Aug 24, 2023 at 13:29
  • I might be able to get my client to chip in. Can you share details on how the sponsorship works?
    – Justin657
    Aug 24, 2023 at 16:29
  • It's pretty simple, you can reach out to me on chat.civicrm.org or by email coleman at civicrm.org. Core team rates are here: civicrm.org/work-with-the-core-team
    – Coleman
    Aug 24, 2023 at 20:52

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.