1

Update I think the issue I'm running into is from this bug: https://issues.civicrm.org/jira/browse/CRM-18055

Using Civi 4.6.7 and Drupal 7.

This is my first custom report, and I'm attempting to let users sort by some custom fields. By reading through the docs and with the help of all y'all here on the forums, I got the fields added to the Sort dropdown:

class CRM_Report_Form_Special extends CRM_Report_Form {
  protected $_selectAliasesTotal = array();
  protected $_customGroupGroupBy = true;
  protected $_autoIncludeIndexedFieldsAsOrderBys = array( 'Activity' );
  protected $_customGroupExtends = array(
    'Activity',
  );

That part works great. What I can't get to work is when the user actually tries to sort by one of the custom fields, I always get a database error, as if the relevant columns don't exist:

FROM civireport_activity_temp_target tar
GROUP BY civicrm_activity_id  ORDER BY value_volunteer_activity_11_civireport.volunteer_activity_type_30 DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_volunteer_activity_11_civireport.volunteer_activity_type_30' in 'order clause']
    [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 GROUP_CONCAT(civicrm_contact_contact_target SEPARATOR ';') as civicrm_contact_contact_target, GROUP_CONCAT(civicrm_contact_contact_source_id SEPARATOR ';') as civicrm_contact_contact_source_id, GROUP_CONCAT(civicrm_contact_contact_assignee_id SEPARATOR ';') as civicrm_contact_contact_assignee_id, GROUP_CONCAT(civicrm_contact_contact_target_id SEPARATOR ';') as civicrm_contact_contact_target_id, civicrm_email_contact_target_email, civicrm_activity_id, civicrm_activity_source_record_id, civicrm_activity_status_id, civicrm_activity_details, civicrm_value_volunteer_activity_11_custom_30, civicrm_value_volunteer_activity_11_custom_43, civicrm_value_volunteer_activity_11_custom_31, civicrm_value_volunteer_activity_11_custom_42
FROM civireport_activity_temp_target tar
GROUP BY civicrm_activity_id  ORDER BY value_volunteer_activity_11_civireport.volunteer_activity_type_30 DESC  LIMIT 0, 50 [nativecode=1054 ** Unknown column 'value_volunteer_activity_11_civireport.volunteer_activity_type_30' in 'order clause']"]

[Edit] The field I want here is civicrm_value_volunteer_activity_11_civireport.volunteer_activity_type_30, instead of value_volunteer_activity_11_civireport.volunteer_activity_type_30, but I don't understand why the settings at the top of my post don't make that happen already...

I think that the custom fields should work here, right? I don't want to go mucking about with the various SQL statements to fix this, do I? Thanks in advance for any help, and please let me know if I've left out relevant info!

0

I think the issue I'm running into is a result of this bug: https://issues.civicrm.org/jira/browse/CRM-18055

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.