So many people and offices are confused about when and when not to use the match boxes on e-form templates. Indeed, the match box can be a tricky concept on the surface, but a little thought, and trial and error will lead to a light-bulb moment.
The whole purpose of the match box is to tell Sunapsis that you want to insert a new row into a record, or update an already existing one to prevent duplicate entries.
Sunapsis documentation says this:
The “Match” column simply means that the field will be updated with the information that is pulled in from the template. If this is not checked, information from the template will be inserted into the record.
This is what the match column does, but HOW does one use it properly?
First, understand that without any match boxes checked, the system will insert or try to insert a new row of data. I say TRY because in some instances, Sunapsis does not allow additional rows of data. For example, in the address table there can only be one entry for each address type. If a US Residential Address already exists, and the template wants to add a new row, it will fail because it is not allowed to do so.
Enter the match box…
In this example, we need a way to tell the system to check for the address type, and if a row of that type already exists we want it to UPDATE the row, not try to insert a new one. In other words, address type is a key or unique field for that row.
The important concept here is key/unique field. I’ll define this as the field (or in some cases multiple fields) that should only exist once among all rows in the table for that individual. State, city, street number? You could have US Residential Address and SEVIS US Residential Address with those same values. In fact, you want that to be the case. But neither address type should exist more than once.
Thus, the key or unique field for the address table is the address type. There cannot be more than one record for a single address type. Because this is the unique field, we match on this field. From now on when the template runs, it will first check to see if a row matches that address type, and if one does, it will update that row rather than try to insert a new one.
Preventing Duplicates
In the case of tables that do allow multiple rows, we want to prevent a template from creating two rows with the same data. A good example is Passport Information. Passports are generally good for 10 years, so sometimes multiple applications are submitted within that time frame, all with the same passport information. What is the key field for passport information? Again, what field should not exist more than once when multiple rows exist? Passport number. No two passports have the same number, and so a match on this field means that if a passport with that number exists, the other fields will update (technically the other fields should be the same as what already exists but not necessarily). To reiterate, passport number is a singular, unique field that we know for certain should not exist for more than one passport.
Why not match on issue or expiration date? Perhaps someone happens to have passports from multiple countries that were issued and expire on the same days. A very remote possibility, but it could happen. It’s even more remote that two countries use the same numbering system and happen to each provide a passport to someone with the same exact number.
Matching On Multiple Fields
In some instances a row must be uniquely identified using multiple fields. Dependents for example do not have a single field that can 100% positively identify a single dependent. First names may be the same and twins will share a date of birth. What then makes the most sense to use as a unique combination of fields? I suggest First Name & Date of Birth. I can think of a situation where there may be two dependents with the same first and last name (Father/Son with different suffixes), or with the same last name and date of birth (twins), but I hope no one ever has twins and gives them the same first name!
Rules of Thumb for How to Choose What Field/Fields to Match On
- If the case is you cannot have more than one type (Address, Name Management), match on the type field.
- If you’re trying to prevent duplicate data, determine the field or fields that define a record entry as unique.
Hopefully this helps to shine light on the mysterious match boxes and you can now avoid the dreaded template failure or the task of deleting duplicate records.