CiviCRM Community Forums (archive)

I found the following in the documenation:
Some tips and tricks for integrating Views2 and CiviCRM

•If your CiviCRM install uses a different database to your Drupal install, ensure that your Drupal DB user has SELECT rights to your CiviCRM DB. Some hosting platforms might make this difficult. For more information about setting up Drupal multi-database access: http://drupal.org/node/18429
•You will need to set database prefixes in your Drupal settings.php file so that Views2 can access your CiviCRM DB. The prefixes can be seen on a url like: http://drupal.demo.civicrm.org/civicrm/admin/setting/uf?reset=1. You will need to have Views2 enabled to see those settings. Important: You will have to update your settings.php with new prefixes every time you add new Custom Field Group.  Also, after making changes in civicrm you should clear Drupal's view cache at:  Administer › Site building › Views › Tools and press "Clear Views cache".
•If you have CiviCRM and Drupal installed on the same database, and drupal tables are prefixed, you might receive an error in your views saying table does not exist, if this happens, you need to alter your drupal sites/default/settings.php file.  find the part that says $db_prefix = 'drupal_'; and change it to this: $db_prefix = array ('default'=>'drupal_','civicrm_event'=>'','civicrm_activity'=>'','civicrm_contact'=>'','civicrm_address'=>'','civicrm_contribution'=>'');  NOTE: your prefix may be something other then drupal_, just replace drupal_ with whatever prefix you have configured.
•If the above steps dont work for you or your drupal/civicrm db's have a different user name/password, try this recipe from andrew: CiviCRM Views 2 integration with a remote CiviCRM database

The problem is that it does not say what to do:
Can we have a simple procedure and an example of settings.php that works?

When I try to see the civicrm/admin/setting/uf&reset=1 link, it shows me Drupal version 6.15 and Drupal users table name : users, but I thought it should show me the civicrm tables linked to the custom fields and custom groups?

Thanks. I did update my profile, but it did not change the views fields: I still see what I was seing before: CiviCRM Address, Contacts, Email Member, Phone details, relationships, but not the custom data fields I need.
Any idea?

Interesting, they are mostly text custom fields: name, address, occupation, skills, activities,...

Eileen,
Yes, I need 'contact' and 'individual' specific fields to show (I have created groups in CiviCRM first, where people can select on their profile what activity they are interested in). I have the corresponding groups in OG (Organic Groups) in Drupal, and I need to produce views in Drupal with information from CiviCRM because the integration/sync is not working very well and is mostly one way Drupal--> CiviCRM, not the other way around. The core fields show indeed (for example, CiviCRM Contact shows birthdate, ContactID,...)
The specific tables I need are defined in my settings.php as:
'civicrm_value_skills_2'                   => 'civcrm.',
'civicrm_value_system_4'                   => 'civcrm.',
'civicrm_value_volunteer_interest_3'       => 'civcrm.',
and i got the list of tables directly from MySQL.
I do not have a civicrm_value_member_data_1 table

nb: my profile had a typo... I am actually running 3.1.3 for CiviCRM. 3.2 is for ckeditor...

Eileen, thanks for your help, but there is still some confusion between database name and table name.

if my MySQL database name is : home_db
I use one database for all the tables (Drupal, civicrm,...)

examples of table names are:
access
acl
blocks
civicrm_acl
civicrm_acl-cache
...
(all my civicrm table have the civicr_ prefix in their name)
...

node

node_access

...

so now what should be in the array?

'civicrm_value_volunteer_interest_3'       => 'civicrm_',
'civicrm_value_volunteer_interest_3'       => 'home_db.civicrm_',
'civicrm_value_volunteer_interest_3'       => 'home_db',

or something else? obviously php and drupal know already about the nemae of the database by the $db_Url array, so what should the "prefix" be?
Thanks

Yes, I did try without anything, and I just tried all the combinations I proposed. After quite a lot of testing, it looks like

1- In Views, I can create a view with types CiviCRM Contacts and there my Custom data fields are available, but none of the Drupal user fields.
2- If I create a view with type "user", I see fields from the CiviCRM types of Address, Contacts, Email, Member, phone Detail, Relationship and in each of these categories, the fields that are defined by default in CiviCRM, but none of my fields defined in Custom data in CiviCRM and used in profiles

We are getting close, but still not able to generate the views I need with data from Drupal (Username, email, OG membership) and CiviCRM (custom data fields)

I realize this is an old topic but I'm dredging up past issues with a Drupal/civiCRM website and I didn't want to complicate things by adding another thread about the same issue.

@Alan.Guggenheim: Did you manage to successfully expose the data you wanted to access in Views?

We had a similar need and failing any other options we modified a file in Views to achieve that.

File: views/includes/view.inc
Line: 739/740

Right after $start = views_microtime();

We change the $query and $count query replacing "{users}" with "drupal-database-name.users" by adding the following two lines of code:
$query = str_replace("{users}", "databasename_drupal.users", $query);
$count_query = str_replace("{users}", "databasename_drupal.users", $count_query);

We also have provided Drupal with information about our civiCRM database in settings.php

$db_url['default'] = 'mysql://username:password@localhost/databasename_drupal';
$db_url['civicrm'] = 'mysql://username:password@localhost/databasename_civicrm';

This does expose the fields we are looking for filtering our results for but it is difficult to maintain for updates and sometimes the view crashes and returns a results page that ignores all of the filters. I am looking for a better solution. This solution was used with civiCRM 3.1 and now we are on civiCRM 3.4.5.

Is there a recommended method to expose civiCRM Data and particularly Contact and Custom data related to the individual contacts?

Thanks,
Andrew   

Do you ever get the feeling you're just talking to yourself?

Well, I found something kind of neat this morning... If I follow the instructions on the Views 2 Integration page and modify my settings.php file accordingly I can not make a "CiviCRM Contacts" view and create a relationship between the civiCRM Contact record and a Drupal User ID... It just won't work; end of sentence.

However, if I make a Drupal "User" view then I can access all of the civiCRM fields I need to without creating a relationship between Contacts & Users. I must have the changes to the settings.php file in place for this to work.

Initially I thought I would have to create a "CiviCRM Contacts" view because the fields I am returning are 99% civiCRM Contact data. The only user field I need is the user name that links to their profile page.

I hope this helps out someone who is in the same boat.

Cheers,
Andrew