2

What is the easiest way to add a custom field that contains multiple contact references a la Activity Target Contacts.

I.E.

Custom Data:::::Extends Organizations
Field Set Name: Investment Round

Custom Field 1 Title: Amount Raised
Custom Field 1 Type: Money

Custom Field 2 Title: Raised Date
Custom Field 2 Type: Date

Custom Field 3 Title: Investor(s)
Custom Field 3 Type: Contact References

If this doesn't exist as a quickhack I could consider working on this if there was support for the idea. It seems like it would involve creating a helper table with the following columns.

CREATE TABLE `civicrm_custom_contact`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Custom Field ID',
  `custom_field_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_custom_group.',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_contact.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `field_contact_id` (`custom_field_id`,`entity_id`),
  KEY `FK_civicrm_custom_contact_custom_field_id` (`custom_field_id`),
  KEY `FK_civicrm_custom_contact_contact_id` (`entity_id`),
  CONSTRAINT `FK_civicrm_custom_contact_custom_field_id` FOREIGN KEY (`custom_field_id`) REFERENCES `civicrm_custom_field` (`id`) ON DELETE CASCADE
  CONSTRAINT `FK_civicrm_custom_contact_contact_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=170 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

making a table which looks like

    id| custom_field_id | entity_id
    1 | 12              | 123
    2 | 13              | 4232

id is the primary key,
custom_field_id is the foreign key to the civicrm_custom_field table,
entity id is the foreign key to the civircm_contact table.

In this way we could link multiple contacts to a custom field similar to the way multiple contacts are added to an activity.

So, to recap, is there an existant way to do this, and if not, would anyone be interested in working on this with me?

  • Can you explain what are the downsides of doing the above as an Activity? It looks like an activity, it sounds like an activity ;-) – petednz - fuzion Apr 11 '16 at 18:51
  • We use custom fields on an activity that bins contacts in a particular way. For example, participants in an activity who had a particular role - it'd be great if I could have the "with" box but as custom data... – Cheryl Apr 11 '16 at 19:10
  • @petednz How easy is it to build reports off of an activity? And is it easy to join those to non-activity custom data? – Vito Mantese Apr 11 '16 at 19:16
  • If you are on Drupal I would say 'easy' and then do it through Views. – petednz - fuzion Apr 11 '16 at 19:18
  • It may also help if you ask 'what is the easiest way to achieve the following outcome' and detail the outcome you need. – petednz - fuzion Apr 11 '16 at 19:19
1

It looks like they are activities (new type investment), with a custom field (amount) and that should get you covered without the need of coding anything.

As for the reporting, as you know sql, I would suggest you to create a custom dataviz using https://civicrm.org/extensions/civisualize-missing-data-visualization-extension (you'll need a bit of javascript to do the graphs, but that's a handful of lines)

  • 1
    I wanted to use custom data on Organizations because I have many other fields tied to organizations that are easily incorporated into reports with the CustomGroupExtends = array('Organization') designation. I love civizualize (Thanks for all your work) but it requires customization and I like that I can quickly add custom fields into a report as they are created with the CustomGroupExtends feature. I use civizualize for Dashboards and it works very well. – Vito Mantese Apr 13 '16 at 17:13

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.