Author Topic: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts (Read 2176 times)

dccordell · April 01, 2013, 09:06:52 pm

I have a Webform allowing a user to create contacts and set the relation to them as being in the same household using a custom Relationship Type. Now I'd like to include a small block on the user page showing their current household members. I assumed this would be an easy-to-build view, displaying CiviCRM contacts, filtered by those having a particular relationship with the current user. So far, I've been unsuccessful in this.

When I try to create a View of "CiviCRM Contacts", the list is completely empty, before doing any filtering. If I can't get the list of Civi contacts to begin with, then I definitely can't filter them for the current user, so I'm stuck.

Am I going about this the right way? I have checked permissions under CiviCRM, and it appears that Admin and Authenticated User has permission to view the necessary data.

petednz · April 01, 2013, 09:16:09 pm

civi and drupal in same db or not? if not then prefixing is the issue - or lack of it

instructions are something like go to /civicrm/admin/setting/uf?reset=1

copy what is there, paste in to settings.php (caution, find someone who can explain this more thoroughly than i can/will)

but yes in theory, a block above a webform to show the current users relationships etc is entirely doable

dccordell · April 01, 2013, 09:26:45 pm

I have seen those instructions somewhere while I was researching this issue, so I will go dig those back up. I wasn't sure if that applied to us, but apparently it does. What was confusing me was the lack of an error -- there just was no data being retrieved for the view.

Does this seem to be the procedure you are mentioning, pete: http://wiki.civicrm.org/confluence/display/CRMDOC40/Views3+Integration

petednz · April 01, 2013, 09:33:03 pm

That is the documentation indeed

petednz · April 01, 2013, 09:33:48 pm

Except for an older version of civi - but may still apply - but best to dig out the 4.2 verson in wiki

dccordell · April 02, 2013, 11:58:59 am

Well I'm having some issues getting the process on that page to work.

It says to add that big chunk of code (from ../civicrm/admin/setting/uf?reset=1) into the settings.php file, just after $database = array(...);

When I do this, the entire site goes down, saying Access Denied (a Drupal page), even the home page. I reverted to the backup settings.php, and all went back to normal.

I am wondering if it has something to do with the way our settings.php file is set up. I notice that it says the $database = array() is supposed to have several settings fields in it, but our's is blank:

$databases = array();
require_once('dbconfig.php');

And inside dbconfig.php is where our database credentials/settings are located. So I decided I would try to put the chunk of code into dbconfig.php instead of settings.php, but this made the whole site go down completely, 404.

So now I'm not sure what to try. I notice the instructions page I'm using is for Views3 (our version), but for CiviCRM 4.0 (our version is 4.2). I can't find a Wiki for Views3 on Civi4.2, so I'm not sure if that has something to do with this problem or not...

Any ideas?

Edit: Found the Views3/CiviCRM4.2 documentation finally: http://wiki.civicrm.org/confluence/display/CRMDOC42/Views3+Integration
-- going through it now.. will report back.

awasson · April 04, 2013, 04:03:59 pm

There is a difference between D6 and D7 regarding the way the DB info is inserted. I always place my block at the bottom of settings.php for D6 and then below the $databases = array () block in D7.

D6 (At the bottom of the settings.php page):

$db_prefix = array(

... A bunch of CiviCRM DB tables
'civicrm_acl' => '`mydb_civicrm`.',

);

D7 (Just below $databases = array ( . . . ):

$databases['default']['default']['prefix']= array(

... A bunch of CiviCRM DB tables
'civicrm_acl' => '`mydb_civicrm`.',

);

dccordell · April 05, 2013, 10:20:43 am

Ok, when I do it that way, awasson, and try to make a View of CiviCRM Contacts, on the preview it says:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'drupal.ymcacivicrm_contact' doesn't exist

Could this have something to do with prefixing? At least it isn't crashing the whole site now, but still is having problems.

Also, to make a view of CiviCRM Contacts, are you able to select that in the dropbox box on the View creation page, or do you have to select User? I think we may want to create Views of other CiviCRM data (Events, Memberships, etc), so it would be nice to have the CiviCRM options in that list working.

Any ideas?

dccordell · April 05, 2013, 10:29:02 am

Ah, I was wrong.. apparently the code I added earlier was not saved, so when I tested the page, it wasn't even pulling the updated settings.php.

Now, when I add the block like you said you do, awasson, and refresh my Drupal page, it says on a Drupal page:

Page not found

The requested page "/drupal/" could not be found.

Any idea what could be causing this? It causes the whole site to not be found... could this be something wrong with the database settings? Here is what we have:
settings.php:

Code: [Select]

$databases = array();
require_once('dbconfig.php');
$databases['default']['default']['prefix']= array(

  'civicrm_acl'                              => '`cividb`.',
  'civicrm_acl_cache'                        => '`cividb`.',
.....
  'civicrm_website'                          => '`cividb`.',
  'civicrm_worldregion'                      => '`cividb`.',
);

Notice how our $databases = array() is ampty, but on the following line, it is calling for dbconfig.php, which is where our actual db settings are.. and inside it:
dbconfig.php:

Code: [Select]

$databases['default']['default'] = array(
        'driver' => 'mysql',
        'database' => 'drupal',
        'username' => 'drupal',
        'password' => 'password',
        'host' => 'our_host_here',
        'port' => '3306',
        'prefix' => 'ymca'
);

Any ideas? It seems to be looking for our site in the wrong place after adding that block...

awasson · April 05, 2013, 10:39:25 am

The empty databases array is a bit strange... On the sites I have running, it is an array with the drupal database and connection information in it. It's something like the following:
$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'DB_NAME',
'username' => 'DB_USERNAME',
'password' => 'DB_PASSWORD',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);

I have never seen the require_once('dbconfig.php'); line in any of my sites though so it could be that the information that I see in the array is actually in the dbconfig.php file. I could see that happening if the Drupal installation was done via an automated script like Fantastico or something similar.

Note: It is important that both databases share the same DB_USER otherwise I don't think you'll be able to get views working.

Andrew

dccordell · April 05, 2013, 11:18:48 am

Ok, it seems to be working now, at least after a quick test in Views showing Civi contacts! So, for the record, here is what I did:

Originally, we had that funky empty $databases statement in settings.php. After that statement, was the include(dbconfig.php) statement, which basically pulled the database info in from dbconfig.php instead of having it in settings.php.

So I moved the settings from dbconfig.php back into settings.php (probably could have skipped this step though). Then I added in the block of code for Views Integration.
Upon doing this, the site went back down saying Can not find...

I went back to the settings.php and inserted into the top of the added block of code: 'default' => 'ymca', so now we have:

Code: [Select]

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'drupal',
      'username' => 'drupal',
      'password' => 'password',
      'host' => 'db_host_path',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => 'ymca',
    ),
  ),
);

$databases['default']['default']['prefix']= array(
'default' => 'ymca',
  'civicrm_acl'                              => '`cividb`.',
  'civicrm_acl_cache'                        => '`cividb`.',
...

And the "Cannot find.. " error went away and now we seem to be able to pull civi info into views. Nice!

Thanks for all the help guys.. this was frustrating, but should hopefully be worth it now. Will let you know if I have any more problems out of this.

awasson · April 05, 2013, 12:33:53 pm

Wow... Good troubleshooting....

I'm not quite sure how that prefix works into the mix. I've just checked a bunch of my sites to see if prefix is used in them and I'm coming up empty so far. I'll have to keep this in mind in case I run into the same situation.

Have a great weekend!

Andrew

dccordell · April 06, 2013, 12:00:58 pm

Now the problem seems to be getting the View to display the correct contacts.

How can I get it to only list the CiviCRM contacts that are related to the currently logged in drupal user? The relationship from the logged in user to the civicrm contacts is "same household", so I'd like to be able to only display contacts that have that particular relationship with the current Drupal user. Is this possible?

I've played around with all the settings in Views for a couple days now with no luck. Any ideas?

CiviCRM Community Forums (archive)

News:

Author Topic: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts (Read 2176 times)

dccordell

Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

petednz

Re: Creating a Drupal View with CiviCRM Contacts, viewing only those related contact

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only those related contact

petednz

Re: Creating a Drupal View with CiviCRM Contacts, viewing only those related contact

petednz

Re: Creating a Drupal View with CiviCRM Contacts, viewing only those related contact

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only those related contact

awasson

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

awasson

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

awasson

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts

dccordell

Re: Creating a Drupal View with CiviCRM Contacts, viewing only related contacts