How to display Individual Custom Fields in a Participants View

Question

I want to create a View :

• which displays some Individuals Custom Data (e.g. Passport information)
• where Individuals are filtered according to their Participation to an Event (id=20)

I imagine 3 different ways to do that but none of them is working :

1. Create a Contact oriented View and filter via some civicrm_participant fields but the civicrm_participant table (and fields) is not accessible for filtering :
2. Create a Participant oriented View ans add some Individual Custom Fields : but in this case, the custom fields are not populated though I was able to add them :
3. Create a Smart Group based upon the Participants, and then filter the Contact View on this Smart Group, but Views does not seem to consider Smart Groups, but only Static groups

Of course, I checked that my custom field tables are included in settings

Clearing the caches has no effect

How can I solve my problem ? (civicrm 5.6.0 / Drupal 7.60)

Answer 1

You should be able to achieve what is required by creating a CiviCRM Participants view and using the CiviCRM Participants: Participant's Contact ID as a relationship to bring in contact custom data –

Or import the belew view (I've removed custom data so this needs to be added yourself) -

$view = new view(); $view->name = 'individual_and_participant_view'; $view->description = ''; $view->tag = 'default'; $view->base_table = 'civicrm_participant'; $view->human_name = 'Individual and participant view'; $view->core = 7; $view->api_version = '3.0'; $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master / $handler = $view->new_display('default', 'Master', 'default'); $handler->display->display_options['title'] = 'Individual and participant view'; $handler->display->display_options['use_more_always'] = FALSE; $handler->display->display_options['access']['type'] = 'none'; $handler->display->display_options['cache']['type'] = 'none'; $handler->display->display_options['query']['type'] = 'views_query'; $handler->display->display_options['exposed_form']['type'] = 'basic'; $handler->display->display_options['pager']['type'] = 'full'; $handler->display->display_options['pager']['options']['items_per_page'] = '100'; $handler->display->display_options['pager']['options']['offset'] = '0'; $handler->display->display_options['pager']['options']['id'] = '0'; $handler->display->display_options['pager']['options']['quantity'] = '9'; $handler->display->display_options['style_plugin'] = 'table'; $handler->display->display_options['style_options']['columns'] = array( 'id' => 'id', 'display_name' => 'display_name', ); $handler->display->display_options['style_options']['default'] = '-1'; $handler->display->display_options['style_options']['info'] = array( 'id' => array( 'sortable' => 0, 'default_sort_order' => 'asc', 'align' => '', 'separator' => '', 'empty_column' => 0, ), 'display_name' => array( 'sortable' => 0, 'default_sort_order' => 'asc', 'align' => '', 'separator' => '', 'empty_column' => 0, ), ); / Relationship: CiviCRM Participants: Participant's Contact ID / $handler->display->display_options['relationships']['contact_id_1']['id'] = 'contact_id_1'; $handler->display->display_options['relationships']['contact_id_1']['table'] = 'civicrm_participant'; $handler->display->display_options['relationships']['contact_id_1']['field'] = 'contact_id'; / Field: CiviCRM Events: Title / $handler->display->display_options['fields']['title']['id'] = 'title'; $handler->display->display_options['fields']['title']['table'] = 'civicrm_event'; $handler->display->display_options['fields']['title']['field'] = 'title'; / Field: CiviCRM Contacts: Display Name */ $handler->display->display_options['fields']['display_name']['id'] = 'display_name'; $handler->display->display_options['fields']['display_name']['table'] = 'civicrm_contact'; $handler->display->display_options['fields']['display_name']['field'] = 'display_name'; $handler->display->display_options['fields']['display_name']['link_to_civicrm_contact'] = 0;

/* Display: Page */ $handler = $view->new_display('page', 'Page', 'page'); $handler->display->display_options['path'] = 'individual-and-participant-view';

Answer 2

We have managed to solve a similar scenario with the help of Views Field View extension. It's a bit laborious but you can define a (secundary) 'contact view' with the desired fields and then a (primary) 'participant view' with desired participant fields and the 'contact view' as a view field. You need to add a contextual filter on the participant view with the contact ID.