2

I have read the answers to this question:

How to create a Drupal View with multiple CiviCRM relationships?

and it has helped me to create a Drupal view that links an individual to a household, but when I want to go in the other direction, the relationship only shows me ONE of the individuals in the household and not all of them.

How do I create a view that pulls ALL of the individuals associated with a household?

That is, how do I tell the view to pull all of the related entities and not just the first one?

EDIT: Here is the export for my view code:

$view = new view();
$view->name = 'household_members';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'civicrm_contact';
$view->human_name = 'Household Members';
$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'] = 'Household Members';
$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'] = '10';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: CiviCRM Contacts: CiviCRM Relationship (starting from Contact B) */
$handler->display->display_options['relationships']['relationship_id_b']['id'] = 'relationship_id_b';
$handler->display->display_options['relationships']['relationship_id_b']['table'] = 'civicrm_contact';
$handler->display->display_options['relationships']['relationship_id_b']['field'] = 'relationship_id_b';
$handler->display->display_options['relationships']['relationship_id_b']['label'] = 'Household Members';
$handler->display->display_options['relationships']['relationship_id_b']['relationship_type'] = '7';
/* Relationship: CiviCRM Relationships: Contact ID A */
$handler->display->display_options['relationships']['contact_id_a_']['id'] = 'contact_id_a_';
$handler->display->display_options['relationships']['contact_id_a_']['table'] = 'civicrm_relationship';
$handler->display->display_options['relationships']['contact_id_a_']['field'] = 'contact_id_a_';
$handler->display->display_options['relationships']['contact_id_a_']['relationship'] = 'relationship_id_b';
$handler->display->display_options['relationships']['contact_id_a_']['label'] = 'Members';
/* Relationship: CiviCRM Contacts: CiviCRM Relationship (starting from contact A) */
$handler->display->display_options['relationships']['relationship_id_a']['id'] = 'relationship_id_a';
$handler->display->display_options['relationships']['relationship_id_a']['table'] = 'civicrm_contact';
$handler->display->display_options['relationships']['relationship_id_a']['field'] = 'relationship_id_a';
$handler->display->display_options['relationships']['relationship_id_a']['label'] = 'Individual to Household';
$handler->display->display_options['relationships']['relationship_id_a']['relationship_type'] = '7';
/* Relationship: CiviCRM Relationships: Contact ID B */
$handler->display->display_options['relationships']['contact_id_b_']['id'] = 'contact_id_b_';
$handler->display->display_options['relationships']['contact_id_b_']['table'] = 'civicrm_relationship';
$handler->display->display_options['relationships']['contact_id_b_']['field'] = 'contact_id_b_';
$handler->display->display_options['relationships']['contact_id_b_']['relationship'] = 'relationship_id_a';
$handler->display->display_options['relationships']['contact_id_b_']['label'] = 'Household';
/* Field: CiviCRM Contacts: Contact ID */
$handler->display->display_options['fields']['id']['id'] = 'id';
$handler->display->display_options['fields']['id']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['id']['field'] = 'id';
$handler->display->display_options['fields']['id']['separator'] = '';
/* Field: CiviCRM Contacts: Contact Type */
$handler->display->display_options['fields']['contact_type']['id'] = 'contact_type';
$handler->display->display_options['fields']['contact_type']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['contact_type']['field'] = 'contact_type';
/* 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']['relationship'] = 'contact_id_a_';
$handler->display->display_options['fields']['display_name']['label'] = 'Household Members';
$handler->display->display_options['fields']['display_name']['link_to_civicrm_contact'] = 0;
/* Field: CiviCRM Contacts: Display Name */
$handler->display->display_options['fields']['display_name_1']['id'] = 'display_name_1';
$handler->display->display_options['fields']['display_name_1']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['display_name_1']['field'] = 'display_name';
$handler->display->display_options['fields']['display_name_1']['relationship'] = 'contact_id_b_';
$handler->display->display_options['fields']['display_name_1']['label'] = 'Household Name';
$handler->display->display_options['fields']['display_name_1']['link_to_civicrm_contact'] = 0;
/* Contextual filter: CiviCRM Contacts: Contact ID */
$handler->display->display_options['arguments']['id']['id'] = 'id';
$handler->display->display_options['arguments']['id']['table'] = 'civicrm_contact';
$handler->display->display_options['arguments']['id']['field'] = 'id';
$handler->display->display_options['arguments']['id']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['id']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['id']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['id']['summary_options']['items_per_page'] = '25';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'household-members';
Improve this question
2
  • It would help if you can post the SQL generated by the view you created. My guess is that the view is constructed to only pull the first relationship it found.
    – pkeogan - BackOffice Thinking
    Sep 3, 2015 at 12:31
  • I am thinking so too... I have added a view export to the main question.
    – Jeff Mikels
    Sep 3, 2015 at 16:28

2 Answers 2

Reset to default
1

I'm not sure about proper Stack Exchange protocol here, but I just posted an answer for a different question about how to create a view that includes both guardians with their child all on the same line (if I should write out the same answer or modify it, I certainly can). Perhaps you would find it useful. I would think the same concept would apply to showing all members of a given household: https://civicrm.stackexchange.com/a/5227/105

Improve this answer
3
  • That is a good workaround, but your solution involves installing another module and nesting one view in another view. That would work, but this feels like something that should be built into CiviCRM with relationships and views.
    – Jeff Mikels
    Sep 5, 2015 at 4:26
  • Point granted. In CiviCRM, the Relationship report (under Contact Reports), with Contact B specified as Household, yields the data you're looking for. I'll think more on the Drupal Views front.
    – Allen Hutchison
    Sep 5, 2015 at 5:45
  • Thinking through your question, the only reason we used the extra module to embed the view was to get everything on one line for each child. If that does not matter for your use case, then there is definitely a Views way to do it. I'll post an answer for that scenario.
    – Allen Hutchison
    Sep 5, 2015 at 5:57
0

If getting the list of Household members on the same line is not a concern, then creating the View is doable with two relationships, a filter and a couple of fields.

Create a CiviCRM Contacts View

Relationships

  1. CiviCRM Relationship (starting from contact A); relationship type > Household Member of | Household Member is
  2. CiviCRM Relationships: Contact ID B; in this relationship, reference relationship #1

Filter Criteria

Contact Type = Household; in this filter, reference relationship #2 (Contact ID B)

Fields

  • Whatever info you need for Contact A (the individuals in the household)
  • Whatever info you need for the Household (say, sort name); in this field, reference relationship #2 (Contact ID B)

If you add a sort for the Household name (or whatever field you use for the Household), referencing relationship #2 (Contact ID B), you ensure all of your household members are grouped together.

I just verified with our own Civi install that this yields each household member, not just one.

Hope this helps!

Improve this answer
2
  • Thanks for your input @Allen. Your suggestion appears to generate a list of all contacts filtered to show only contacts who are members of a household and then finally group those contacts by household. Although, it somewhat meets my original criteria, it doesn't meet my goals. I need the view to get a contact id, and if that id is a household, show all members. If that id is for an individual, show the household name. Additionally, having the names on one line is a major plus for usability, However, I think I will have to use your technique and just generate two views.
    – Jeff Mikels
    Sep 9, 2015 at 3:40
  • @Jeff, how are you wanting to get the contact into the view? You could use an exposed filter (or two) to dictate the individual Contact A (to thus show the household) or the household Contact B (to thus show the individuals).
    – Allen Hutchison
    Sep 9, 2015 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.