Match an Imported or New Record by Multiple Fields

Let’s say you want to import a bunch of records to Participants Database, but there are already records in the database, some of which may match records you’re importing, which you want to update. No problem, just match the ID of the incoming record with the record IDs in the database.

But what if you don’t have IDs on your importing records? You can match by some other field, but if that one field doesn’t uniquely identify a record, you may end up overwriting the wrong record. In a case like this, the solution is to match by two (or more) fields so you can be sure you’re matching the right record.

This tutorial explains how to do that using a custom plugin that invokes a two-field match when importing or adding a new record. In the example code, we will be matching the First Name and Last Name fields of an incoming record, then if it finds a match, it will overwrite the matching record. Non-matching records will be imported as new records.

Note: this tutorial applies to Participants Database version 1.9.4.2 and later only. An earlier post describes how this was done before that change.

Using the incoming_record_match Object

When a record is imported, or when a new record is created in the backend or by using the signup form on the frontend, the incoming data is checked against the current contents of the database before it is written to the database. What happens in that check is determined by the settings.

For a new record coming in from the frontend or backend, the match settings from the Signup Form are used. When importing records via CSV, the settings on the Import CSV page are used.

When the new record is submitted or imported, the incoming_record_match class is used to find a match in the database based on the settings. It does two things: tells the main code whether a match is found and provides the ID of the matched record.

Participants Database has a new action (as of version 1.9.4) that provides access to this class so that custom code can change how it works. This is what we will be doing in our example code.

How the Code Works

What I am providing here is a custom plugin that provides a multiple field match operation that overrides the normal single-field match. The object is set up so that if external code provides the matching information, it will use that instead of doing the match in the normal way.

The custom code is set up to match two fields: first_name and last_name. This is defined on line 23 where the $match_field_list variable is set up as an array of field names. You can change this to be any fields you want to check.

These field names are then used to build a database query that looks for a matching record in the database.

If the query does match a record in the database, it tells the match object the ID of the matched record and returns true, indicating there is a match.

The effect of this depends on the matching mode. If it is a CSV import, the matching record will either be updated or if it is set to not import the matching record, it will skip it.

When a new record is getting added (frontend or backend), depending on the preference set in the Signup Form settings, it will do one of three things: show an error message (preventing a duplicate), update the matching record, or simply create a new record.

Feedback Messaging

If you are using this plugin to prevent a new record from being a duplicate of an existing record, you will probably want to add a feedback message for people so they know what happened and what they can do.

This is set up at the top of the plugin file where a filter function is placed on the pdb-validation_error_messages filter. This function replaces the plugin settings duplicate error message with a custom message. You will probably want to change that to the exact wording you want the user to see.

The xnau_check_for_match_feedback function also blanks out the CSS that is used to highlight the fields that caused the error. If you’re proficient with CSS, you may want to add your own CSS that will be used along with the error message.

Here is the Plugin

Please refer to Installing a Plugin from a Gist for instructions on adding this plugin to your site. Remember you will possibly need to modify this example code in order for it to do what you want.

Note: This plugin is only a tutorial example, it is not guaranteed to work or be free of undesired side-effects in your setup. You are expected to be able to make the needed changes to make it work the way you want.