# Script Request: Mark duplicates in relational DB

**URL:** <https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182>\
**Category:** Get Help\
**Created:** [August 18, 2022, 9:09am UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182 "2022-08-18T09:09:36Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Maximilian\_Breckbill](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/maximilian_breckbill/32/5080_2.png) [@Maximilian\_Breckbill](https://community.fibery.io/u/Maximilian_Breckbill)\
**Post date:** [August 18, 2022, 9:09am UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/1 "2022-08-18T09:09:36Z")

</div>

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](http://Make.com), but would prefer avoiding sending each newly created entity there first ;-D.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 18, 2022, 10:20am UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/2 "2022-08-18T10:20:03Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Maximilian\_Breckbill](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/maximilian_breckbill/32/5080_2.png) [@Maximilian\_Breckbill](https://community.fibery.io/u/Maximilian_Breckbill)\
**Post date:** [August 18, 2022, 11:18am UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/3 "2022-08-18T11:18:54Z")

</div>

Hi Chris - thanks for your feedback :).

No, the original one doesn’t need to be marked as having a copy.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 18, 2022, 11:40am UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/4 "2022-08-18T11:40:27Z")

</div>

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:

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/a/a6c768a29806c8d17dcdcf5856fb8af7819214a3.png)

The formula will be something like this:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Maximilian\_Breckbill](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/maximilian_breckbill/32/5080_2.png) [@Maximilian\_Breckbill](https://community.fibery.io/u/Maximilian_Breckbill)\
**Post date:** [August 18, 2022, 12:40pm UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/5 "2022-08-18T12:40:44Z")

</div>

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’

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 18, 2022, 12:50pm UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/6 "2022-08-18T12:50:00Z")

</div>

If you create a self-relation as follows:  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/e/ecd6d0f50e243f0a3b97c30257dc38e36ada2562.png)

you will end up with two fields:  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/9301c2580189742d2b0b8ed5c5b907eb162b7e03.png)

you can then rename the second one:  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/9ce77b0ced11fabdd6b7450da781ab9e8b4d4643.png)

---

<div class="post-metadata">

**Author:** ![Maximilian\_Breckbill](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/maximilian_breckbill/32/5080_2.png) [@Maximilian\_Breckbill](https://community.fibery.io/u/Maximilian_Breckbill)\
**Post date:** [August 18, 2022, 12:58pm UTC](https://community.fibery.io/t/script-request-mark-duplicates-in-relational-db/3182/7 "2022-08-18T12:58:16Z")

</div>

Thanks Chris! That worked perfectly 🙂