Answers.Body: <p>Below is the query it runs, with the relevant parts being</p>
  <pre><code>LEFT JOIN civicrm_case_activity ca4
            ON civicrm_case.id = ca4.case_id
  LEFT JOIN civicrm_activity t_act
            ON t_act.id = ca4.activity_id
            AND t_act.is_current_revision = 1 
        ...
  GROUP BY case_id ORDER BY activity_date_time ASC 
  </code></pre>
  <p>but this query isn't full-group-by compliant and also I think the GROUP BY is in the wrong place. Also <code>case_id</code> is ambiguous since it could apply to the <code>AS case_id</code> in the select or the civicrm_case_activity.case_id field or the civicrm_case_contact.case_id field.</p>
  <p>So it seems like there's something off about the query.</p>
  <pre><code>SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type       FROM civicrm_case
          INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
          INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id
          LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
          LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
          LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
            AND option_group_case_status.id = case_status.option_group_id )
          LEFT JOIN civicrm_case_activity ca4
            ON civicrm_case.id = ca4.case_id
          LEFT JOIN civicrm_activity t_act
            ON t_act.id = ca4.activity_id
            AND t_act.is_current_revision = 1        LEFT JOIN civicrm_phone
            ON civicrm_phone.contact_id = civicrm_contact.id
              AND civicrm_phone.is_primary = 1
          LEFT JOIN civicrm_relationship case_relationship
            ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2)
            OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2))
              AND case_relationship.is_active
              AND case_relationship.case_id = civicrm_case.id
          LEFT JOIN civicrm_relationship_type case_relation_type
            ON case_relation_type.id = case_relationship.relationship_type_id
              AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1)
   AND civicrm_case.is_deleted = 0
   AND civicrm_contact.is_deleted &lt;&gt; 1
   AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2)
   AND case_relationship.is_active
   AND civicrm_case.status_id IN (1,3)
  GROUP BY case_id  ORDER BY activity_date_time ASC LIMIT 0, 10 
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-08T18:31:18.020Z
Answers.LastActivityDate: 2023-06-08T18:31:18.020Z
Answers.UserDisplayName: Demerit
Body: <p>For a case with overdue (not yet completed) scheduled tasks, checking the &quot;Search Cases with Upcoming Activities&quot; in the &quot;My Cases&quot; dashlet correctly displays the next upcoming task which happens to be in the past (overdue). Unchecking this box, however, displays the third-to-last activity, which is as it happens also in the past (overdue) but comes well after the very next (overdue) task shown when this box is checked. My question is what is the function of the dashlet when the &quot;Search Cases with Upcoming Activities&quot; box is unchecked, and why would it show the third-to-last task in the sequence of tasks for my particular ongoing case. Many thanks!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-06-09T20:49:24.837Z
Comments.Text: I think there are others who are working on using SearchKit to provide alternative Case dashboards. just fyi.
Comments.UserDisplayName: petednz - fuzion
Tags: <civicase><dashlet>
Title: Meaning of "Search Cases with Upcoming Activities" in "My Cases" dashlet
ThreadText: # Meaning of "Search Cases with Upcoming Activities" in "My Cases" dashlet
  
  (Asked by: username_0 on 2023-06-08)
  
  For a case with overdue (not yet completed) scheduled tasks, checking the "Search Cases with Upcoming Activities" in the "My Cases" dashlet correctly displays the next upcoming task which happens to be in the past (overdue). Unchecking this box, however, displays the third-to-last activity, which is as it happens also in the past (overdue) but comes well after the very next (overdue) task shown when this box is checked. My question is what is the function of the dashlet when the "Search Cases with Upcoming Activities" box is unchecked, and why would it show the third-to-last task in the sequence of tasks for my particular ongoing case. Many thanks!
  
  **Tags:** civicase, dashlet
  
  > **Comments:**
  > **username_1**: I think there are others who are working on using SearchKit to provide alternative Case dashboards. just fyi.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2023-06-08. Score: 1
  
  Below is the query it runs, with the relevant parts being
  LEFT JOIN civicrm_case_activity ca4
            ON civicrm_case.id = ca4.case_id
  LEFT JOIN civicrm_activity t_act
            ON t_act.id = ca4.activity_id
            AND t_act.is_current_revision = 1 
        ...
  GROUP BY case_id ORDER BY activity_date_time ASC 
  
  but this query isn't full-group-by compliant and also I think the GROUP BY is in the wrong place. Also case_id is ambiguous since it could apply to the AS case_id in the select or the civicrm_case_activity.case_id field or the civicrm_case_contact.case_id field.
  So it seems like there's something off about the query.
  SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id as activity_type_id, civicrm_case.case_type_id as case_type_id, civicrm_case.status_id as case_status_id, t_act.status_id as status_id, civicrm_case.start_date as case_start_date, GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = 2, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role, t_act.activity_date_time as activity_date_time, t_act.id as activity_id, case_status.label AS case_status, civicrm_case_type.title AS case_type       FROM civicrm_case
          INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
          INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id
          LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
          LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
          LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
            AND option_group_case_status.id = case_status.option_group_id )
          LEFT JOIN civicrm_case_activity ca4
            ON civicrm_case.id = ca4.case_id
          LEFT JOIN civicrm_activity t_act
            ON t_act.id = ca4.activity_id
            AND t_act.is_current_revision = 1        LEFT JOIN civicrm_phone
            ON civicrm_phone.contact_id = civicrm_contact.id
              AND civicrm_phone.is_primary = 1
          LEFT JOIN civicrm_relationship case_relationship
            ON ((case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 2)
            OR (case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = 2))
              AND case_relationship.is_active
              AND case_relationship.case_id = civicrm_case.id
          LEFT JOIN civicrm_relationship_type case_relation_type
            ON case_relation_type.id = case_relationship.relationship_type_id
              AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1)
   AND civicrm_case.is_deleted = 0
   AND civicrm_contact.is_deleted <> 1
   AND (case_relationship.contact_id_b = 2 OR case_relationship.contact_id_a = 2)
   AND case_relationship.is_active
   AND civicrm_case.status_id IN (1,3)
  GROUP BY case_id  ORDER BY activity_date_time ASC LIMIT 0, 10
  
  
  ---
  
  