CiviCRM Community Forums (archive)

If you go to CiviCRM > Administer CiviCRM > Manage > Find and Merge Duplicate Contacts, we can set rules which define what a unique Contact is. Then when we import a batch of Contact Data, these redup rules take a role in defining what gets imported as unique data. (At least that is how it appears from the tests I have been running on imported Contact Data.)

Unfortunately there is no similar setup for CiviCRM > CiviEvent Dashboard > Import Participants. I would like to set it so that what was unique is a combination of various custom fields, then my client could (in theory) import the records of Participants for Events (Performances) from their ticketing data. I have tried to import Participants for Preformances, but less than half of them are imported because people will buy several tickets for a performance so that a group of friends can attend; what makes each entry unique in the data-to-be-imported is the combination of performance, purchaser and seat.

I'm sure that there would be other people wanting to customise the import of data for other items such as; Activities, Contributions, Members, but importing Participants for Performances is the big hurdle I would like to get over at the moment and I'm not sure if there is anything much I can do apart from put in a Feature Request.

Please let me know that I'm wrong I'd love to know how to do this.

Thanks for your reply. Yes, we import the Contact records before the Participants. What is happening is that the multiple records of one person's participation are not being imported; namely as a representation of one person buying a bunch of tickets for a group of people. When one person purchases multiple tickets for an event, the record for this is not split up amongst all the participants; it is held in the one account record which is then replicated for each seat purchased.

So, what is wanted is not necessarily the participation of a Contact at a Performance; they really want to know how many tickets someone bought and whether they were full-price, concession, etc. So each imported Participant record will not actually be a record of an individual person at an event; it will be a record of an Account and which Seat were sold by the ticketing company.

The account and the seat (in combination) are what define the unique record being imported. It would be an incomplete record if we were to get the ticketing company to provide the seats grouped as a quantity of tickets per Account, because that would not indicate the different types of tickets within the purchase.

To further this, the dedupe rule-controlled fields are for Contact Imports. How can I use them for Participant Imports?

I'm looking into these various items now. Ultimately, what the client would like is to be able to import Participants where the following fields identify a unique Participant;

• Account-ID (an External ID for the Contact)
• Performance-ID (an External-ID for the Event)
• Seating (for a group-purchase, this identifies individuals)

Supplementary data would be the Ticket-Type.

I am actually considering whether a Seating table for Performances would be worthwhile as an extension of CiviCRM. From this it could be possible to tie in participants based purely on Performance-ID and Seating. In such a case, we could have one Account-ID spread across several seats.
For this to happen though, we would need to be able to use Custom Fields in the dedupe rules. This is where I get down to the core of the initial post in this Thread again.

As you noted;

the contact record is a persistent set of data about a person, and then there would be a separate participant record for each event they attend.

However, having just discussed this with the ticketing company, the critical information is the Seating data for each Performance. Which is where I get back to my initial request; I would like to be able to use Custom Fields in dedupe rules.

Maybe I should change the Title of this Thread.

Yes, you're right, I can use Custom Fields in Dedupe Rules. Unfortunately, I can't find how to use Custom Fields for Participants in Dedupe Rules. This is because I can't find how to set up Dedupe Rules for anything other than Contacts. Does this exist?

and not sure this makes a lot of sense either

Does this mean you agree that Dedupe rules should apply to things other than only Contacts, or does it mean that you don't see the sense in extending dedupe rules to other objects?

Anyway, furthering my research into this issue; I have looked at the description of the table for Participants (you suggested earlier that I look into the tables) and I see I need to brush up on my MySQL:
CREATE TABLE `civicrm_participant` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Participant Id',
  `contact_id` int(10) unsigned default '0' COMMENT 'FK to Contact ID',
  `event_id` int(10) unsigned default '0' COMMENT 'FK to Event ID',
  `status_id` int(10) unsigned NOT NULL default '1' COMMENT 'Participant status ID. FK to civicrm_participant_status_type. Default of 1 should map to status = Registered.',
  `role_id` int(10) unsigned default NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
  `register_date` datetime default NULL COMMENT 'When did contact register for event?',
  `source` varchar(128) collate utf8_unicode_ci default NULL COMMENT 'Source of this event registration.',
  `fee_level` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that we store the label value and not the key',
  `is_test` tinyint(4) default '0',
  `is_pay_later` tinyint(4) default '0',
  `fee_amount` decimal(20,2) default NULL COMMENT 'actual processor fee if known - may be 0.',
  `registered_by_id` int(10) unsigned default NULL COMMENT 'FK to Participant ID',
  `discount_id` int(10) unsigned default NULL COMMENT 'FK to Discount ID',
  `fee_currency` varchar(64) collate utf8_unicode_ci default NULL COMMENT '3 character string, value derived from config setting.',
  PRIMARY KEY  (`id`),
  KEY `index_status_id` (`status_id`),
  KEY `index_role_id` (`role_id`),
  KEY `FK_civicrm_participant_contact_id` (`contact_id`),
  KEY `FK_civicrm_participant_event_id` (`event_id`),
  KEY `FK_civicrm_participant_registered_by_id` (`registered_by_id`),
  KEY `FK_civicrm_participant_discount_id` (`discount_id`),
  CONSTRAINT `FK_civicrm_participant_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_participant_discount_id` FOREIGN KEY (`discount_id`) REFERENCES `civicrm_discount` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_participant_event_id` FOREIGN KEY (`event_id`) REFERENCES `civicrm_event` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_participant_registered_by_id` FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_participant_status_id` FOREIGN KEY (`status_id`) REFERENCES `civicrm_participant_status_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2389 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I'm just trying to figure out whether the table indexing further strengthens the limitations of one-person-per-event, or if I can have multiple instances of a 'person' at an event in order to represent several people at an event where one (identified) person bought all of their tickets.