Answers.Body: <p>It looks like you are AND'ing the two conditions so it's never true. This seems to work for me:</p>
  <p><a href="https://i.sstatic.net/E13JX.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/E13JX.png" alt="enter image description here" /></a></p>
  <p>So the WHERE clause is like:</p>
  <pre><code>&quot;where&quot;: [
      [
        &quot;OR&quot;,
        [
          [
            &quot;cas.something:name&quot;,
            &quot;IS EMPTY&quot;
          ],
          [
            &quot;cas.something:name&quot;,
            &quot;=&quot;,
            &quot;two&quot;
          ]
        ]
      ]
    ],
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-05-17T15:00:08.123Z
Answers.Text: Thank you! All is sorted and apologies for my poor analysis!
Answers.UserDisplayName: Simon Thornton-Wood
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-05-17T14:52:17.703Z
Answers.LastActivityDate: 2023-05-17T14:52:17.703Z
Answers.UserDisplayName: Demerit
Body: <p>In SearchKit, I am trying to search for Cases which have a specific value assigned to a custom field (&quot;Case Subtype&quot;), or for which the custom field is not populated.</p>
  <p>I don't seem to be able to construct the query to satisfy both conditions, though I have successfully created separate queries to deliver each condition.</p>
  <p>It seems to me that it might be down to a lack of understanding of the relationship between the Cases table and an associated Case custom fields table, albeit that the custom field I am operating on is visible for selection in the query without joining another table.</p>
  <p>Here is the query structure I have tried (and I have tried variations of it too), which yields no results:</p>
  <pre><code>{
    &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;Case_CaseContact_Contact_01.sort_name&quot;,
      &quot;subject&quot;,
      &quot;case_type_id:label&quot;,
      &quot;case_custom.case_custom_subject:label&quot;,
      &quot;status_id:label&quot;,
      &quot;start_date&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.membership_type_id:label&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.status_id:label&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.display_name&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.near_contact_id.display_name&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.membership_type_id:label&quot;,
      &quot;Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.status_id:label&quot;,
      &quot;Case_CaseActivity_Activity_01.subject&quot;,
      &quot;Case_CaseActivity_Activity_01.activity_date_time&quot;
    ],
    &quot;orderBy&quot;: [],
    &quot;where&quot;: [
      [
        &quot;case_type_id:name&quot;,
        &quot;=&quot;,
        &quot;certified_membership&quot;
      ],
      [
        &quot;ISNULL(case_custom.case_custom_subject:name)&quot;,
        &quot;=&quot;,
        true
      ],
      [
        &quot;OR&quot;,
        [
          [
            &quot;case_custom.case_custom_subject:name&quot;,
            &quot;IN&quot;,
            [
              &quot;Uncertified_participant&quot;,
              &quot;Certified_participant&quot;
            ]
          ]
        ]
      ]
  </code></pre>
  
Tags: <custom-fields><civicase><searchkit>
Title: SearchKit: search for Cases with criteria relating to a custom field
ThreadText: # SearchKit: search for Cases with criteria relating to a custom field
  
  (Asked by: username_0 on 2023-05-17)
  
  In SearchKit, I am trying to search for Cases which have a specific value assigned to a custom field ("Case Subtype"), or for which the custom field is not populated.
  I don't seem to be able to construct the query to satisfy both conditions, though I have successfully created separate queries to deliver each condition.
  It seems to me that it might be down to a lack of understanding of the relationship between the Cases table and an associated Case custom fields table, albeit that the custom field I am operating on is visible for selection in the query without joining another table.
  Here is the query structure I have tried (and I have tried variations of it too), which yields no results:
  {
    "version": 4,
    "select": [
      "Case_CaseContact_Contact_01.sort_name",
      "subject",
      "case_type_id:label",
      "case_custom.case_custom_subject:label",
      "status_id:label",
      "start_date",
      "Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.membership_type_id:label",
      "Case_CaseContact_Contact_01_Contact_Membership_contact_id_01.status_id:label",
      "Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.display_name",
      "Case_CaseContact_Contact_01_Contact_RelationshipCache_Contact_01.near_contact_id.display_name",
      "Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.membership_type_id:label",
      "Case_CaseContact_Contact_01_Contact_Membership_contact_id_02.status_id:label",
      "Case_CaseActivity_Activity_01.subject",
      "Case_CaseActivity_Activity_01.activity_date_time"
    ],
    "orderBy": [],
    "where": [
      [
        "case_type_id:name",
        "=",
        "certified_membership"
      ],
      [
        "ISNULL(case_custom.case_custom_subject:name)",
        "=",
        true
      ],
      [
        "OR",
        [
          [
            "case_custom.case_custom_subject:name",
            "IN",
            [
              "Uncertified_participant",
              "Certified_participant"
            ]
          ]
        ]
      ]
  
  **Tags:** custom-fields, civicase, searchkit
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-05-17. Score: 0 | Accepted answer
  
  It looks like you are AND'ing the two conditions so it's never true. This seems to work for me:
  
  So the WHERE clause is like:
  "where": [
      [
        "OR",
        [
          [
            "cas.something:name",
            "IS EMPTY"
          ],
          [
            "cas.something:name",
            "=",
            "two"
          ]
        ]
      ]
    ],
  
  > **Comments:**
  > **username_0**: Thank you! All is sorted and apologies for my poor analysis!  
  
  ---
  
  