Skip to content

With CiviCRM Case, adding Case as source, the default values cause an Error

When you have Contact and then Case as data sources, use default values, join "Client :: Contact ID" with "Contact :: Contact ID". Create fields Contact ID, Case ID, Case Type ID, create output, Contact type. Execute it.

It fails on the is_deleted field. I think "_case.is_deleted" in the ON case should be "case.is_deleted".

(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT COUNT(*) AS count FROM (SELECT DISTINCT `contact`.`id` AS `contact_id`, `case`.`id` AS `case_id`, `case`.`case_type_id` AS `case_type_id` FROM `civicrm_contact` `contact` INNER JOIN (SELECT `_case`.`id` AS `id`, `_case`.`case_type_id` AS `case_type_id`, `_case`.`subject` AS `subject`, `_case`.`start_date` AS `start_date`, `_case`.`end_date` AS `end_date`, `_case`.`details` AS `details`, `_case`.`status_id` AS `status_id`, `_case`.`is_deleted` AS `is_deleted`, `_case`.`created_date` AS `created_date`, `_case`.`modified_date` AS `modified_date`, `_case_contact`.`case_id` AS `case_contact_case_id`, `_case_contact`.`contact_id` AS `case_contact_contact_id` FROM `civicrm_case` `_case` INNER JOIN `civicrm_case_contact` `_case_contact` ON `_case`.`id`  = `_case_contact`.`case_id`  WHERE 1 AND `_case`.`is_deleted` = 0 ) `case` ON `case`.`case_contact_contact_id`  = `contact`.`id`  AND (`_case`.`is_deleted` = 0) WHERE 1 AND `contact`.`is_deleted` = 0  ORDER BY `contact_id` ASC) `count_combined_sql_data_flow` [nativecode=1054 ** Unknown column '_case.is_deleted' in 'on clause']
    [type] => DB_Error
    [user_info] => SELECT COUNT(*) AS count FROM (SELECT DISTINCT `contact`.`id` AS `contact_id`, `case`.`id` AS `case_id`, `case`.`case_type_id` AS `case_type_id` FROM `civicrm_contact` `contact` INNER JOIN (SELECT `_case`.`id` AS `id`, `_case`.`case_type_id` AS `case_type_id`, `_case`.`subject` AS `subject`, `_case`.`start_date` AS `start_date`, `_case`.`end_date` AS `end_date`, `_case`.`details` AS `details`, `_case`.`status_id` AS `status_id`, `_case`.`is_deleted` AS `is_deleted`, `_case`.`created_date` AS `created_date`, `_case`.`modified_date` AS `modified_date`, `_case_contact`.`case_id` AS `case_contact_case_id`, `_case_contact`.`contact_id` AS `case_contact_contact_id` FROM `civicrm_case` `_case` INNER JOIN `civicrm_case_contact` `_case_contact` ON `_case`.`id`  = `_case_contact`.`case_id`  WHERE 1 AND `_case`.`is_deleted` = 0 ) `case` ON `case`.`case_contact_contact_id`  = `contact`.`id`  AND (`_case`.`is_deleted` = 0) WHERE 1 AND `contact`.`is_deleted` = 0  ORDER BY `contact_id` ASC) `count_combined_sql_data_flow` [nativecode=1054 ** Unknown column '_case.is_deleted' in 'on clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT COUNT(*) AS count FROM (SELECT DISTINCT `contact`.`id` AS `contact_id`, `case`.`id` AS `case_id`, `case`.`case_type_id` AS `case_type_id` FROM `civicrm_contact` `contact` INNER JOIN (SELECT `_case`.`id` AS `id`, `_case`.`case_type_id` AS `case_type_id`, `_case`.`subject` AS `subject`, `_case`.`start_date` AS `start_date`, `_case`.`end_date` AS `end_date`, `_case`.`details` AS `details`, `_case`.`status_id` AS `status_id`, `_case`.`is_deleted` AS `is_deleted`, `_case`.`created_date` AS `created_date`, `_case`.`modified_date` AS `modified_date`, `_case_contact`.`case_id` AS `case_contact_case_id`, `_case_contact`.`contact_id` AS `case_contact_contact_id` FROM `civicrm_case` `_case` INNER JOIN `civicrm_case_contact` `_case_contact` ON `_case`.`id`  = `_case_contact`.`case_id`  WHERE 1 AND `_case`.`is_deleted` = 0 ) `case` ON `case`.`case_contact_contact_id`  = `contact`.`id`  AND (`_case`.`is_deleted` = 0) WHERE 1 AND `contact`.`is_deleted` = 0  ORDER BY `contact_id` ASC) `count_combined_sql_data_flow` [nativecode=1054 ** Unknown column '_case.is_deleted' in 'on clause']"]
)