Answers.Body: <p>You'll be able to do this if you upgrade to 5.63. In that version, custom &quot;EntityReference&quot; fields are fully supported as joins. See <a href="https://github.com/civicrm/civicrm-core/pull/26192" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/26192</a></p>
  <p>FYI I recommend you check your custom fields and ensure you are using the new &quot;EntityReference&quot; field type and not the old &quot;ContactReference&quot; type which is not as well supported.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-21T17:18:57.697Z
Answers.Text: Thanks. I'll test it out on the new version.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-24T07:22:56.060Z
Answers.Text: 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: https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/24
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-24T13:29:55.347Z
Answers.Text: I see, what you need is for [#26192](https://github.com/civicrm/civicrm-core/pull/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?
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-24T16:29:34.350Z
Answers.Text: I might be able to get my client to chip in. Can you share details on how the sponsorship works?
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-24T20:52:06.887Z
Answers.Text: 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: https://civicrm.org/work-with-the-core-team
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-20T13:32:42.153Z
Answers.LastActivityDate: 2023-08-20T13:32:42.153Z
Answers.UserDisplayName: Coleman
Body: <p>Got a Civi newbie question.</p>
  <p>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.</p>
  <p>Imagine that I've got this setup:</p>
  <ul>
  <li><strong>Regional offices</strong> as Organization subtypes.</li>
  <li><strong>Local offices</strong> 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.</li>
  <li><strong>Individual</strong> 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.</li>
  </ul>
  <p>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.</p>
  <p>Viewing contact: Regional office = <strong>US East Coast</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Local office</th>
  <th style="text-align: right;">Number of members</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>New York</td>
  <td style="text-align: right;">1,245</td>
  </tr>
  <tr>
  <td>Boston</td>
  <td style="text-align: right;">837</td>
  </tr>
  <tr>
  <td>Philadelphia</td>
  <td style="text-align: right;">469</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In quasi-SQL I would write something like:</p>
  <pre><code>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 = &quot;RegionalOffice&quot;
  AND regional_office.id = &lt;URL.id&gt;
  ORDER BY NumberOfMembers DESC
  </code></pre>
  <p>I have searched the forums and documentation but haven't found a clear answer.</p>
  <p>In this post, Aidan seems to indicate this is not possible with Contact Reference custom fields:
  &quot;Lack of searchability is one of the downsides to using Contact References.&quot;
  <a href="https://civicrm.stackexchange.com/a/35646/15346">https://civicrm.stackexchange.com/a/35646/15346</a></p>
  <p>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.</p>
  <p>What's the best way to achieve this without resorting to Relationships?
  Thanks</p>
  <hr />
  <p>Civi 5.62.1
  Drupal 10</p>
  
Tags: <contacts><custom-fields><searchkit><contact-reference>
Title: How to Search for Contacts stored in a Contact Reference Custom Field
ThreadText: # How to Search for Contacts stored in a Contact Reference Custom Field
  
  (Asked by: username_0 on 2023-08-19)
  
  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
  
  **Tags:** contacts, custom-fields, searchkit, contact-reference
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-08-20. Score: 2 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: Thanks. I'll test it out on the new version.  
  > **username_0**: 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: https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/24  
  > **username_1**: I see, what you need is for [#26192](https://github.com/civicrm/civicrm-core/pull/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?  
  > **username_0**: I might be able to get my client to chip in. Can you share details on how the sponsorship works?  
  > **username_1**: It's pretty simple, you can reach out to me on chat.civicrm.org or by email username_1 at civicrm.org. Core team rates are here: https://civicrm.org/work-with-the-core-team  
  
  ---
  
  