How To Delete Duplicate Records Without

[M_k]

Hi Airtable!

I have a base that I created in which I will be adding Notes from Apple.

There are duplicate records, but there is no key, except for a formula to create different record ID’s. This ID is unique for all records, including duplicates.

My question is how do I create a unique key, when each of the fields are not unique in my base.

Fields:

DATE *these dates are not all different

SUBJECT *some are the same

NOTES *the titles are either too varied or the same

TIME CREATED *some of the same records will have different times

FORMULA *creates a different record ID, even if it’s a duplicate

Perhaps the formula can be adjusted to create the same ID for all duplicates, based on another field?

If so, I don’t quite know how to do that.

I have created a share link to my base, I thought it might be easier.

I would appreciate any help.

Thank you,

Mary Kay

P.S. For some reason my share link looks like this. If you go to the last record, there’s a link (View larger version) to view the whole base.

I set up this link correctly and it views fine when I select preview in my base, so I am not sure what’s going on.

[kuovonne]

Is this a one-time dedupe, or an onging dedupe?

Create a new formula field expressly for the purposes of detecting duplicates. In the formula field, concatenate enough other fields to try to uniquely identify records. Then run the dedupe block on the new formula field.

For example:

DATE & SUBJECT

or

DATE & SUBJECT & NOTES

[ScottWorld]

Technically, if they’re running the Dedupe block, they don’t even need to create a formula, because the Dedupe block lets them choose multiple fields. Dedupe will only consider it a duplicate if ALL fields match across records.

@M_k You can learn more about the Dedupe block here.

[kuovonne]

ScottWorld, you’re right. You can tell the Dedupe block which fields to consider when looking for duplicates.

If you don’t have the Dedupe block, you can create the formula field and then group on the formula field. Collapse the groupings and look for groups that have more than one item in the group. But the Dedupe block is much nicer.

If this is an ongoing dedupe issue, you can also use a Scripting block to do this. I just wrote one for a client.

[M_k]

Hi @kuovonne and @ScottWorld

I am using the Free Plan and I was going to remove the duplicates through Integromat, so I was hoping that I could change the existing formula which creates unique ID’s or another formula to duplicate ID #’s for every duplicate record and using one of my fields in the formula. So that I know which records are duplicates.

@kuovonne can you explain a little more about creating the groups, I am not too familiar with this.

Would I use the same formula that you provided?

This will be an ongoing de-dupe process.

Thank you,
Mary

[ScottWorld]

kuovonne:

you’re right this time

“This time”? That’s kind of rude, isn’t it? I’ve been helping people nonstop here — 100% voluntarily — for the last few months, and I would say that 99% of my answers have not only been the correct answers, but have actually been extremely thorough answers as well.

[kuovonne]

@ScottWorld

My apologies. My reply was badly phrased. I have edited it. By “this time” I was referring to the fact that in this thread, you are right while I was wrong, whereas in other posts we simply had differing opinions.

You’ve definitely spent a lot of time helping people here.

[kuovonne]

M_k:

can you explain a little more about creating the groups
…
This will be an ongoing de-dupe process.

I was suggesting using the grouping feature in Airtable views to group on the new formula field. Then collapse the views and look to see which groups have 2 or more in them. It would be a pain, but with only 76 records for a one-time deduping for free, it would be doable. However, that won’t work on an ongoing basis.

I have another way of identifying duplicates that might work for you, but it will take a bit of time to write up.

[ScottWorld]

@kuovonne Aw, thank you so much for your kind apology & your explanation! :slightly_smiling_face: I absolutely accept your apology. Thanks for editing your reply too! :slightly_smiling_face:

Haha, that is true that we have sometimes given people different ways of solving the exact same problem! :stuck_out_tongue_winking_eye:

In my opinion, you have consistently given absolutely fantastic & top-notch advice to everyone in this community! :slightly_smiling_face: And you are a top-notch expert at scripting, too! (I know nothing about scripting, since I’m just beginning to learn JavaScript.) You have even reminded me of little things which I have forgotten about — such as the SWITCH function, which is so much easier than nested IF statements!

So, a big thank you for your MASSIVE CONTRIBUTIONS to this community! :slightly_smiling_face:

[kuovonne]

It’s late, and I don’t have time to do a full write-up now, but I thought I’d share this screen shot for now. Will this work for your Integromat integration?