Skip to content

Specify Forums

Personal tools
You are here: Home » Frequent User Questions » Editing or removing duplicate or variant collector (agent) names

Editing or removing duplicate or variant collector (agent) names

Document Actions
A “Violation of unique key constraint…duplicate key” error may occur when two variations of the same name were entered into the collector table for a single Collecting event, and during the process of standardizing the names using the "remove duplicates" function, Specify is unable to create the same name twice in the collectors for that CE.

The procedure to remove variant agents should be simple, but I have included background information in case it might be helpful. The same basic method can be used to remove variant spellings of locality information, or taxa that have been misspelled. This method is not intended to handle taxonomic synonyms.

Choose which variant you wish to use as the correct one. Make all the variant records of one agent match, either one at a time or using batch edit, then run the "removing duplicates" function. By editing to the preferred spelling directly in the agents table, all the associated collection objects are edited automatically. Removing duplicates links all associated records to a single instance of the correct name in the agents table. Removing duplicates works only for exact duplicates, so spelling, punctuation, and blank spaces must match exactly, and all fields must be identical. If some of the “duplicates” are not removed, look for spaces in fields and also make sure that fields that contain data have not been removed from the form at some stage.

Whether you choose to use batch edit or edit one record at a time depends on which table you’re accessing the records from and how many there are. When the data is converted into Specify, links are established between each collection record and the collector or collectors. Each spelling of Dr. Corner's name is probably linked to at least one collection record, and you can't delete the nonstandard entries until you change the collector for each related record. The hard way is to find and edit each related record, selecting the preferred spelling, then deleting the variant agent records when they are no longer linked to any related records.

You could also find agents via the collecting events table. If it is necessary to find the record that is causing the error, here is a method, To locate the specimen records with variant collectors names, open collecting events (under collection data), query under collectors/collector for person's last name LIKE Corner. This should get you all the Corner collecting events. You can include first name in your query to select the variant spellings if desired. From the data entry form, for each collection record you can choose "edit", double click on the collectors and edit within the collectors subform to the desired spelling. Also from the collecting events data form, you can access "related records" to see which collection objects are linked to each collecting event. Editing the collector's name in collecting events will also change the link from collection objects related to that event to the preferred spelling. Then you can delete the alternate spellings from the agents form.

Data may have been entered under these different spellings in your original database, before conversion to Specify. We encourage collections to standardize their data before it is converted to Specify, because it can actually be easier to catch variations in a grid or spreadsheet. If the variant spellings were entered within Specify, check to make sure each data entry person understands how to use the pull-down list to enter agents.

The agents lookup lists are meant to avoid this sort of problem, but if anyone has chosen one of the nonstandard entries since the conversion to Specify, then more links to the nonstandard entries will have been created. It is still possible for someone to create or recreate a different spelling if they don't look up or use the standard, but standardization is a more serious problem in fields that lack a look-up table. I strongly recommend that each collection create a data standardization document so that all the data entry people will know the preferred standards for your collection. Another way to encourage standardization is to edit the usage notes for each field, for the benefit of the data entry person, but you may have already done this.

Created by jean
Last modified 2009-03-03 01:13 PM

Powered by Plone

This site conforms to the following standards: