Find duplicates
How could I find duplicates in a table, querying a certain field (such as customer's name)?
6 replies
-
This is what Ive found, hope its what you are looking for:
Anwer from an support member in another post you could have found too by searching for "duplicates":
Alex
Thursday, October 11, 2018 5:09 PM
Hi, you can use a formula to identify duplicates.Here is an example:
You can insert a formula of the following form into the appropriate table (in the example Contact) in the field "Trigger after update" of the table properties:let myFirstName := 'First name';
let myLastName := 'Last name';
let cntFirstName := cnt((select YOUR TABLE)['First name' = myFirstName]);
let cntLastName := cnt((select YOUR TABLE)['Last name' = myLastName]);
if cntFirstName > 1 and cntLastName > 1 then
alert("Duplicate!");
'First name' := null;
'Last name' := null
endThe system checks whether the fields First name and Last name already contain the same combination of names in another data record.
If this is the case, a message "Duplicate!" appears and the fields are cleared again.
If you do not want to perform the check at table level (i.e. every time any field content is changed in the table), you can also insert the script at each of the individual fields in the same option ("Trigger after update") of the field properties. -
I did already search for duplicates, but unfortunately the search system does not allow exact matches, so produces way to many irrelevant search results.
This approach makes sense if a form is being used to add new records. But what if I already have a table, such as imported records, and I want to find the duplicates?
-
Oh ok, then I may misunderstood.
Iam not quite sure, but maybe this link helps you out?
In this linked post there is another link maybe leading to a solution for you.
-
Hi RG -
You can take what Kruna recommends and with a small change have the formula field say "duplicate" so you can then do a search on that word. For example you can change the if statement to:
if cntFirstName > 1 and cntLastName > 1 then
"Duplicate"
endThen you can add the formula field to your table view and see what records are considered duplicate and then figure out which ones you need to delete.
-
Finding duplicates on a single field is easy. Define a formula field, or a formula column in a tabular view, with something like:
let myLastName := 'Last name';
cnt((select Table1)['Last name' = myLastName]) > 1
The formula returns a boolean value, just filter on "yes".
Finding duplicates considering two or more fields is a bit more tricky. The formula presented in a previous post will not give the correct result. Consider, for example, a table with the four records:
Donald Duck
Mickey Mouse
Donald Mouse
Mickey DuckWhile there are no duplicates when considering both fields, the formula presented in a previous post will flag all records as duplicates. A better formula would be:
let myFirstName := 'First name';
let myLastName := 'Last name';
cnt((select Table1)['First name' = myFirstName and 'Last name' = myLastName]) > 1The "and" operation must happen inside the "select".
Warning: since the "select" operation is quite inefficient, building the filtered view on a large database could take a long time.
-
One field is all I need. This will work grea, thank you.
Content aside
- 2 yrs agoLast active
- 6Replies
- 723Views