CiviCRM Community Forums (archive)

I am willing to pay to get this issue sorted out with a patch to views files in a drupal install running civicrm 4.2.2 and drupal 7.

I have created a view (user) listing certain contact fields. I need to set it up this way so that I can access as many civicrm tables as I can as selecting civicrm types of content as opposed to users limits fields I can select (eg no access to some paticipant/events tables when in a civicrm contacts view).
Is there a way to simply create a civicrm general view that combines drupal users, contacts, participants, events, without having to limit tables available?
This would work perfectly for me and I'm sure others as well.
Failing that, my problem right now is that I need to access the civicrm groups field category from the users view. The field is there but simply does not work. I see it has previously been mentioned at for example:
http://forum.civicrm.org/index.php?topic=22475.0
In this case, Lobo suggested:

all the views integration code is under: drupal/modules/views/

your best bet would be to track it from there and trace whats happening.

When one tries to add the civicrm Group field (The same happens with Tags), the following error results:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'civicrm_contact.group' in 'field list'

The reason is there is no field 'group' in the civicrm_contact table.

The resulting query (in my case) is:

SELECT DISTINCT users.name AS users_name, users.uid AS uid, civicrm_participant.id AS civicrm_participant_id, civicrm_contact.first_name AS civicrm_contact_first_name, civicrm_contact.id AS civicrm_contact_id, civicrm_contact.last_name AS civicrm_contact_last_name, users.mail AS users_mail, civicrm_value_event_rideshare_16.rideshare_options_61 AS civicrm_value_event_rideshare_16_rideshare_options_61, civicrm_value_event_rideshare_16.share_point_62 AS civicrm_value_event_rideshare_16_share_point_62, civicrm_participant.register_date AS civicrm_participant_register_date, civicrm_address.city AS civicrm_address_city, civicrm_address2.postal_code AS civicrm_address2_postal_code, civicrm_contact.group AS civicrm_contact_group
FROM
{users} users
LEFT JOIN {civicrm_uf_match} civicrm_uf_match ON users.uid = civicrm_uf_match.uf_id
LEFT JOIN {civicrm_participant} civicrm_participant ON civicrm_uf_match.contact_id = civicrm_participant.contact_id
LEFT JOIN {civicrm_contact} civicrm_contact ON civicrm_uf_match.contact_id = civicrm_contact.id
LEFT JOIN {civicrm_value_event_rideshare_16} civicrm_value_event_rideshare_16 ON civicrm_contact.id = civicrm_value_event_rideshare_16.entity_id
LEFT JOIN {users_roles} users_roles_value_0 ON users.uid = users_roles_value_0.uid AND users_roles_value_0.rid = '6'
LEFT JOIN {civicrm_address} civicrm_address ON civicrm_uf_match.contact_id = civicrm_address.contact_id AND civicrm_address.is_primary = '1'
LEFT JOIN {civicrm_address} civicrm_address2 ON civicrm_uf_match.contact_id = civicrm_address2.contact_id AND civicrm_address2.is_primary = '1'
WHERE (( (civicrm_participant.status_id IN  ('1', '5', '6')) AND (civicrm_value_event_rideshare_16.rideshare_options_61 IN  ('need', 'offer', 'both', 'cannot')) AND( (users_roles_value_0.rid = '6') )))
ORDER BY civicrm_participant_register_date DESC
Everything works fine if I can just get this group field to work so I can filter by it and in that way, know which group the user/contact belongs in.

If only the civicrm Group category could correctly pull in group names and group ID's.
Again, I'm willing to pay for this.
Cheers.

OK, thanks Hershel,
If it does get fixed I'll post whatever patch back here.

Hi Drewpal - were you able to make any progress on views integration?