Hi Fibery Community - I’m looking for help creating a simple script for my relational DB.
Basically, every time a new entity is created in my relational DB, I’d like the automation to scan the DB for duplicates. If it finds one, it should mark the newly created record as “duplicate”.
I know how to set this up with Make.com, but would prefer avoiding sending each newly created entity there first ;-D.
You don’t actually need a script, you can probably do it with an automation rule or with an autorelation and a formula.
If the new one should be marked as ‘duplicate’, should the original entity also be marked as having a copy?
Hi Chris - thanks for your feedback :).
No, the original one doesn’t need to be marked as having a copy.
Try this:
Create a one-to-many self-relation, and give the fields the names ‘Original’ and ‘Duplicates’
Define an automation that runs when an entity is created or updated (for the fields that are used to identify duplication).
This automation will update the Original field to whichever entity already exists with matching values.
Something like this:
The formula will be something like this:
Records.Filter(((([Public Id] != [Step 1 Record].[Public Id]) and (Email = [Step 1 Record].Email)) and (Number = [Step 1 Record].Number)) and IsEmpty(Original)).Sort([Creation Date]).First()
It queries all records to find any with matching values (excluding the one being matched).
Note: The IsEmpty(Original) part is there to catch the situation where multiple matching records are created at around the same time - it ensures only one record will be chosen as the ‘original’.
Now, any entity that has a value in the Original field is a duplicate.
If it’s useful, you could also have additional automations that run when the Original field is updated (and is not-empty) e.g. to merge data from fields that are not taken into account when looking for matches, and/or have an action to delete the duplicate.
Thanks Chris!
This sounds like it will work, but a bit more complex than anticipated 
Can you share a screenshot / example of what you mean by this part? Not quite sure I understand.
Create a one-to-many self-relation, and give the fields the names ‘Original’ and ‘Duplicates’
If you create a self-relation as follows:
you will end up with two fields:
you can then rename the second one:
Thanks Chris! That worked perfectly