How can I include the number of relationships in a household in exported data for that household

Question

I need to mail multiple copies of our Client and Family Newsletter to households shared by individual clients.

How can I access the 'number of relationships' for each household (visible in the top ribbon), so that I could include that number in the info I give to the mail house, so that they can put the right number of copies in the envelope for each household?

Or is there a better way to achieve the same outcome?

Answer 1

Pete's answer is the only solution I can think of that doesn't require coding, but it's only an option for Drupal users.

If you're on Joomla/Wordpress, you'll need to code a solution, or hire someone to do so. Create a custom field on the contact "number of relationships". Create an extension with civix and use hook_civicrm_post on any relationship being created/updated/deleted; use an API call to getcount for relationships, and populate the number of relationships to your custom field.

If you know SQL but not PHP, an easier solution is to set up a cron job to fill in the custom field with a SQL statement that runs over the whole database. I'm not sure if you can use an UPDATE statement with a GROUP BY though - so you might need to use a subselect, which is expensive.

The other SQL-centric option is to create a trigger to handle this. You can be quick and dirty and drop it right into the database, or you can wrap it in a CiviCRM extension. If you go the latter route, consider coding it into the "Summary Fields" extension: https://civicrm.org/extensions/summary-fields

Answer 2

Sorry I don't have a solution in CiviCRM directly. if you are using Drupal, then I would expect that you can make a View that uses Aggregation that should give you the count of relationshipss along with address etc which you could then print out as Mailing Labels either via a PDF plugin for Views, or view an external 'mail merge' solution.