Views Custom Data attached to Relationships

[jaymcgraw]

I have a custom data set used for Relationships in Civi (the intention is to track committee members, so have a field of "Position").

When I try to get to this using Views, I first create a view on CiviCRM Contacts. I then add a relationship in the view to CiviCRM Relationships: Contact ID A. All is well at this point. I have the CiviCRM contact record along with the committee (organization) record. However, I have no option to add the custom field I had created.

So I moved on and also went ahead and added another relationship in the view to the Drupal User record because I need some fields from there. Then I noticed that the custom field I needed was now in the list. However, when I try to add it, it forces me to choose the Drupal User relationship.

This seems a bug to me. Why does the Drupal User relationship even need to be a part of the view to get to CiviCRM Custom Data?

Here's the SQL:

SELECT civicrm_contact.display_name AS civicrm_contact_display_name, civicrm_contact.id AS id, users_civicrm_uf_match.picture AS users_civicrm_uf_match_picture, users_civicrm_uf_match.uid AS users_civicrm_uf_match_uid, users_civicrm_uf_match.name AS users_civicrm_uf_match_name, users_civicrm_uf_match.mail AS users_civicrm_uf_match_mail, users_civicrm_uf_match__civicrm_value_volunteer_track_10.position_12 AS users_civicrm_uf_match__civicrm_value_volunteer_track_10_pos
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_relationship} civicrm_relationship ON civicrm_contact.id = civicrm_relationship.contact_id_a
INNER JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id AND civicrm_relationship.relationship_type_id = 11
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
LEFT JOIN {civicrm_uf_match} users_civicrm_uf_match__civicrm_uf_match ON users_civicrm_uf_match.uid = users_civicrm_uf_match__civicrm_uf_match.uf_id
LEFT JOIN {civicrm_uf_match} users_civicrm_uf_match_users_civicrm_uf_match__civicrm_uf_match ON users_civicrm_uf_match.uid = users_civicrm_uf_match_users_civicrm_uf_match__civicrm_uf_match.uf_id
LEFT JOIN {civicrm_relationship} users_civicrm_uf_match__civicrm_relationship ON users_civicrm_uf_match__civicrm_uf_match.contact_id = users_civicrm_uf_match__civicrm_relationship.contact_id_a
LEFT JOIN {civicrm_relationship} users_civicrm_uf_match_users_civicrm_uf_match__civicrm_relationship ON users_civicrm_uf_match__civicrm_uf_match.contact_id = users_civicrm_uf_match_users_civicrm_uf_match__civicrm_relationship.contact_id_a
LEFT JOIN {civicrm_value_volunteer_track_10} users_civicrm_uf_match__civicrm_value_volunteer_track_10 ON users_civicrm_uf_match__civicrm_relationship.id = users_civicrm_uf_match__civicrm_value_volunteer_track_10.entity_id

Thanks in advance for any help or insight on this!

[jaymcgraw]

Is there anyone that can help with this? Ultimately, I need to add a custom data field tied to Relationships from Civi into a View and I'm unable to.

[jalama]

So this one isn't really a bug per se as much as a limit of Views and way we originally set-up views support in CiviCRM.  The issue is the same as for Surveys, Address, and Group fields touched on in this issue http://issues.civicrm.org/jira/browse/CRM-8782.

In regards to CiviCRM Relationships (and Activities for that matter) the custom fields are only going to show up in CiviCRM Relationship Views (I'm open to arguments that Relationship views are a waste of time and should be baked into CiviCRM Contact views in a future releases btw).  With our current set -up was cannot hav them appear in both Contact and Relationship views.  Though I'm sure you could patch that issue.  The function to look at would be civicrm_views_custom_data_cache() in the civicrm.views.inc file (/sites/all/modules/drupal/modules/views/civicrm.views.inc).

Note: the same will be true of Participant custom fields from a CiviCRM Event View.

[jaymcgraw]

If not able to get relationship custom data by starting with CiviCRM Contacts, I'm fine with starting with CiviCRM Relationships (though I would agree with you that everything should be baked into CiviCRM Contact views in future releases). However, when I do that, I can't pull in the Drupal User record. I get an error when I add that relationship in the view:

Column not found: 1054 Unknown column 'civicrm_contact_civicrm_relationship.uf_id' in 'on clause'

Any quick fix for that?

Full SQL:

Code: [Select]

SELECT civicrm_contact_civicrm_relationship.display_name AS civicrm_contact_civicrm_relationship_display_name, civicrm_contact_civicrm_relationship.id AS civicrm_contact_civicrm_relationship_id, civicrm_contact_civicrm_relationship_1.display_name AS civicrm_contact_civicrm_relationship_1_display_name, civicrm_contact_civicrm_relationship_1.id AS civicrm_contact_civicrm_relationship_1_id, civicrm_value_volunteer_tracking_5.position_9 AS civicrm_value_volunteer_tracking_5_position_9, civicrm_contact_civicrm_relationship_1__civicrm_value_extended_profile_2.designations_3 AS civicrm_contact_civicrm_relationship_1__civicrm_value_extend
FROM
{civicrm_relationship} civicrm_relationship
INNER JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON contact_id_b = civicrm_contact_civicrm_relationship.id AND civicrm_relationship.relationship_type_id = '5'
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship_1 ON contact_id_a = civicrm_contact_civicrm_relationship_1.id AND civicrm_relationship.relationship_type_id = '5'
LEFT JOIN {civicrm_uf_match} civicrm_contact_civicrm_relationship_1__civicrm_uf_match ON civicrm_contact_civicrm_relationship_1.id = civicrm_contact_civicrm_relationship_1__civicrm_uf_match.contact_id
LEFT JOIN {users} users_civicrm_contact_civicrm_relationship_1__civicrm_uf_match ON civicrm_contact_civicrm_relationship_1.uf_id = users_civicrm_contact_civicrm_relationship_1__civicrm_uf_match.uid
LEFT JOIN {civicrm_value_volunteer_tracking_5} civicrm_value_volunteer_tracking_5 ON civicrm_relationship.id = civicrm_value_volunteer_tracking_5.entity_id
LEFT JOIN {civicrm_value_extended_profile_2} civicrm_contact_civicrm_relationship_1__civicrm_value_extended_profile_2 ON civicrm_contact_civicrm_relationship_1.id = civicrm_contact_civicrm_relationship_1__civicrm_value_extended_profile_2.entity_id
LIMIT 100 OFFSET 0