Posted on by

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.

Snapshot of import match settings on the Import CSV File page in the Participants Database admin.

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.

9 thoughts on “Match an Imported or New Record by Multiple Fields

  1. Hi Roland,
    I am able to create plugin and install without any errors. Also, no issues identified while submitting new records through signup form. But…It is not giving any feedback message for duplicate entries. Instead it is just updating the matched record automatically. I think there is something in the bottom section of the code that needs to be updated. Since I am just a rookie in understanding the codes, I need your help in fixing this issue. Thank you

    Raju

    1. OK, can you explain how you want it to work? That article describes matching a record and updating the matched record. If instead you want it to add a new record if it finds the match you need to change the “Duplicate Record Preference” setting so that it will do that.

      1. Thanks for your reply Roland.

        Currently, without this plugin, it is adding new record without any match logic. With this, it is updating the existing matched record. My expectation is to have a feedback message saying ‘You must choose a different First Name or Last Name, the name you submitted matches an existing record.’, and then, Displays the details of it, Then prompts, Do you want to … “UPDATE” button, “Add NEW” button.

        1. Make sure the “Duplicate Record PReference” is set to “Show a validation error message” so that the error message will show if a new signup that matches an existing record is submitted.

        2. Great! I was wandering for “Duplicate Record Preference” in the plugin code. My bad. I found it in settings, and updated it, Now it is displaying the feedback message. Thank you so much.

          Is there a way we can add functionality of displaying the match record (few fields only) along with feedback message?

        3. Certainly it is possible, but it will be a bit complex because you’ll need to restructure the plugin as a class so that it can store the information of which record was matched, then use that to generate the error message. The code I provided is really only meant to be a proof of concept, it will be up to you to add any specific functionality you need.

        4. Ok. Thank you Roland!. Commendable support.

  2. Hello Roland
    This looks like it’s exactly what I’ve been looking for. When I tested the ‘match settings’ on the sign up form it did not display the validation error message as I expected but went ahead and created the record anyway. Is it because it does not work with pdb_signup_member_payment?
    Patrick

    1. To test your code, first clear the error log, then run your test. Check the error log right after that for information about how it worked.

Leave a Reply

Your email address will not be published. Required fields are marked *

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.

9 thoughts on “Match an Imported or New Record by Multiple Fields

  1. Hi Roland,
    I am able to create plugin and install without any errors. Also, no issues identified while submitting new records through signup form. But…It is not giving any feedback message for duplicate entries. Instead it is just updating the matched record automatically. I think there is something in the bottom section of the code that needs to be updated. Since I am just a rookie in understanding the codes, I need your help in fixing this issue. Thank you

    Raju

    1. OK, can you explain how you want it to work? That article describes matching a record and updating the matched record. If instead you want it to add a new record if it finds the match you need to change the “Duplicate Record Preference” setting so that it will do that.

      1. Thanks for your reply Roland.

        Currently, without this plugin, it is adding new record without any match logic. With this, it is updating the existing matched record. My expectation is to have a feedback message saying ‘You must choose a different First Name or Last Name, the name you submitted matches an existing record.’, and then, Displays the details of it, Then prompts, Do you want to … “UPDATE” button, “Add NEW” button.

        1. Make sure the “Duplicate Record PReference” is set to “Show a validation error message” so that the error message will show if a new signup that matches an existing record is submitted.

        2. Great! I was wandering for “Duplicate Record Preference” in the plugin code. My bad. I found it in settings, and updated it, Now it is displaying the feedback message. Thank you so much.

          Is there a way we can add functionality of displaying the match record (few fields only) along with feedback message?

        3. Certainly it is possible, but it will be a bit complex because you’ll need to restructure the plugin as a class so that it can store the information of which record was matched, then use that to generate the error message. The code I provided is really only meant to be a proof of concept, it will be up to you to add any specific functionality you need.

        4. Ok. Thank you Roland!. Commendable support.

  2. Hello Roland
    This looks like it’s exactly what I’ve been looking for. When I tested the ‘match settings’ on the sign up form it did not display the validation error message as I expected but went ahead and created the record anyway. Is it because it does not work with pdb_signup_member_payment?
    Patrick

    1. To test your code, first clear the error log, then run your test. Check the error log right after that for information about how it worked.

Leave a Reply

Your email address will not be published. Required fields are marked *

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.