CiviCRM Community Forums (archive)

Hi,

On a Civi 4.0.8 site, an activity search with no criteria gives the following error:

Unknown column 'civicrm_value_product_categories_4.id' in 'field list'

for this query:

SELECT DISTINCT ( civicrm_activity.id ), contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_activity.id as activity_id, activity_type.label as activity_type, civicrm_activity.subject as activity_subject, civicrm_activity.activity_date_time as activity_date_time, activity_status.label as activity_status, civicrm_activity.duration as activity_duration, civicrm_activity.location as activity_location, civicrm_activity.details as activity_details, civicrm_activity.source_record_id as source_record_id, civicrm_activity.is_test as activity_is_test, civicrm_activity.campaign_id as activity_campaign_id, civicrm_activity.engagement_level as activity_engagement_level, civicrm_activity.source_contact_id as `source_contact_id`, civicrm_value_about_lead_1.id as civicrm_value_about_lead_1_id, civicrm_value_about_lead_1.date_created_2 as custom_2, civicrm_value_about_lead_1.how_heard_4 as custom_4, civicrm_value_about_lead_1.how_heard_details_5 as custom_5, civicrm_value_about_lead_1.school_9 as custom_9, civicrm_value_about_lead_1.formation_continue_14 as custom_14, civicrm_value_about_lead_1.corporate_client_16 as custom_16, civicrm_value_about_lead_1.downloaded_brochure_s_21 as custom_21, civicrm_value_about_lead_1.team_22 as custom_22, civicrm_value_about_lead_1.territory_23 as custom_23, civicrm_value_about_lead_1.source_website_24 as custom_24, civicrm_value_about_lead_1.agreed_t_and_c_30 as custom_30, civicrm_value_about_lead_1.studentnumber_31 as custom_31, civicrm_value_about_lead_1.product_types_34 as custom_34, civicrm_value_about_lead_1.product_s__39 as custom_39, civicrm_value_meeting_fields_2.id as civicrm_value_meeting_fields_2_id, civicrm_value_meeting_fields_2.purpose_of_meeting_17 as custom_17, civicrm_value_enquiry_activity_fields_3.id as civicrm_value_enquiry_activity_fields_3_id, civicrm_value_enquiry_activity_fields_3.level_18 as custom_18, civicrm_value_enquiry_activity_fields_3.enrolment_expiry_date_25 as custom_25, civicrm_value_enquiry_activity_fields_3.enrolment_start_date_26 as custom_26, civicrm_value_enquiry_activity_fields_3.product_code_27 as custom_27, civicrm_value_enquiry_activity_fields_3.enrolment_value_28 as custom_28, civicrm_value_enquiry_activity_fields_3.enrolment_currency_29 as custom_29, civicrm_value_product_categories_4.id as civicrm_value_product_categories_4_id, civicrm_value_product_categories_4.product_33 as custom_33, civicrm_value_area_of_interest_6.id as civicrm_value_area_of_interest_6_id, civicrm_value_area_of_interest_6.products_37 as custom_37, civicrm_value_download_info_7.id as civicrm_value_download_info_7_id, civicrm_value_download_info_7.downloaded_brochure_s__38 as custom_38, civicrm_value_contact_us_form_8.id as civicrm_value_contact_us_form_8_id, civicrm_value_contact_us_form_8.questions__comments_40 as custom_40  FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
                          AND civicrm_activity.is_deleted = 0
                          AND civicrm_activity.is_current_revision = 1 ) LEFT JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status') LEFT JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
                             AND option_group_activity_status.id = activity_status.option_group_id )  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
                             AND option_group_activity_type.id = activity_type.option_group_id )
LEFT JOIN civicrm_value_about_lead_1 ON civicrm_value_about_lead_1.entity_id = contact_a.id
LEFT JOIN civicrm_value_area_of_interest_6 ON civicrm_value_area_of_interest_6.entity_id = civicrm_activity.id
LEFT JOIN civicrm_value_contact_us_form_8 ON civicrm_value_contact_us_form_8.entity_id = civicrm_activity.id
LEFT JOIN civicrm_value_download_info_7 ON civicrm_value_download_info_7.entity_id = civicrm_activity.id
LEFT JOIN civicrm_value_enquiry_activity_fields_3 ON civicrm_value_enquiry_activity_fields_3.entity_id = civicrm_activity.id
LEFT JOIN civicrm_value_meeting_fields_2 ON civicrm_value_meeting_fields_2.entity_id = civicrm_activity.id  WHERE  (  civicrm_activity.status_id IN (1,2) AND civicrm_activity.is_test = 0 )  AND  ( 1 )   GROUP BY civicrm_activity.id   ORDER BY activity_date_time desc  LIMIT 0, 50

The field civicrm_value_product_categories_4 exists in a civicrm_value_X table which isn't joined to in the above query. However the custom field group for civicrm_value_product_categories_4 applies to Events, not Activities.

One possible clue: civicrm_value_product_categories_4 uses an option group that is shared with 2 other fields, one of which applies to Activities.

Any clues welcome, we need to try to track this down ASAP.

Dave J

can u truncate civicrm_cache and see if that fixes it?

It did, thanks! Thought I'd tried that but evidently not. Phew!

can you also check the custom group table and ensure that table extends events and not activities

It does:
  CONSTRAINT `FK_civicrm_value_product_categories_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_event` (`id`) ON DELETE CASCADE


mysql> select * from civicrm_custom_group where id = 4\G
*************************** 1. row ***************************
                         id: 4
                       name: Product_Categories
                      title: Product Categories
                    extends: Event
...
Thanks Lobo.

Cheers,

Dave