Answers.Body: <p><code>civicrm_custom_group.extends_entity_column_value</code> stores more than just a number. You can have a custom group that extends a relationship type, but it can also be an activity type or a contact (sub-)type. If you extend a contact subtype, that field will contain the name of the subtype (not quite the same thing as the label btw), which will be in <em>words</em>.</p>
  
  <p>The other thing is, that a custom group can extend <em>more than one</em> activity/relationship/contact subtype. How this is done, is by separating the values in <code>extends_entity_column_value</code> with the contents of <code>CRM_Core_DAO::VALUE_SEPARATOR</code> (ASCII SOH or 0x01), which isn't always visible when the data is shown to humans.</p>
  
  <p>So, I have for <code>extends_entity_column_value</code> in one of my custom groups, <code>&lt;SOH&gt;School&lt;SOH&gt;Further_Education_Institution&lt;SOH&gt;</code>.</p>
  
  <p>To unpack one of these kind of fields into an array, look at <code>CRM_Utils_Array::explodePadded</code>. (<code>implodePadded</code> being the reverse.) You could also match it using a regex- for example, this looks for a contact_sub_type field containing any of the subtypes School, Further Education Institution or Sixth Form College:</p>
  
  <p><code>AND contact_a.contact_sub_type REGEXP CONCAT('(^|', x'01', ')School|Further_Education_Institution|Sixth_Form_College($|', x'01', ')')</code></p>
  
  <p><strong>Edit</strong>, for further info on where the IDs come from.
  <code>extends_entity_column_value</code> will contain SOH-separated:</p>
  
  <ul>
  <li>Numeric activity type IDs, if the custom group extends activities. Maybe null if it extends <em>all</em> activities. See also, <code>civicrm_option_group</code>'s <code>value</code> column when <code>option_group_id</code> is 2 (you're looking at options in the <code>activity_type</code> option group).</li>
  <li>Numeric relationship type IDs, if the custom group extends relationships. Maybe null if it extends <em>all</em> relationships. See also, <code>civicrm_relationship_type</code>'s <code>id</code> column.</li>
  <li>Contact subtype names, if the custom group extends a contact type. Maybe null if it extends <em>all</em> subtypes of a contact type. See also, <code>civicrm_contact_type</code>'s <code>name</code> column. (If you look in civicrm_contact_type, and any of your contact (sub-)types have spaces/punctuation in their names you'll see the difference between the name and the label. The name is for use by code, the label is for humans to see.)</li>
  </ul>
  
  <p>Custom field groups can also extend things other than contacts, relationships and activities. Events, participants and contributions for example.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-09T16:33:03.703Z
Answers.Text: Thanks @DaveFF. My study with phpMyadmin (visualises SOH) gave me the same answer once I tried selecting _more than one_. Meanwhile I switched to civi API where 'get' results are already returned as array, where you can pass the same array as 'IN' condition. Now I have to figure out the distinctive handling of activity/relationship/contact subtype.
Answers.UserDisplayName: ñull
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-09T17:04:33.657Z
Answers.Text: @ñull I've added some info on what those IDs link to.
Answers.UserDisplayName: DaveFF
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-08T08:56:25.797Z
Answers.LastActivityDate: 2016-06-09T17:03:35.357Z
Answers.LastEditDate: 2016-06-09T17:03:35.357Z
Answers.UserDisplayName: DaveFF
Body: <p>I made a custom group in a Relationship type. When I look back in MySQL table civicrm_custom_group I see that civicrm_relationship_type.id is stored in column civicrm_custom_group.extends_entity_column_value . </p>
  
  <p>I am trying to write query that lists all custom groups in order to write an initialisation script. When doing a JOIN ON involving civicrm_custom_group.extends_entity_column_value, I discovered that is of type VARCHAR. I did not know that even with the MySQL CAST function it is impossible to use mixed types in a ON comparison. </p>
  
  <p>Could it be bug that the id is stored in the wrong column? May be it should have been stored in column civicrm_custom_group.extends_entity_column_id ? That column is correctly of type UNSIGNED INT(10)</p>
  
Tags: <custom-fields><relationships><issues>
Title: Relationship type id stored in the wrong custom group column?
ThreadText: # Relationship type id stored in the wrong custom group column?
  
  (Asked by: username_0 on 2016-06-07)
  
  I made a custom group in a Relationship type. When I look back in MySQL table civicrm\_custom\_group I see that civicrm\_relationship\_type.id is stored in column civicrm\_custom\_group.extends\_entity\_column\_value .
  
  I am trying to write query that lists all custom groups in order to write an initialisation script. When doing a JOIN ON involving civicrm\_custom\_group.extends\_entity\_column\_value, I discovered that is of type VARCHAR. I did not know that even with the MySQL CAST function it is impossible to use mixed types in a ON comparison.
  
  Could it be bug that the id is stored in the wrong column? May be it should have been stored in column civicrm\_custom\_group.extends\_entity\_column\_id ? That column is correctly of type UNSIGNED INT(10)
  
  **Tags:** custom-fields, relationships, issues
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-06-08. Score: 3 | Accepted answer
  
  `civicrm_custom_group.extends_entity_column_value` stores more than just a number. You can have a custom group that extends a relationship type, but it can also be an activity type or a contact (sub-)type. If you extend a contact subtype, that field will contain the name of the subtype (not quite the same thing as the label btw), which will be in *words*.
  
  The other thing is, that a custom group can extend *more than one* activity/relationship/contact subtype. How this is done, is by separating the values in `extends_entity_column_value` with the contents of `CRM_Core_DAO::VALUE_SEPARATOR` (ASCII SOH or 0x01), which isn't always visible when the data is shown to humans.
  
  So, I have for `extends_entity_column_value` in one of my custom groups, `SchoolFurther_Education_Institution`.
  
  To unpack one of these kind of fields into an array, look at `CRM_Utils_Array::explodePadded`. (`implodePadded` being the reverse.) You could also match it using a regex- for example, this looks for a contact\_sub\_type field containing any of the subtypes School, Further Education Institution or Sixth Form College:
  
  `AND contact_a.contact_sub_type REGEXP CONCAT('(^|', x'01', ')School|Further_Education_Institution|Sixth_Form_College($|', x'01', ')')`
  
  **Edit**, for further info on where the IDs come from.
  `extends_entity_column_value` will contain SOH-separated:
  
  * Numeric activity type IDs, if the custom group extends activities. Maybe null if it extends *all* activities. See also, `civicrm_option_group`'s `value` column when `option_group_id` is 2 (you're looking at options in the `activity_type` option group).
  * Numeric relationship type IDs, if the custom group extends relationships. Maybe null if it extends *all* relationships. See also, `civicrm_relationship_type`'s `id` column.
  * Contact subtype names, if the custom group extends a contact type. Maybe null if it extends *all* subtypes of a contact type. See also, `civicrm_contact_type`'s `name` column. (If you look in civicrm\_contact\_type, and any of your contact (sub-)types have spaces/punctuation in their names you'll see the difference between the name and the label. The name is for use by code, the label is for humans to see.)
  
  Custom field groups can also extend things other than contacts, relationships and activities. Events, participants and contributions for example.
  
  > **Comments:**
  > **username_0**: Thanks @username_1. My study with phpMyadmin (visualises SOH) gave me the same answer once I tried selecting _more than one_. Meanwhile I switched to civi API where 'get' results are already returned as array, where you can pass the same array as 'IN' condition. Now I have to figure out the distinctive handling of activity/relationship/contact subtype.  
  > **username_1**: @username_0 I've added some info on what those IDs link to.  
  
  ---
  
  