CiviCRM Drupal Views Integration

I've started working with CiviCRM as I've been looking to build a new website for a NPO I work for, which does Youth Development onboard a Sail Training ship. One of the problems we face is the information we have being in multiple locations, so to get any critical information for our operations onboard (such as Passenger Manifests, Crew Transfers and other details) we have to go through the office. On top of this, alot of these things are STILL put together by hand via spreadsheets and what not, which can make for a slow process. Implementing CiviCRM to start bringing all our membership information, event information and registration together could certainly streamline this process, but critical to this is quickly generating manifests, reports and what not so that whoever needs the data with the right login credentials can get it quickly with no fuss and just print it out. Views seemed the best way to do this as it's flexibility in displaying information queried from the database is excellent, as long as it's given good definition to work with. Basically I've spent the last week or so relearning a bit about programming and improving CiviCRM's Views Integration so it can be better used to display information, at present the base tables defined are Contacts, Events, Participants, Contributions and Activities. These tables are linked in various ways so that you have a fairly flexible framework with which to build your queries from. As well as the fixed tables that CiviCRM uses, this integration will also go through all your Custom Data Groups which are related to the 5 base tables above and automatically assign them. There's also the ability to create links with whatever text you like, that can link to either the usual CiviCRM Management Pages and functions, OR to a custom Drupal View as a Page, with the contact/event/etc ID tacked on the end. What this means is it should be quite feasible to build a front end for grabbing information that your users specifically need for your implementation of CiviCRM, without having to hack up the various CiviCRM Pages or touch a shred of PHP. Not every single field of information that could be used has been implemented yet, for example things like Honorees and Recurring Payments are still on the todo list, but overall a fair amount of information can be pulled from the CiviCRM Database. While this stuff I'm doing is very much hobby work in between my real work, most fields can be added fairly simply using existing handlers so if one or two things are missing that you'd like for your implementation, or if something isn't working as it should let me know on the forums and I'll see if I can sort it out. A diagram of how the Views Tables are joined can be found on the wiki and the adjustments should be in the CiviCRM 2.2 alpha shortly. James Rakich Update: This code has now been committed to svn and will be part of the alpha 2 next week

Comments

Hello, James.

I'm currently working on adding Relationships table to Views 2, and already have some basic fields available. What is the best way to merge my code to your integration code?

Regards,
Danila

check: http://civicrm.org/licensing

and then create an issue (http://issue.civicrm.org/) and attach your change(s) as a patch to the trunk version. The new views code (from james) is at: http://svn.civicrm.org/civicrm/trunk/drupal/modules/views/

lobo

Use CiviCRM? Like CiviCRM? Then show your appreciation and donate to CiviCRM

I have placed the patch to http://issues.civicrm.org/jira/browse/CRM-3995

Hope I specified licensing correctly.

I have tested patched code from views folder on 2.1.4, on a view which I need: aggregated list of companies where our alumni work with a number in brackets:

Company A (3)
Company B (20)

When one clicks on a company, a list of people working in it appears.

I have not yet tested if it is possible to link to several tables continuously - like "Show all contributions from people that work in Company A".

Regards,
Danila

thanx for the contribution :)

lobo

Use CiviCRM? Like CiviCRM? Then show your appreciation and donate to CiviCRM

Good stuff Danila, just having a look at it, the relationships required to link up to civicrm_contact could be a bit tricky because you have to go through the table in BOTH directions, probably requiring multiple relationship handlers to be set up. Play around with it a bit and see how you go, I have an idea for it but haven't the time right now to chase it, on the weekend maybe if you have issues integrating it.

As well as that, rather than joining to relationship_type_id (which is just a type storage area), I'd recommend using the function CRM_Core_PseudoConstant::relationshipType() inside a handler you attach to civicrm_relationship -> relationship_type_id. It'll return an array you can use to convert your type_id into a plain text description. Use civicrm_handler_filter_activity_type.inc and civicrm_handler_field_activity_type.inc as inspiration, you'll just have to edit it a little to pull more from the array, two text fields instead of one.

Re "whoever needs the data with the right login credentials"

(How) does this relate to CiviCRM ACL's and/or Drupal OG or OG User Roles? Being able to integrate CiviCRM data into a Drupal site using Views sounds great. I need to be able to support advanced contact searches that obey group-based access control (if you belong to Group A, you can see fields XYZ of contacts in Group B). User should be able to log into Drupal, and have their OG membership honored by the corresponding CiviCRM group membership and ACL during a search. Have managed to get this to work with a custom Drupal module. Would love to be able to dispense with the custom module.

Views can restrict who can use a view via Drupal Access Permissions and Drupal Roles. Therefore anything you can sync with Drupal's Roles using CiviCRM's sync modules, you can sync with Views to control your access. You set it up when you build the views in Site Building.

I'm far from expert here, so I may be wrong, but it looks like the Views integration doesn't "understand" if Civi is using its own database. I get the following error when I try to create an event View in Drupal:

user warning: Table 'd6.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event LIMIT 0, 10 in...

d6 is my Drupal db - my Civi db is d6civi. Hope this helps... thanks for all the work you do!

http://forum.civicrm.org/index.php/board,42.0.html

lobo

Use CiviCRM? Like CiviCRM? Then show your appreciation and donate to CiviCRM

If I want to assist in enabling more fields to be used in Views2 (eg. civievent max participants, and currently registered participants), where do i go for more info to get a clue?  I looked at the civicrm developer docs but do not see info about Views...