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
