How to prevent duplicate entries
Is there a way to prevent duplicate entries to my database? So when I try to enter the same information into a field it will alert me if that information already exists in another record.
Under Field Options click Validation and check the Uniqu Value box.
My own question: How to do this for several fields? I have a table of addresses and want to prevent dublicates being registered. Can one check for dublicates based on several fields? Preferable a script could run in the background and notify the user only when necessary.
Henning - you could create a calculation field that concantonates the fields you want to check and make it a unique value.
This I have tried, but it triggers even if there is no dublicate records.
Validation: Unique value
Validate by calculation -> Specify -> Field=Name&Surname&Postcode
Maybe this is wrong? How about a script that triggers when these fields are entered?
Multible good solutions must exist or a built in function since this is a common problem?
Don't put this as "validate by calculation". Click the auto-enter tab, click the by calculation option and then enter your expression there.
One caveat when it comes to uniqueness checks using the unique values validations or when using ! to perform a find for duplicate values in a field, if there are any returns in your field, such as a list of values, the uniqueness check is applied to each value--not the value of the field as a whole.
Thus if you have "Apple¶Orange" in a text field, the unique values validation will kick in if any other record stores "Apple" or "Orange", not just if they have both such values. Performing a find using ! will likewise find this record if another record stores "Orange" or "Apple".
I imagine you could write a script that captures those 3 fields as variables, performs a find using those variables and shows a dialog if found count is not equal to 1 or show all records if it does = 1. you could trigger it on record commit to let the user fill in all the fields before it runs or trigger it on modify for the last field that gets entered.
Retrieving data ...