3

I am working on a church website with civicrm integration.

What I want is a list of church members sorted or grouped by household.

Sorting by sortname is no problem. I am able to get members with the same last name to show up below each other. And then from the eldest to the youngest of a family.

The problem is with children from divorced families. They dont have the same last name but do live (in most cases) on the same address. The mother has her own maiden name, and the children the name of their father. But in this case the children are part of the household of the mother.

When sorting on last name the children do not list under the mother.

I know there is an option to do something with relations, from A to B but it is not clear to me how this works.

I read information that is related, but not specific here, and here.

Any help would be greatly appreciated!

This is an export of my current view:

    $view = new view();
$view->name = 'civicrm_ledenlijst';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'civicrm_contact';
$view->human_name = 'Civicrm ledenlijst';
$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'] = 'Civicrm ledenlijst';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['use_more_text'] = 'meer';
$handler->display->display_options['access']['type'] = 'role';
$handler->display->display_options['access']['role'] = array(
  3 => '3',
  2 => '2',
);
$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['exposed_form']['options']['submit_button'] = 'Toepassen';
$handler->display->display_options['exposed_form']['options']['exposed_sorts_label'] = 'Sorteren op';
$handler->display->display_options['exposed_form']['options']['sort_asc_label'] = 'Oplopend';
$handler->display->display_options['exposed_form']['options']['sort_desc_label'] = 'Aflopend';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '150';
$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['pager']['options']['expose']['items_per_page_label'] = 'Items per pagina';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all_label'] = '- Alle -';
$handler->display->display_options['pager']['options']['expose']['offset_label'] = 'Startpunt';
$handler->display->display_options['pager']['options']['tags']['first'] = '« eerste';
$handler->display->display_options['pager']['options']['tags']['previous'] = '‹ vorige';
$handler->display->display_options['pager']['options']['tags']['next'] = 'volgende ›';
$handler->display->display_options['pager']['options']['tags']['last'] = 'laatste »';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'sort_name' => 'sort_name',
  'birth_date' => 'birth_date',
  'street_address' => 'street_address',
  'postal_code' => 'postal_code',
  'city' => 'city',
  'phone' => 'phone',
  'phone_1' => 'phone_1',
  'email' => 'email',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'sort_name' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'birth_date' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'street_address' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'postal_code' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'city' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'phone' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'phone_1' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
  'email' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
$handler->display->display_options['style_options']['sticky'] = TRUE;
/* Veld: CiviCRM Contacts: Sort Name */
$handler->display->display_options['fields']['sort_name']['id'] = 'sort_name';
$handler->display->display_options['fields']['sort_name']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['sort_name']['field'] = 'sort_name';
$handler->display->display_options['fields']['sort_name']['label'] = 'Naam';
$handler->display->display_options['fields']['sort_name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['sort_name']['link_to_civicrm_contact'] = 0;
/* Veld: CiviCRM Contacts: Birth Date */
$handler->display->display_options['fields']['birth_date']['id'] = 'birth_date';
$handler->display->display_options['fields']['birth_date']['table'] = 'civicrm_contact';
$handler->display->display_options['fields']['birth_date']['field'] = 'birth_date';
$handler->display->display_options['fields']['birth_date']['label'] = 'Geb. datum';
$handler->display->display_options['fields']['birth_date']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['birth_date']['date_format'] = 'custom';
$handler->display->display_options['fields']['birth_date']['custom_date_format'] = 'd-m-Y';
$handler->display->display_options['fields']['birth_date']['second_date_format'] = 'long';
$handler->display->display_options['fields']['birth_date']['timezone'] = 'Europe/Amsterdam';
/* Veld: CiviCRM Address: Full Street Address */
$handler->display->display_options['fields']['street_address']['id'] = 'street_address';
$handler->display->display_options['fields']['street_address']['table'] = 'civicrm_address';
$handler->display->display_options['fields']['street_address']['field'] = 'street_address';
$handler->display->display_options['fields']['street_address']['label'] = 'Adres';
$handler->display->display_options['fields']['street_address']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['street_address']['location_type'] = '0';
$handler->display->display_options['fields']['street_address']['location_op'] = '0';
$handler->display->display_options['fields']['street_address']['is_primary'] = 0;
$handler->display->display_options['fields']['street_address']['is_billing'] = 0;
/* Veld: CiviCRM Address: ZIP / Postal Code */
$handler->display->display_options['fields']['postal_code']['id'] = 'postal_code';
$handler->display->display_options['fields']['postal_code']['table'] = 'civicrm_address';
$handler->display->display_options['fields']['postal_code']['field'] = 'postal_code';
$handler->display->display_options['fields']['postal_code']['label'] = 'Postcode';
$handler->display->display_options['fields']['postal_code']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['postal_code']['location_type'] = '0';
$handler->display->display_options['fields']['postal_code']['location_op'] = '0';
$handler->display->display_options['fields']['postal_code']['is_primary'] = 0;
$handler->display->display_options['fields']['postal_code']['is_billing'] = 0;
/* Veld: CiviCRM Address: City / Suburb */
$handler->display->display_options['fields']['city']['id'] = 'city';
$handler->display->display_options['fields']['city']['table'] = 'civicrm_address';
$handler->display->display_options['fields']['city']['field'] = 'city';
$handler->display->display_options['fields']['city']['label'] = 'Plaats';
$handler->display->display_options['fields']['city']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['city']['location_type'] = '0';
$handler->display->display_options['fields']['city']['location_op'] = '0';
$handler->display->display_options['fields']['city']['is_primary'] = 0;
$handler->display->display_options['fields']['city']['is_billing'] = 0;
/* Veld: CiviCRM Phone Details: Phone Number */
$handler->display->display_options['fields']['phone']['id'] = 'phone';
$handler->display->display_options['fields']['phone']['table'] = 'civicrm_phone';
$handler->display->display_options['fields']['phone']['field'] = 'phone';
$handler->display->display_options['fields']['phone']['label'] = 'Tel. vast';
$handler->display->display_options['fields']['phone']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['phone']['location_type'] = '0';
$handler->display->display_options['fields']['phone']['location_op'] = '0';
$handler->display->display_options['fields']['phone']['is_primary'] = 0;
$handler->display->display_options['fields']['phone']['phone_type'] = '1';
/* Veld: CiviCRM Phone Details: Phone Number */
$handler->display->display_options['fields']['phone_1']['id'] = 'phone_1';
$handler->display->display_options['fields']['phone_1']['table'] = 'civicrm_phone';
$handler->display->display_options['fields']['phone_1']['field'] = 'phone';
$handler->display->display_options['fields']['phone_1']['label'] = 'Tel. mobiel';
$handler->display->display_options['fields']['phone_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['phone_1']['location_type'] = '0';
$handler->display->display_options['fields']['phone_1']['location_op'] = '0';
$handler->display->display_options['fields']['phone_1']['is_primary'] = 0;
$handler->display->display_options['fields']['phone_1']['phone_type'] = '2';
/* Veld: CiviCRM Email: E-mailadres */
$handler->display->display_options['fields']['email']['id'] = 'email';
$handler->display->display_options['fields']['email']['table'] = 'civicrm_email';
$handler->display->display_options['fields']['email']['field'] = 'email';
$handler->display->display_options['fields']['email']['location_type'] = '0';
$handler->display->display_options['fields']['email']['location_op'] = '0';
$handler->display->display_options['fields']['email']['is_primary'] = 1;
/* Sorteercriterium: CiviCRM Contacts: Last Name */
$handler->display->display_options['sorts']['last_name']['id'] = 'last_name';
$handler->display->display_options['sorts']['last_name']['table'] = 'civicrm_contact';
$handler->display->display_options['sorts']['last_name']['field'] = 'last_name';
/* Sorteercriterium: CiviCRM Contacts: Birth Date */
$handler->display->display_options['sorts']['birth_date']['id'] = 'birth_date';
$handler->display->display_options['sorts']['birth_date']['table'] = 'civicrm_contact';
$handler->display->display_options['sorts']['birth_date']['field'] = 'birth_date';
/* Filtercriterium: CiviCRM Contacts: Contact Type */
$handler->display->display_options['filters']['contact_type']['id'] = 'contact_type';
$handler->display->display_options['filters']['contact_type']['table'] = 'civicrm_contact';
$handler->display->display_options['filters']['contact_type']['field'] = 'contact_type';
$handler->display->display_options['filters']['contact_type']['value'] = array(
  'Individual' => 'Individual',
);
/* Filtercriterium: CiviCRM Contacts: Is Deleted */
$handler->display->display_options['filters']['is_deleted']['id'] = 'is_deleted';
$handler->display->display_options['filters']['is_deleted']['table'] = 'civicrm_contact';
$handler->display->display_options['filters']['is_deleted']['field'] = 'is_deleted';
$handler->display->display_options['filters']['is_deleted']['value'] = '0';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'civicrm-ledenlijst';
$handler->display->display_options['menu']['type'] = 'normal';
$handler->display->display_options['menu']['title'] = 'Civicrm ledenlijst';
$handler->display->display_options['menu']['name'] = 'main-menu';
$translatables['civicrm_ledenlijst'] = array(
  t('Master'),
  t('Civicrm ledenlijst'),
  t('meer'),
  t('Toepassen'),
  t('Reset'),
  t('Sorteren op'),
  t('Oplopend'),
  t('Aflopend'),
  t('Items per pagina'),
  t('- Alle -'),
  t('Startpunt'),
  t('« eerste'),
  t('‹ vorige'),
  t('volgende ›'),
  t('laatste »'),
  t('Naam'),
  t('Geb. datum'),
  t('Adres'),
  t('Postcode'),
  t('Plaats'),
  t('Tel. vast'),
  t('Tel. mobiel'),
  t('E-mailadres'),
  t('Page'),
);
3

Welcome to CiviCRM StackExchange!

** This answer assumes you've already created Households in CiviCRM and related Individuals to their respective Households. If you already have your families separated into Households, then you're very close.

We're using the default Household Member of / Household Member is CiviCRM relationship type, so below is how we have our View setup to display Individuals grouped by their Households. (We're a church using CiviCRM and Drupal as well).

Couple of notes first for the sake of the Views setup:

  • "Household Member of" is Side A of the Civi relationship.
  • "Household Member is" is Side B of the Civi relationship.

Our Drupal view first pulls the Households themselves, then the individuals related to the Households. So for our Views Relationship connections, we have to start from Contact B (which is the Households) and set it up this way:

  1. Add a relationship (under the Advanced section of the view): CiviCRM Contacts: CiviCRM Relationship (starting from Contact B). We could specify a relationship there if we wanted to (Household Member of|Household Member is), but we don't have to because of Step 3.
  2. Add a second relationship: CiviCRM Relationships: Contact ID A which uses the relationship from Step 1. Setting up the second relationship
  3. Use a CiviCRM Contacts: Contact Type = Household filter in the Filter Criteria section. (We also use CiviCRM Contacts: Is Deleted (False) and (CiviCRM Contact A) CiviCRM Contacts: Is Deleted (False) to make sure we're not getting Households or Individuals from the Trash).

For the sake of the Field Criteria then, any field that needs to display Household info, we just use the normal fields as is, Display Name, Address, etc. For example: Household Display Name

Any time we are listing the individuals from the Household we specify the Views Relationship we setup in Step 2 above - CiviCRM Relationships: Contact ID A. Displaying the name of an individual in the Household

A final and critical step is the Format Settings (looks like you're using a Table format like we do): Make sure to group by the Household Name (or some other unique Household identifier like Household Contact ID maybe):

Group By Household Name

If you have any questions, please don't hesitate to ask. Views + CiviCRM is incredibly powerful.

  • 1
    Thank you Allen! This was very helpfull! Had to play around a bit but I found out how it works now. Only downside is that single induviduals also have to be in a household to make them show up in the view. If I select households AND induviduals I get duplicates. – Johan den Hollander Jan 6 '16 at 21:31
  • Glad to help! Yes, having single folks in their own household is a bit much, but the upside of the families together offsets that for us. May it serve you well! – Allen Hutchison Jan 6 '16 at 21:33
  • What kind of view do you start off with? CiviCRM Contact? I'm having trouble with an ajax error that pops up every time I try to add the CiviCRM Contacts: CiviCRM Relationship (starting from Contact B) view relationship. – Cynthia Woodard Feb 4 '16 at 2:15
  • @CynthiaWoodard, yes, the view is a CiviCRM Contacts. – Allen Hutchison Feb 4 '16 at 16:55
  • 1
    @cynthia, that might be worth writing out a separate question to see if anyone could help you piece it together. I love Views integration, and could probably think thru it later (or this week), but if you're going for speedy resolution, then putting it out to the wider community as a new question will get the problem more visibility. – Allen Hutchison Feb 7 '16 at 21:31
1

Sounds doable with civicrm relationships as drupal relationships. In CiviCRM menu:

Admin - Customize Data and Screens - Relationship Types

you can see what Contact A is and what Contact B.

In relationship "Household Member of/Household Member is" the Individual is Contact A and the Household is Contact B. Hope that helps.

(Als je wil kan ik meekijken :) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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