CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Drupal Modules (Moderator: Donald Lobo) »
  • Views not handling custom data fields properly
Pages: [1]

Author Topic: Views not handling custom data fields properly  (Read 1876 times)

jptillman

  • I’m new here
  • *
  • Posts: 4
  • Karma: 0
  • CiviCRM version: 3.4.5
  • CMS version: Drupal 6
  • MySQL version: 4.1.22
  • PHP version: 5.3.6
Views not handling custom data fields properly
October 11, 2011, 11:45:55 am
I've added a couple of custom data fields to my Contacts.  I've added them to a profile to present them to users.  I've created a view that attempts to show contact information, including one of these two fields.  When the view preview is executed, I get no results and no errors.  When I remove the custom field, I get results.  Here's the query that Views says is being run when the custom field is in the view:

SELECT DISTINCT(civicrm_membership.id) AS id, civicrm_contact.sort_name AS civicrm_contact_sort_name, civicrm_contact.id AS civicrm_contact_id, civicrm_contact.job_title AS civicrm_contact_job_title, civicrm_contact.organization_name AS civicrm_contact_organization_name, civicrm_email.email AS civicrm_email_email, civicrm_contact.custom_20 AS civicrm_contact_custom_20 FROM civicrm_membership civicrm_membership  LEFT JOIN civicrm_contact civicrm_contact ON civicrm_membership.contact_id = civicrm_contact.id LEFT JOIN civicrm_email civicrm_email ON civicrm_contact.id = civicrm_email.contact_id WHERE (civicrm_contact.contact_type in ('Individual')) AND (civicrm_membership.status_id in ('1', '2', '3')) GROUP BY id ORDER BY civicrm_contact_sort_name ASC


Note how the field custom_20 is added, which is the id of my custom data field.  However, the database has a table called `civicrm_value_privacy_control_12`  which contains the 2 fields I added.  They are not in the civicrm_contact table.  So something is wrong here.

This site is running Civicrm v. 3.4.5, recently upgraded.

I have another Drupal site that is working correctly with views and some other custom fields.  It is running version 3.3.3 of Civi.

Any tips?

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Views not handling custom data fields properly
October 11, 2011, 11:48:00 am
Can you flesh out what types of fields you are having problems with eg are they Multiple Record fields or anything a bit exotic. If so have you tried it with a bog standard custom field?
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

jptillman

  • I’m new here
  • *
  • Posts: 4
  • Karma: 0
  • CiviCRM version: 3.4.5
  • CMS version: Drupal 6
  • MySQL version: 4.1.22
  • PHP version: 5.3.6
Re: Views not handling custom data fields properly
October 11, 2011, 12:02:20 pm
Quote from: petednz on October 11, 2011, 11:48:00 am
Can you flesh out what types of fields you are having problems with eg are they Multiple Record fields or anything a bit exotic. If so have you tried it with a bog standard custom field?

All fields are single value.  Not sure what a "bog standard custom field" is.  My original 2 fields are Yes/No fields, set to Radio Button.  Just to confirm there are no issues with the type of widget, I added an alphanumeric set to Text Box, removed the original field I was testing with and added the text field.  Result is the same.  Note that the id simply incremented, but the syntax is the same:

SELECT DISTINCT(civicrm_membership.id) AS id, civicrm_contact.sort_name AS civicrm_contact_sort_name, civicrm_contact.id AS civicrm_contact_id, civicrm_contact.job_title AS civicrm_contact_job_title, civicrm_contact.organization_name AS civicrm_contact_organization_name, civicrm_email.email AS civicrm_email_email, civicrm_contact.custom_22 AS civicrm_contact_custom_22 FROM civicrm_membership civicrm_membership  LEFT JOIN civicrm_contact civicrm_contact ON civicrm_membership.contact_id = civicrm_contact.id LEFT JOIN civicrm_email civicrm_email ON civicrm_contact.id = civicrm_email.contact_id WHERE (civicrm_contact.contact_type in ('Individual')) AND (civicrm_membership.status_id in ('1', '2', '3')) GROUP BY id ORDER BY civicrm_contact_sort_name ASC

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Views not handling custom data fields properly
October 11, 2011, 01:04:57 pm
hi - not sure what is going on - sorry. Just put a CiviMember type view on my 3.4.6 set up - added a Membership Custom Field - it displays in the Table and the Query looks like this
SELECT civicrm_membership.id AS id, civicrm_membership.join_date AS civicrm_membership_join_date, civicrm_value_income_level_5.income_level_9 AS civicrm_value_income_level_5_income_level_9, civicrm_contact.display_name AS civicrm_contact_display_name, civicrm_contact.id AS civicrm_contact_id FROM civicrm_membership civicrm_membership  LEFT JOIN civicrm_value_income_level_5 civicrm_value_income_level_5 ON civicrm_membership.id = civicrm_value_income_level_5.entity_id LEFT JOIN civicrm_contact civicrm_contact ON civicrm_membership.contact_id = civicrm_contact.id

So I see my fields are coming in with the Field Group name associated
civicrm_value_income_level_5.income_level_9

Yours are coming in as
civicrm_contact.custom_22

So I take it that your Custom Fields are for Contacts, not for Memberships but that this is a Membership based View.

Not sure if this is just a case of a missing handler. Was surprised to see that when I went to look for Custom Fields for contacts that those fields were even available under the Groups: CiviCRM Contacts option.

Maybe Jim will comment on this.
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

jptillman

  • I’m new here
  • *
  • Posts: 4
  • Karma: 0
  • CiviCRM version: 3.4.5
  • CMS version: Drupal 6
  • MySQL version: 4.1.22
  • PHP version: 5.3.6
Re: Views not handling custom data fields properly
October 11, 2011, 06:27:56 pm
Quote from: petednz on October 11, 2011, 01:04:57 pm
So I take it that your Custom Fields are for Contacts, not for Memberships but that this is a Membership based View.

Right you are.  You may be on to something with that.  This view is a clone of another view I'd created (without the new fields) and I hadn't realized it was Membership oriented.  Perhaps if I rebuild it as a Contact-oriented view, I can still achieve what I'm after.  Will try that and report back.

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Views not handling custom data fields properly
October 11, 2011, 06:43:16 pm
Good luck - my next question was going to be wonder aloud if a Contact View might suffice
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

jptillman

  • I’m new here
  • *
  • Posts: 4
  • Karma: 0
  • CiviCRM version: 3.4.5
  • CMS version: Drupal 6
  • MySQL version: 4.1.22
  • PHP version: 5.3.6
Re: Views not handling custom data fields properly
October 11, 2011, 06:47:05 pm
Quote from: jptillman on October 11, 2011, 06:27:56 pm
Quote from: petednz on October 11, 2011, 01:04:57 pm
So I take it that your Custom Fields are for Contacts, not for Memberships but that this is a Membership based View.

Right you are.  You may be on to something with that.  This view is a clone of another view I'd created (without the new fields) and I hadn't realized it was Membership oriented.  Perhaps if I rebuild it as a Contact-oriented view, I can still achieve what I'm after.  Will try that and report back.

That fixed it.  Just for the record, when using my original view configuration (Membership oriented) I found the custom Contact data fields in the pick list of new fields you can add to the view.  They appeared under the main list of Contact fields.  After switching to a Contact-oriented view, an entirely separate category appeared named after my custom data set (called "Privacy Control, in my case") and the custom fields were part of that subgroup of available fields.

It appears that Civicrm was smart enough to add the custom data fields to the list of available fields when I was building my original view, but wasn't quite clever enough to map them to the appropriate data table in the sql query which caused the view to return no data at all and show no error (?!).  That could be quite a trap for someone.  I personally blew about an hour on it before asking for help.

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Views not handling custom data fields properly
October 11, 2011, 06:58:13 pm
agreed - i was surprised that the Contact Custom fields were showing - but you are right perhaps this is an 'almost' new feature that needs a tweak (pete goes off to hunt for a jalama)
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

jdaniluk

  • I’m new here
  • *
  • Posts: 20
  • Karma: 0
  • CiviCRM version: 4.2.0
  • CMS version: Drupal 7.14
  • MySQL version: 5.1
  • PHP version: 5.3
Re: Views not handling custom data fields properly
October 26, 2011, 09:47:57 am
I don't know whether this is a related issue or not.  I'm using Drupal 7.7 and Civi 4.0.5, and I have some custom data fields for Contacts.  The custom data fields are used in a profile on an event registration form, and I want to generate a table showing event participants, including columns displaying the custom data fields. (I don't want to have to click through to each individual participant record in order to see the custom data).

I tried a view of participants.  When I'm building the view it lets me select the custom data fields to display, but then I get an error like
Quote
Column not found: 1054 Unknown column 'civicrm_contact.custom_38' in 'field list'

I also tried making a smart group based on the event participants so I could do a contact-oriented view filtering on the group, but I can't get that to give any results.

I'd even be willing to use a CiviCRM report if I could get the report to display the custom data in columns, but I can't figure out how to do that either. 

Any suggestions?

jdaniluk

  • I’m new here
  • *
  • Posts: 20
  • Karma: 0
  • CiviCRM version: 4.2.0
  • CMS version: Drupal 7.14
  • MySQL version: 5.1
  • PHP version: 5.3
Re: Views not handling custom data fields properly
October 26, 2011, 11:17:27 am
An update to my own issue:

After updating settings.php to add the database information for the new custom data table, I can display the custom data fields in a contact-oriented view filtered by a group (but not a smart group).   I still can't add the custom data fields to a participant-oriented view.

So to do what I want, it looks like I'll have to build a group (not a smart group) of participants and update it manually from "find participants" each time I use it.

Am I missing something obvious?
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Drupal Modules (Moderator: Donald Lobo) »
  • Views not handling custom data fields properly

This forum was archived on 2017-11-26.