I'm trying to add Notes field to the advanced Contribution search so that I can do wildcard search on the basis of notes but the DAO throwing error: "unknown column", as it's not able to locate civicrm_note table.

Here's my extension file

function MyExtension_civicrm_buildForm($formName, &$form) {
    if ($formName == 'CRM_Contribute_Form_Search' || $formName == 'CRM_Contact_Form_Search_Advanced') {
          ['rows' => 4, 'cols' => 40], 
          array('class' => 'crm-form-textarea')

Custom template file

    {$form.contribution_note.label}<br />

After debugging I found the query is not making a join to civicrm_note table. Here is the query I got on debugging.

SELECT civicrm_contribution.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`, civicrm_contribution.id as contribution_id, civicrm_contribution_recur.id as contribution_recur_id, civicrm_contribution.contribution_status_id as `contribution_status_id`, civicrm_contribution.campaign_id as `contribution_campaign_id`, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.total_amount as `total_amount`, civicrm_contribution.currency as `currency`, civicrm_contribution.thankyou_date as `thankyou_date`, civicrm_contribution.source as `contribution_source`, civicrm_contribution.is_test as `is_test`, civicrm_contribution.is_pay_later as `is_pay_later`, civicrm_product_en_CA.id as product_id, civicrm_product_en_CA.name as `product_name`, civicrm_product_en_CA.id as `contribution_product_id`, civicrm_contribution.financial_type_id as `financial_type_id` FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id LEFT JOIN civicrm_contribution_recur ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id LEFT JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT JOIN civicrm_product_en_CA ON civicrm_contribution_product.product_id =civicrm_product_en_CA.id WHERE ( civicrm_note.note = 'check' AND civicrm_contribution.id != 0 ) AND (contact_a.is_deleted = 0) GROUP BY civicrm_contribution.id ORDER BY `receive_date` desc, `contact_a`.`id` LIMIT 0, 50;

Do I require to use QueryObject hook to override the BAO ?

Also, if I use "note" as the field name instead of "contribution_note", it's doing the lookup for notes associated with the contact rather than contribution notes.


If you go to search - custom search - fulltext search, it already searches this field. Does that fit your needs?

  • Yes, indeed that could be done but I want to make this field available and visible on the advanced search form as well. – civih Sep 27 '19 at 11:33
  • 1
    Are you using Drupal? The fulltext custom search is available to be configured as a block (see the drupal admin - structure - blocks page) which you can put in a sidebar and then it's available on every page (or you can also limit it to certain pages same as any block). – Demerit Sep 30 '19 at 16:24

Maybe I am missing something but couldn't you create a regular CiviCRM custom field and make it searchable? This should also make it available in search masks.

  • Yes, it's the last option. Didn't want to create a custom field for a functionality that's already there in CiviCRM (civicrm_note) and mapping is already defined. – civih Sep 27 '19 at 11:32

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.