What is the easiest way to add a custom field that contains multiple contact references a la Activity Target Contacts.
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?