Case report showing 1 result
As of 5.10.2 the case summary report joins with relationships. ` SELECT SQL_CALC_FOUND_ROWS c2_civireport.sort_name as civicrm_c2_client_name, c2_civireport.id as civicrm_c2_id, case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.status_id as civicrm_case_status_id, case_civireport.case_type_id as civicrm_case_case_type_id, case_civireport.start_date as civicrm_case_start_date, case_civireport.end_date as civicrm_case_end_date
FROM civicrm_contact contact_civireport inner join civicrm_relationship relationship_civireport on contact_civireport.id = relationship_civireport.contact_id_b inner join civicrm_case case_civireport on case_civireport.id = relationship_civireport.case_id inner join civicrm_relationship_type relationship_type_civireport on relationship_type_civireport.id=relationship_civireport.relationship_type_id inner join civicrm_case_contact case_contact_civireport on case_contact_civireport.case_id = case_civireport.id inner join civicrm_contact c2_civireport on c2_civireport.id=case_contact_civireport.contact_id
WHERE case_civireport.case_type_id REGEXP '[[:cntrl:]]29[[:cntrl:]]' AND ( case_civireport.is_deleted = 0 )
ORDER BY c2_civireport.sort_name ASC
LIMIT 0, 50 `
This only returns 1 result on a client's case summary report. Performing a Find Case returns 400+ results.
Reverting to an earlier version of the report fixes the issue:
` SELECT SQL_CALC_FOUND_ROWS c2_civireport.sort_name as civicrm_c2_client_name, c2_civireport.id as civicrm_c2_id, case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.status_id as civicrm_case_status_id, case_civireport.case_type_id as civicrm_case_case_type_id, case_civireport.start_date as civicrm_case_start_date, case_civireport.end_date as civicrm_case_end_date
FROM civicrm_case case_civireport inner join civicrm_case_contact case_contact_civireport on case_contact_civireport.case_id = case_civireport.id inner join civicrm_contact c2_civireport on c2_civireport.id=case_contact_civireport.contact_id
WHERE case_civireport.case_type_id REGEXP '[[:cntrl:]]29[[:cntrl:]]' AND ( case_civireport.status_id IN (1) )
LIMIT 0, 50
`
It appears the case summary report has changed to support relationships.
Checking the civicrm_relationship for a case ID that is valid, there isn't a case_id present for that case. Ideally this report should not be dependent (INNER JOIN) for a relationship existing and should begin withh the civicrm_case table.