Generated column to COUNT occurrences in table - possible?

[Rob_Simpson]

Hi there everyone!

I’ve got a case where data rows are added by multiple sources - and some duplicates are added.
It’s not efficient to check for an item’s existence before adding a new row as the duplicates are very infrequent.

I’m looking to periodically check and remove duplicates. The current solution I have is to create a generated column which counts the repetitions of the value in a particular field.

Basic example:
My_ID column may contain -
110
111
112
111
113
114

This generated column should show 1 for all except ‘111’ which would show two - there are two occurrences.

I can then use a couple of quick calls to find and remove duplicates.

Assuming this is possible, I can’t find the syntax to do so in a generated column. Can anyone help point me in the right direction please?

(or if there’s a solution I’m missing, please do let me know!)

Thanks!
Rob

[Nazar_Dmytryshyn]

Hello @Rob_Simpson,

If I understand you correctly, this cannot be done with the help of Generated Column.
You can easily find duplicates using the Rest Console:

Regards
Nazar

[Rob_Simpson]

Excellent - thanks so much @Nazar_Dmytryshyn - very helpful indeed.

I’ve replicated that and all working nicely.
I’m thinking of calling this, and deleting all duplicates (meaning there would be no instances or repetition) then recreating one record with the ID. This feels slightly inefficient, but I can’t think of a better way to do it.

Is there a way to delete the 2nd occurrence and upwards, leaving just the first instance in one action?

[Nazar_Dmytryshyn]

@Rob_Simpson,

You can remove duplicates using ‘Rest Console’. To do this, you need to specify the ‘objectId’ you received after the GET request in the ‘Request Body’, and execute DELETE request:

Regards
Nazar