CiviCRM Community Forums (archive)

I've created an hour tracking system in Drupal using CCK.  I have an Hours content type with multiple fields, and I want to use Views to display the hours, and for each entry, I need to associate it with the user and show the user's first and last name, as well as be able to filter on other user fields, fields which we have on civiCRM.  So, in essence, I need a View that can combine the Content, User, and CiviCRM field types.

1) I've tried a few things, but nothing's really worked out.  I've tried the Node View type, but that doesn't seem to allow for integration of CiviCRM contacts and Drupal users.  I've also tried using the CiviCRM Contact and Users View types, but that doesn't allow me to access the Content fields.  Is there something that I'm missing?

2) Also, I can't really even get Drupal and CiviCRM Integration working at all.  I have followed all the instructions on this forum, and at this website: http://redspire.net/content/civicrm-views-2-integration-remote-civicrm-database#comment-4815, but it doesn't work.  I keep getting this error.

user warning: Table 'civicrm_sandbox.users' doesn't exist
query:
SELECT civicrm_contact.id
AS id, users_civicrm_uf_match.mail
AS users_civicrm_uf_match_mail, users_civicrm_uf_match.uid
AS users_civicrm_uf_match_uid, users_civicrm_uf_match.name AS users_civicrm_uf_match_name
FROM civicrm_contact civicrm_contact
LEFT JOIN civicrm_uf_match civicrm_uf_match ON civicrm_contact.id = civicrm_uf_match.contact_id
LEFT JOIN users users_civicrm_uf_match ON civicrm_uf_match.uf_id = users_civicrm_uf_match.uid
LIMIT 0, 10 in C:\wamp\www\drupal\sites\all\modules\views\includes\view.inc on line 755.
on this CiviCRM Contact View:

$view = new view;
$view->name = 'test_civi';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'civicrm_contact';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'drupal_id' => array(
    'id' => 'drupal_id',
    'table' => 'civicrm_contact',
    'field' => 'drupal_id',
  ),
));
$handler->override_option('fields', array(
  'id' => array(
    'label' => 'Contact ID',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'set_precision' => FALSE,
    'precision' => 0,
    'decimal' => '.',
    'separator' => ',',
    'prefix' => '',
    'suffix' => '',
    'exclude' => 0,
    'id' => 'id',
    'table' => 'civicrm_contact',
    'field' => 'id',
    'relationship' => 'none',
  ),
  'mail' => array(
    'label' => 'E-mail',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_user' => 'mailto',
    'exclude' => 0,
    'id' => 'mail',
    'table' => 'users',
    'field' => 'mail',
    'relationship' => 'drupal_id',
  ),
  'name' => array(
    'label' => 'Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_user' => 1,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'drupal_id',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'first_name' => 'first_name',
  ),
  'info' => array(
    'first_name' => array(
      'sortable' => 0,
      'separator' => '',
    ),
  ),
  'default' => '-1',
));

This is what I have in settings.php

$db_url['default'] = 'mysqli://root@localhost/drupal_sandbox';
$db_url['civicrm'] = 'mysqli://root@localhost/civicrm_sandbox';
$db_prefix['civicrm'] = 'civicrm_';
And I added this to civicrm.views.inc in $data['civicrm_contact']['table']['base'] = array(

'database' => 'civicrm',
I am running Drupal v6.14 and CiviCRM v3.0.0.


So all in all, I'd love to know if any of you know why I can't even get a simple integration view going with just civi ID and Drupal username together, despite following every article on it word for word, and as an added bonus, how I could possibly add CCK Content to the View.

Thanks a lot in advance.

Wow, so I've seen that page before, but I never actually went in and checked what was in this link: http://drupal.demo.civicrm.org/civicrm/admin/setting/uf?reset=1.  I just assumed that all they meant was the 'civicrm' -> 'civicrm_' db prefix.

But I added the prefixes I need now, and I'm able to get the view that I posted earlier working, where it's just the user's CiviCRM ID, and his Drupal email and username showing, so thanks a lot for that.

Does anyone know how I can also incorporate content data in that as well?  So I guess, instead of the view being a CiviCRM Contact or Users Type, for it to be a Node type View.  Is that possible?

well, I think I may be getting somewhere.  However, I get this error:

user warning: SELECT command denied to user 'sandboxdrupal'@'xx.xx.xxx.xx' for table 'civicrm_uf_match'
query:
SELECT COUNT(*)
FROM (SELECT node.nid AS nid FROM node node
LEFT JOIN content_type_hours node_data_field_user ON node.vid = node_data_field_user.vid
LEFT JOIN sandboxdrupal.users users_node_data_field_user ON node_data_field_user.field_user_uid = users_node_data_field_user.uid
LEFT JOIN content_type_hours node_data_field_date ON node.vid = node_data_field_date.vid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN sandboxcivi.civicrm_uf_match users_node_data_field_user__civicrm_uf_match ON users_node_data_field_user.uid = users_node_data_field_user__civicrm_uf_match.uf_id
LEFT JOIN sandboxcivi.civicrm_uf_match users_node_data_field_user_users_node_data_field_user__civicrm_uf_match ON users_node_data_field_user.uid = users_node_data_field_user_users_node_data_field_user__civicrm_uf_match.uf_id
LEFT JOIN sandboxcivi.civicrm_contact users_node_data_field_user__civicrm_contact ON users_node_data_field_user__civicrm_uf_match.contact_id = users_node_data_field_user__civicrm_contact.id
WHERE node.type in ('hours') ) count_alias in /home/content/p/g/f/pgfidelity/html/dev/sites/all/modules/views/includes/view.inc on line 729.
Does this seem like a permissions problem?  sandboxdrupal is the drupal db, but civicrm_uf_match is a civicrm table.  I added this code to civicrm.views.inc:

'database' => 'civicrm',
Do I also need to add the drupal db somehow?  Any insight?

EDIT:
So this really seems to be a permissions problem.  I'm on GoDaddy.  Anyone know if there's anything special I need to do there to give the DB's access to eachother?