Answers.Body: <p>Can you try with a LIKE 'CLC' rather than '='? This might help as the contact_sub_types are stored with the weird CRM_Core_DAO::VALUE_SEPARATOR around the values?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-07-15T08:38:19.257Z
Answers.Text: That didn't work, but changing the constraint to "CONTAINS" worked! I guess CIVI must store sub-types as a list of some kind?
Answers.UserDisplayName: tanbog
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-07-15T14:15:51.947Z
Answers.Text: Yes, a contact can have more than 1 sub type and it is stored in 1 field with the mentioned CRM_Core_DAO::VALUE_SEPARATOR in between.
Answers.UserDisplayName: ErikH - CiviCooP
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-07-15T08:26:35.437Z
Answers.LastActivityDate: 2022-07-15T08:26:35.437Z
Answers.UserDisplayName: ErikH - CiviCooP
Body: <p>I am using civicrm on drupal 7. I have configured my Civi instance to accept external REST API calls with authentication.</p>
  <p>I want to get all the values from a set of custom fields associated with an organization sub-type.</p>
  <p>I am making a call something like this:</p>
  <pre><code>$contacts = civicrm_api4('Contact', 'get', [
    'select' =&gt; [
      'id', 
      'display_name', 
      'my_custom_field_set.*',
    ],
    'join' =&gt; [
      ['my_custom_field_set AS my_custom_field_set', 'LEFT'],
    ],
    'where' =&gt; [
      ['my_custom_field_set.name', 'IS NOT NULL'], 
      ['contact_type', '=', 'Organization'], 
      ['contact_sub_type', '=', 'CLC'],
    ],
  ]);
  
  </code></pre>
  <p>This mostly works... except for 5 entries in my database that get skipped. The five failures are all part of another sub-type: <code>ICT Program Client</code> which has some other fields and relationships associated with it.</p>
  <p>If I remove the sub-type <code>CLC</code> WHERE constraint it works. But I would prefer to understand why the presence of the other sub-type on a contact would make the query fail.</p>
  <p>The 5 failures are definitely of sub-type <code>CLC</code>.</p>
  
Tags: <rest><apiv4><sub-types>
Title: Odd REST API result
ThreadText: # Odd REST API result
  
  (Asked by: username_0 on 2022-07-15)
  
  I am using civicrm on drupal 7. I have configured my Civi instance to accept external REST API calls with authentication.
  I want to get all the values from a set of custom fields associated with an organization sub-type.
  I am making a call something like this:
  $contacts = civicrm_api4('Contact', 'get', [
    'select' => [
      'id', 
      'display_name', 
      'my_custom_field_set.*',
    ],
    'join' => [
      ['my_custom_field_set AS my_custom_field_set', 'LEFT'],
    ],
    'where' => [
      ['my_custom_field_set.name', 'IS NOT NULL'], 
      ['contact_type', '=', 'Organization'], 
      ['contact_sub_type', '=', 'CLC'],
    ],
  ]);
  
  
  This mostly works... except for 5 entries in my database that get skipped. The five failures are all part of another sub-type: ICT Program Client which has some other fields and relationships associated with it.
  If I remove the sub-type CLC WHERE constraint it works. But I would prefer to understand why the presence of the other sub-type on a contact would make the query fail.
  The 5 failures are definitely of sub-type CLC.
  
  **Tags:** rest, apiv4, sub-types
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2022-07-15. Score: 2
  
  Can you try with a LIKE 'CLC' rather than '='? This might help as the contact\_sub\_types are stored with the weird CRM\_Core\_DAO::VALUE\_SEPARATOR around the values?
  
  > **Comments:**
  > **username_0**: That didn't work, but changing the constraint to "CONTAINS" worked! I guess CIVI must store sub-types as a list of some kind?  
  > **username_1**: Yes, a contact can have more than 1 sub type and it is stored in 1 field with the mentioned CRM_Core_DAO::VALUE_SEPARATOR in between.  
  
  ---
  
  