Prevent user from duplicating value

Question

Hello all,

I'm looking for a solution that would prevent a user from duplicating a value in a certain field if this value is already present in the field.  For example, if "PI2014313" is already present in this particular field, this value could not be entered in again in the same field.

I am aware of different tools that check for duplicates as well as field calculator and definition query scripts that can be used to check for duplicates, however I'm looking for a workflow that would automatically prevent duplicates from being entered in or makes the user aware that this value is a duplicate.

Any assistance would be greatly appreciated!  Thanks in advanced!

ShitalDhakal__GISP · Answer

Hi Craig,

Were you able to figure out the solution to this?

Also, how will you look for duplicate values using Field calculator?

Shital

CraigPrisland1 · Answer

Thanks for information Dan and Cam.

Cam - Your suggestion would work well but I am trying to perform this function on a feature class in a personal (MDB) geodatabase.  If I update this field in Access to disallow edits (in the Indexed column), when I try to open this layer in ArcMap and start editing, I get the following error: Unique index on non-OID field and cannot continue to edit this particular layer.

Cam_Barnard · Answer

Hi Craig,

One possible direction to explore is to use the native database technology.

What you are describing is usually referred to as a Unique Constraint. Unique Constraints and Check Constraints‌

Most databases support this. A particular table's primary key is always under a unique constraint and many databases allow other columns to be configured with this unique constraint property.

A few caveats from having used these in the past.

1. When trying to apply the unique constraint to an existing table column the operation will fail if the data isn't already unique (i.e. if two rows already have duplicate values)

2. This low-level enforcement of uniqueness isn't always gracefully handled by client applications. A users is just as likely to get DB write error as they are to get a meaningful error message.

Check the documentation of the database you are using for unique constraint and you should find instructions.

Cam