The only reason for the calc field is when duplicate values have to be present in more than one field for the record to be considered a duplicate. The same method can be used with a single field, you just use that one field in place of the calculation field.
You may find that all you need is to:
- Enter find mode
- Put an ! in the field
- Perform the find
- Sort your records by this one field to group the duplicate values together.
- Delete the duplicates.
Once you've removed the duplicates, figure out what design change or validation rule you need to prevent the duplicates from being entered in the first place.
I'm not sure I understand what the ! means. For example, if there are two records with the same information does it find both records or is the found set only the duplicate record?
! is the find duplicate values operator. It will find any record where there is at least one other record with the same value in the same field. Try it and see what happens.
Ah, thanks :)
Ok, so what I'm seeing is it';s getting the earlier entries rather than the later ones. Is there a way to get the later duplicates instead?
It gets both, but you then need to sort your records so that the duplicates are grouped together.
Well, I'm really confused.
So I created the following script:
Show All Records
Sort Records [Restore, No Dialog] Sort by Notes::Notes in Ascending order, Notes::Set Number in Ascending order
Go to Record/Request/Page [First]
Replace Field Contents [No dialog, Notes::Mark, ""]
Go to Record/Request/Page [Next, Exit after last]
If [Notes::Global = Notes::Notes]
Set Field [Notes::Mark, "X"]
Set Field [Notes::Global, Notes::Notes]
Perform Find [Restore] Find records when Mark = X
In this script, I'm using the Notes field, which is really the only content, and a field I called Set Number which denotes the first, second, and so on set of notes at the time of import. This means for example, the first 50 notes are denoted as 1, the next 30 are denoted as 2, and so on. This way, I can also tell what set of notes any individual note belongs to.
When I run the script, it seems to find (i.e., Mark) the duplicates in the lower numbered sets first, and then the higher numbered sets. I've tried setting the sort order to both Notes Descending and Number Descending and finally to both, to no avail.
Can you tell me what I'm doing wrong?
What is the data type of your "Set Number" field? Make sure that it is of type Number or you won't be able to sort correctly on it.
Then specify this sort order:
Notes (ascending)// any order will do here
Set Number (ascending)
You would want ascending order here, not descending so the script will marke the 2nd and subsequent duplicates, leaving the lowest numberd field unmarked.
Yes, Set Number is a number.
That looks like it did it. Thanks again!