Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-21538

CiviReport: Field not found when sorting by Case Type as a section header

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.28
    • Fix Version/s: 4.7.30
    • Component/s: CiviReport
    • Labels:
    • Versioning Impact:
      None (no code merged)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      Attempting to create a Case Detail report with the following fields:

      Subject

      Client name

      Case type

      Case status

      Sorting:

      Case Type > Ascending > Section Header/Group by

      Status > Ascending

      Start Date > Ascending

      And I then get the following error message. This is only replicated with the Section Header/Group By enabled on Case Type.

      Errors below:

      Dec 11 14:17:00 [info] $Fatal Error Details = Array
      {{ (}}
      {{ [callback] => Array}}
      {{ (}}
      {{ [0] => CRM_Core_Error}}
      {{ [1] => handle}}
      {{ )}}[code] => -19
      {{ [message] => DB Error: no such field}}
      {{ [mode] => 16}}
      {{ [debug_info] => SELECT SQL_CALC_FOUND_ROWS case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.start_date as civicrm_case_start_date, case_civireport.status_id as civicrm_case_status_id, case_civireport.case_type_id as civicrm_case_case_type_id, contact_civireport.sort_name as civicrm_contact_client_sort_name, contact_civireport.id as civicrm_contact_id, GROUP_CONCAT(DISTINCT(relationship_civireport.relationship_type_id) ORDER BY relationship_civireport.relationship_type_id) as civicrm_relationship_case_role, civireport_case_types.title as case_type_name , case_civireport.case_type_name as civicrm_case_case_type_name }}
      {{ FROM civicrm_case case_civireport}}
      {{ LEFT JOIN civicrm_case_contact civireport_case_contact on civireport_case_contact.case_id = case_civireport.id}}
      {{ LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = civireport_case_contact.contact_id}}

      {{ LEFT JOIN civicrm_relationship relationship_civireport ON relationship_civireport.case_id = case_civireport.id}}LEFT JOIN civicrm_address address_civireport
      {{ ON contact_civireport.id = address_civireport.contact_id AND}}
      {{ address_civireport.is_primary = 1 }}
      {{ LEFT JOIN civicrm_case_type civireport_case_types}}
      {{ ON case_civireport.case_type_id = civireport_case_types.id}}
      {{ WHERE ( case_civireport.is_deleted = 0 ) GROUP BY case_civireport.id ORDER BY civireport_case_types.title ASC LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_civireport.case_type_name' in 'field list']}}
      {{ [type] => DB_Error}}
      {{ [user_info] => SELECT SQL_CALC_FOUND_ROWS case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.start_date as civicrm_case_start_date, case_civireport.status_id as civicrm_case_status_id, case_civireport.case_type_id as civicrm_case_case_type_id, contact_civireport.sort_name as civicrm_contact_client_sort_name, contact_civireport.id as civicrm_contact_id, GROUP_CONCAT(DISTINCT(relationship_civireport.relationship_type_id) ORDER BY relationship_civireport.relationship_type_id) as civicrm_relationship_case_role, civireport_case_types.title as case_type_name , case_civireport.case_type_name as civicrm_case_case_type_name }}
      {{ FROM civicrm_case case_civireport}}
      {{ LEFT JOIN civicrm_case_contact civireport_case_contact on civireport_case_contact.case_id = case_civireport.id}}
      {{ LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = civireport_case_contact.contact_id}}

      {{ LEFT JOIN civicrm_relationship relationship_civireport ON relationship_civireport.case_id = case_civireport.id}}LEFT JOIN civicrm_address address_civireport
      {{ ON contact_civireport.id = address_civireport.contact_id AND}}
      {{ address_civireport.is_primary = 1 }}
      {{ LEFT JOIN civicrm_case_type civireport_case_types}}
      {{ ON case_civireport.case_type_id = civireport_case_types.id}}
      {{ WHERE ( case_civireport.is_deleted = 0 ) GROUP BY case_civireport.id ORDER BY civireport_case_types.title ASC LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_civireport.case_type_name' in 'field list']}}
      {{ [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.start_date as civicrm_case_start_date, case_civireport.status_id as civicrm_case_status_id, case_civireport.case_type_id as civicrm_case_case_type_id, contact_civireport.sort_name as civicrm_contact_client_sort_name, contact_civireport.id as civicrm_contact_id, GROUP_CONCAT(DISTINCT(relationship_civireport.relationship_type_id) ORDER BY relationship_civireport.relationship_type_id) as civicrm_relationship_case_role, civireport_case_types.title as case_type_name , case_civireport.case_type_name as civicrm_case_case_type_name }}
      {{ FROM civicrm_case case_civireport}}
      {{ LEFT JOIN civicrm_case_contact civireport_case_contact on civireport_case_contact.case_id = case_civireport.id}}
      {{ LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = civireport_case_contact.contact_id}}

      {{ LEFT JOIN civicrm_relationship relationship_civireport ON relationship_civireport.case_id = case_civireport.id}}LEFT JOIN civicrm_address address_civireport
      {{ ON contact_civireport.id = address_civireport.contact_id AND}}
      {{ address_civireport.is_primary = 1 }}
      {{ LEFT JOIN civicrm_case_type civireport_case_types}}
      {{ ON case_civireport.case_type_id = civireport_case_types.id}}
      {{ WHERE ( case_civireport.is_deleted = 0 ) GROUP BY case_civireport.id ORDER BY civireport_case_types.title ASC LIMIT 0, 50 [nativecode=1054 ** Unknown column 'case_civireport.case_type_name' in 'field list']"]}}
      {{ )}}

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              grahamsmith Graham Smith
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: