CiviCRM Community Forums (archive)

Hi,

There are quite a few topics related to Views Integration here but none have allowed me to display a custom Civicrm table.field in Views. I am not a programmer.  Our company had developers create a custom Drupal module that integrates custom CiviCRM tables into our site.  One of those is a list of our prices and I would like to take these from the civi database and display them through Views in various places around the site.

I have followed the steps in http://drupal.org/node/18429 for ensuring the connection between Drupal and The CiviCRM database like so:

$db_url['default'] = 'mysqli://dialogmi_Adm1n3:kzOC-&8_6=!F@localhost/dialogmi_ulearn';
$db_url['civi01']= 'mysqli://dialogmi_Adm1n3:kzOC-&8_6=!F@localhost/dialogmi_civi01';

$db_prefix = array(
 'default' => '',
 'civicrm_acl' => 'civicrm.',
 'civicrm_acl_cache' => 'civicrm.',
 'civicrm_acl_entity_role' => 'civicrm.',
 'civicrm_activity' => 'civicrm.',
 'civicrm_activity_assignment' => 'civicrm.',
 'civicrm_activity_target' => 'civicrm.',
 'civicrm_address' => 'civicrm.',
 'civicrm_cache' => 'civicrm.',
 'civicrm_case' => 'civicrm.',
 'civicrm_case_activity' => 'civicrm.',
 'civicrm_case_contact' => 'civicrm.',
 'civicrm_component' => 'civicrm.',
 'civicrm_contact' => 'civicrm.',
 'civicrm_contact_ar_EG' => 'civicrm.',
 'civicrm_contact_en_US' => 'civicrm.',
 'civicrm_contact_ru_RU' => 'civicrm.',
 'civicrm_contribution' => 'civicrm.',
 'civicrm_contribution_page' => 'civicrm.',
 'civicrm_contribution_page_ar_EG' => 'civicrm.',
 'civicrm_contribution_page_en_US' => 'civicrm.',
 'civicrm_contribution_page_ru_RU' => 'civicrm.',
 'civicrm_course_duration_price' => 'civicrm.',
 
 //  more tables ....
);

Then I have tried to input the table into civicrm/drupal/modules/views/civicrm.views.inc  Here is the code that I lifted and tried to alter to match the custom table.field that I want displayed. 

       // Custom code for civicrm_course_duration_price TABLE
    $data['civicrm_course_duration_price']['table']['group']  = t('CiviCRM Course Duration Price');

    $data['civicrm_course_duration_price']['table']['base'] = array(
                                                      'field' => 'id', // Governs the whole mozilla
                                                      'title' => t('CiviCRM Course Duration Price'),
                                                      'help' => t("View displays CiviCRM Prices."),
                                         'database' => 'civi01',
                                                      );
                                            
   //civicrm_course_duration_price - FIELDS

    //Price
    $data['civicrm_course_duration_price']['price'] = array(
                                           'title' => t('price'),
                                 'real field' => 'price',
                                           'help' => t('The price'),
                                           'field' => array(
                                                            'handler' => 'views_handler_field_price',
                                                            'click sortable' => TRUE,
                                                            ),

                                           'argument' => array(
                                                               'handler' => 'views_handler_argument_numeric',
                                                               'numeric' => TRUE,
                                                               ),

                                           'filter' => array(
                                                             'handler' => 'views_handler_filter_numeric',
                                                             ),

                                           'sort' => array(
                                                           'handler' => 'views_handler_sort',
                                                           ),
                                           );



While I can see the title etc in Views once I choose the Filter and select the Field 'price' the following error displays:

Error: handler for civicrm_course_duration_price > price doesn't exist!

I have only followed the instructions seen on various threads and other sites so apologies but I have no real clue about programming. 

Am I missing something in the civicrm.views.inc?

Anybody shed any light on the problem?  Thanks!


Neil

Hi Hershel, I'm trying to do just that and I can do custom data that are connected to either a Membership or a Contribution record - but I can't figure out how to do Custom data that are part of a Contact record.

I'm confused as to what exactly needs to be added to the settings.php - when trying to add my custom data field "Account - Group Name "

I get this warning in Views:
user warning: Unknown column 'civicrm_contact.custom_23' in 'field list' query

when I look up what this is called in the civicrm database I find it is stored under:
civicrm_custom_field (ID is 23).

It's the last piece of this Drupal/Views integration puzzle that I'm missing... driving me crazy.

-- Karin
Drupal 6.20 and CiviCRM 3.4.0 and Views 6.x-2.12

Hi Hershel,

The Set "used for the All Contact Types" that my Custom Data element belongs to is called Contact information. It appears at the bottom of the list showing in the CMS Integration screen (entire list - please see below) as:

'civicrm_value_contact_information_2'      => '`civicrm_lllc_ca`.',

So with this entire list copied into my settings.php I get this warning in Views:
user warning: Unknown column 'civicrm_contact.custom_23' in 'field list' query

when I look up what this is called in the civicrm database I find it is stored under:
civicrm_custom_field (ID is 23).

So I'm not sure what it actually is called. I don't see any of my two dozen custom data that belong to the Set Contact Information in this list. I just tried to pull a different one into Views: similar error message - only the ID differs (of course):
user warning: Unknown column 'civicrm_contact.custom_13'

What do you think I should try next?

-- Karin

$db_prefix = array(
  'civicrm_acl'                              => '`civicrm_lllc_ca`.',
  'civicrm_acl_cache'                        => '`civicrm_lllc_ca`.',
  'civicrm_acl_contact_cache'                => '`civicrm_lllc_ca`.',
  'civicrm_acl_entity_role'                  => '`civicrm_lllc_ca`.',
  'civicrm_activity'                         => '`civicrm_lllc_ca`.',
  'civicrm_activity_assignment'              => '`civicrm_lllc_ca`.',
  'civicrm_activity_target'                  => '`civicrm_lllc_ca`.',
  'civicrm_address'                          => '`civicrm_lllc_ca`.',
  'civicrm_address_format'                   => '`civicrm_lllc_ca`.',
  'civicrm_batch'                            => '`civicrm_lllc_ca`.',
  'civicrm_cache'                            => '`civicrm_lllc_ca`.',
  'civicrm_campaign'                         => '`civicrm_lllc_ca`.',
  'civicrm_campaign_group'                   => '`civicrm_lllc_ca`.',
  'civicrm_case'                             => '`civicrm_lllc_ca`.',
  'civicrm_case_activity'                    => '`civicrm_lllc_ca`.',
  'civicrm_case_contact'                     => '`civicrm_lllc_ca`.',
  'civicrm_component'                        => '`civicrm_lllc_ca`.',
  'civicrm_contact'                          => '`civicrm_lllc_ca`.',
  'civicrm_contact_type'                     => '`civicrm_lllc_ca`.',
  'civicrm_contribution'                     => '`civicrm_lllc_ca`.',
  'civicrm_contribution_page'                => '`civicrm_lllc_ca`.',
  'civicrm_contribution_product'             => '`civicrm_lllc_ca`.',
  'civicrm_contribution_recur'               => '`civicrm_lllc_ca`.',
  'civicrm_contribution_soft'                => '`civicrm_lllc_ca`.',
  'civicrm_contribution_type'                => '`civicrm_lllc_ca`.',
  'civicrm_contribution_widget'              => '`civicrm_lllc_ca`.',
  'civicrm_country'                          => '`civicrm_lllc_ca`.',
  'civicrm_county'                           => '`civicrm_lllc_ca`.',
  'civicrm_currency'                         => '`civicrm_lllc_ca`.',
  'civicrm_custom_field'                     => '`civicrm_lllc_ca`.',
  'civicrm_custom_group'                     => '`civicrm_lllc_ca`.',
  'civicrm_dashboard'                        => '`civicrm_lllc_ca`.',
  'civicrm_dashboard_contact'                => '`civicrm_lllc_ca`.',
  'civicrm_dedupe_exception'                 => '`civicrm_lllc_ca`.',
  'civicrm_dedupe_rule'                      => '`civicrm_lllc_ca`.',
  'civicrm_dedupe_rule_group'                => '`civicrm_lllc_ca`.',
  'civicrm_discount'                         => '`civicrm_lllc_ca`.',
  'civicrm_domain'                           => '`civicrm_lllc_ca`.',
  'civicrm_email'                            => '`civicrm_lllc_ca`.',
  'civicrm_entity_batch'                     => '`civicrm_lllc_ca`.',
  'civicrm_entity_file'                      => '`civicrm_lllc_ca`.',
  'civicrm_entity_financial_trxn'            => '`civicrm_lllc_ca`.',
  'civicrm_entity_tag'                       => '`civicrm_lllc_ca`.',
  'civicrm_event'                            => '`civicrm_lllc_ca`.',
  'civicrm_export_temp'                      => '`civicrm_lllc_ca`.',
  'civicrm_file'                             => '`civicrm_lllc_ca`.',
  'civicrm_financial_account'                => '`civicrm_lllc_ca`.',
  'civicrm_financial_trxn'                   => '`civicrm_lllc_ca`.',
  'civicrm_grant'                            => '`civicrm_lllc_ca`.',
  'civicrm_group'                            => '`civicrm_lllc_ca`.',
  'civicrm_group_contact'                    => '`civicrm_lllc_ca`.',
  'civicrm_group_contact_cache'              => '`civicrm_lllc_ca`.',
  'civicrm_group_nesting'                    => '`civicrm_lllc_ca`.',
  'civicrm_group_organization'               => '`civicrm_lllc_ca`.',
  'civicrm_im'                               => '`civicrm_lllc_ca`.',
  'civicrm_import_job_134b41a965c9cfd8d0de28670c5a3eea' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_170c207a3a8b1855e5840f9d197482ad' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_3f5c0916dee2a34297dcb1b16196bfa3' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_4857a66c97bb705cb7e6959a8ccce321' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_765321f2af338f01b26179d91470a3ba' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_86ad59746c7e7ccc1b8a405f8fad126c' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_8947260f163fd2f6ee75c0a6668a0620' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_968973c3223cd5babe87aa3d9681908c' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_a938a2f805a59d7a89f861f667bac682' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_c9fb11c706c82f97eab206ddc7eb0a1a' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_cac2c471e439acb99a5c1e149811a6c2' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_db8ace9df1ecb66d028a354e34225558' => '`civicrm_lllc_ca`.',
  'civicrm_import_job_f6920b7d5ecdf3810292ba00db62e47b' => '`civicrm_lllc_ca`.',
  'civicrm_line_item'                        => '`civicrm_lllc_ca`.',
  'civicrm_loc_block'                        => '`civicrm_lllc_ca`.',
  'civicrm_location_type'                    => '`civicrm_lllc_ca`.',
  'civicrm_log'                              => '`civicrm_lllc_ca`.',
  'civicrm_mail_settings'                    => '`civicrm_lllc_ca`.',
  'civicrm_mailing'                          => '`civicrm_lllc_ca`.',
  'civicrm_mailing_bounce_pattern'           => '`civicrm_lllc_ca`.',
  'civicrm_mailing_bounce_type'              => '`civicrm_lllc_ca`.',
  'civicrm_mailing_component'                => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_bounce'             => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_confirm'            => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_delivered'          => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_forward'            => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_opened'             => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_queue'              => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_reply'              => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_subscribe'          => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_trackable_url_open' => '`civicrm_lllc_ca`.',
  'civicrm_mailing_event_unsubscribe'        => '`civicrm_lllc_ca`.',
  'civicrm_mailing_group'                    => '`civicrm_lllc_ca`.',
  'civicrm_mailing_job'                      => '`civicrm_lllc_ca`.',
  'civicrm_mailing_recipients'               => '`civicrm_lllc_ca`.',
  'civicrm_mailing_spool'                    => '`civicrm_lllc_ca`.',
  'civicrm_mailing_trackable_url'            => '`civicrm_lllc_ca`.',
  'civicrm_mapping'                          => '`civicrm_lllc_ca`.',
  'civicrm_mapping_field'                    => '`civicrm_lllc_ca`.',
  'civicrm_membership'                       => '`civicrm_lllc_ca`.',
  'civicrm_membership_block'                 => '`civicrm_lllc_ca`.',
  'civicrm_membership_log'                   => '`civicrm_lllc_ca`.',
  'civicrm_membership_payment'               => '`civicrm_lllc_ca`.',
  'civicrm_membership_status'                => '`civicrm_lllc_ca`.',
  'civicrm_membership_type'                  => '`civicrm_lllc_ca`.',
  'civicrm_menu'                             => '`civicrm_lllc_ca`.',
  'civicrm_msg_template'                     => '`civicrm_lllc_ca`.',
  'civicrm_navigation'                       => '`civicrm_lllc_ca`.',
  'civicrm_note'                             => '`civicrm_lllc_ca`.',
  'civicrm_openid'                           => '`civicrm_lllc_ca`.',
  'civicrm_option_group'                     => '`civicrm_lllc_ca`.',
  'civicrm_option_value'                     => '`civicrm_lllc_ca`.',
  'civicrm_participant'                      => '`civicrm_lllc_ca`.',
  'civicrm_participant_payment'              => '`civicrm_lllc_ca`.',
  'civicrm_participant_status_type'          => '`civicrm_lllc_ca`.',
  'civicrm_payment_processor'                => '`civicrm_lllc_ca`.',
  'civicrm_payment_processor_type'           => '`civicrm_lllc_ca`.',
  'civicrm_pcp'                              => '`civicrm_lllc_ca`.',
  'civicrm_pcp_block'                        => '`civicrm_lllc_ca`.',
  'civicrm_persistent'                       => '`civicrm_lllc_ca`.',
  'civicrm_phone'                            => '`civicrm_lllc_ca`.',
  'civicrm_pledge'                           => '`civicrm_lllc_ca`.',
  'civicrm_pledge_block'                     => '`civicrm_lllc_ca`.',
  'civicrm_pledge_payment'                   => '`civicrm_lllc_ca`.',
  'civicrm_preferences'                      => '`civicrm_lllc_ca`.',
  'civicrm_preferences_date'                 => '`civicrm_lllc_ca`.',
  'civicrm_premiums'                         => '`civicrm_lllc_ca`.',
  'civicrm_premiums_product'                 => '`civicrm_lllc_ca`.',
  'civicrm_prevnext_cache'                   => '`civicrm_lllc_ca`.',
  'civicrm_price_field'                      => '`civicrm_lllc_ca`.',
  'civicrm_price_field_value'                => '`civicrm_lllc_ca`.',
  'civicrm_price_set'                        => '`civicrm_lllc_ca`.',
  'civicrm_price_set_entity'                 => '`civicrm_lllc_ca`.',
  'civicrm_product'                          => '`civicrm_lllc_ca`.',
  'civicrm_project'                          => '`civicrm_lllc_ca`.',
  'civicrm_relationship'                     => '`civicrm_lllc_ca`.',
  'civicrm_relationship_type'                => '`civicrm_lllc_ca`.',
  'civicrm_report_instance'                  => '`civicrm_lllc_ca`.',
  'civicrm_saved_search'                     => '`civicrm_lllc_ca`.',
  'civicrm_state_province'                   => '`civicrm_lllc_ca`.',
  'civicrm_subscription_history'             => '`civicrm_lllc_ca`.',
  'civicrm_survey'                           => '`civicrm_lllc_ca`.',
  'civicrm_tag'                              => '`civicrm_lllc_ca`.',
  'civicrm_task'                             => '`civicrm_lllc_ca`.',
  'civicrm_task_action_temp'                 => '`civicrm_lllc_ca`.',
  'civicrm_task_status'                      => '`civicrm_lllc_ca`.',
  'civicrm_tell_friend'                      => '`civicrm_lllc_ca`.',
  'civicrm_timezone'                         => '`civicrm_lllc_ca`.',
  'civicrm_uf_field'                         => '`civicrm_lllc_ca`.',
  'civicrm_uf_group'                         => '`civicrm_lllc_ca`.',
  'civicrm_uf_join'                          => '`civicrm_lllc_ca`.',
  'civicrm_uf_match'                         => '`civicrm_lllc_ca`.',
  'civicrm_value_additional_data_3'          => '`civicrm_lllc_ca`.',
  'civicrm_value_constituent_information_1'  => '`civicrm_lllc_ca`.',
  'civicrm_value_contact_information_2'      => '`civicrm_lllc_ca`.',
  'civicrm_value_statement_of_commitment_4'  => '`civicrm_lllc_ca`.',
  'civicrm_website'                          => '`civicrm_lllc_ca`.',
  'civicrm_worldregion'                      => '`civicrm_lllc_ca`.',
);

The Account - Group Name field I'm trying to pull into Views is of Data Type Alphanumeric (Multi-select) . I just tried a Custom Data field (from the same Set) but of a different type: Yes or No (Radio button): it's producing the same error message in Views (just a different # of course):
user warning: Unknown column 'civicrm_contact.custom_25' in 'field list' query

I've attached a screen shot of the custom data in this Set I'm trying to pull into views:

-- Karin

No issues with Custom Data Fields on a CiviCRM 3.2.5 / Drupal 6.20 install - all are View-ing beautifully. So starting to think this is a 3.4.0 issue.  I'll try this using a 3.3.6 next.