Skip to main content

checking duplicates

M
Forum|alt.badge.img+11

Hi Airtable Community, 

 

The problem is to check in Airtable whether a value in a column (e.g. column 2) occurs **for the first time** or whether it is a duplicate. If the value is the **first occurrence**, a value from another column (e.g. column 4) should be displayed in another column (e.g. column 3). However, if the value is a duplicate (i.e. it already occurs in column 2 in an earlier row), column 4 should remain empty.

Happy for any ideas to solve this 🙂 

 

Thanks & best

Mirko

3 replies

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6001 replies
  • January 7, 2025

Formula fields in Airtable cannot reference data in other records, so you cannot do this with only a formula field.

If values in {Column 2} do not change, you could have an automation that does a "Find records" action based on the {Column 2} value, and then if a duplicate is found, have an "Update Record" action that copies over the value you want.

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo
Hamlin_Krewson2
Forum|alt.badge.img+6

@kuovonne is right in that Formula fields, on their own, cannot reference data from other records. Airtable does, however, give us the Rollup field which can utilize most (if not all) of the same options a standard formula field can. It isn't straightforward, though. You still need a relationship that captures all of the records you want to test. Once you have that, it's a complex algorithm to test for duplicates. 

For instance, I have an assets base of computers. Each computer gets linked to an asset type (to define the model). In the asset type table, I have a lookup of the serial#s from the linked records. This gives me a list of all serial numbers. 

Then in my assets table, I can build a rollup formula to test with an output of (0|1|2) to show if the serial exists, if it's unique, or if it's duplicated. The basic algorithm for this test is:

 

If serial# is empty, value = 0 else If we find a second copy of serial# then true, value = 2 else If we find a copy of serial (sanity check), then true, value = 1

 

 The formula for my need then looks like:

 

IF( NOT( {Serial #} ), VALUE('0'), IF(#If we find a second copy of serial FIND( {Serial #}, ARRAYJOIN(values, ";")&'', FIND( {Serial #}, ARRAYJOIN(values, ";")&'' ) + LEN( {Serial #} ) + 1 ), VALUE('2'), IF( FIND( {Serial #}, ARRAYJOIN(values, ";")&''), VALUE('1') ) ) )

 

 

So, for your example.

  • TableA
    • Column2 contains the data to dedupe
    • Column4 contains data to display on first occurance in column3
    • Column3 is a Rollup field using the aggregation formula shown below.
    • Column5 a link to another table (TableB), all rows in TableA link to the record "DeDupe"
  • TableB
    • Contains one record "DeDupe"
    • Column2 is the linked column from TableA
    • Column3 is a lookup set to look up the data from TableA::Column2

 

IF( NOT( {Column2} ), '', IF( FIND( {Column2}, ARRAYJOIN(values, ";")&'', FIND( {Column2}, ARRAYJOIN(values, ";")&'' ) + LEN( {Column2} ) + 1 ), '', IF( FIND( {Column2}, ARRAYJOIN(values, ";")&''), {Column3} ) ) )

 

 

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6001 replies
  • January 20, 2025
Hamlin_Krewson2 wrote:

@kuovonne is right in that Formula fields, on their own, cannot reference data from other records. Airtable does, however, give us the Rollup field which can utilize most (if not all) of the same options a standard formula field can. It isn't straightforward, though. You still need a relationship that captures all of the records you want to test. Once you have that, it's a complex algorithm to test for duplicates. 

For instance, I have an assets base of computers. Each computer gets linked to an asset type (to define the model). In the asset type table, I have a lookup of the serial#s from the linked records. This gives me a list of all serial numbers. 

Then in my assets table, I can build a rollup formula to test with an output of (0|1|2) to show if the serial exists, if it's unique, or if it's duplicated. The basic algorithm for this test is:

 

If serial# is empty, value = 0 else If we find a second copy of serial# then true, value = 2 else If we find a copy of serial (sanity check), then true, value = 1

 

 The formula for my need then looks like:

 

IF( NOT( {Serial #} ), VALUE('0'), IF(#If we find a second copy of serial FIND( {Serial #}, ARRAYJOIN(values, ";")&'', FIND( {Serial #}, ARRAYJOIN(values, ";")&'' ) + LEN( {Serial #} ) + 1 ), VALUE('2'), IF( FIND( {Serial #}, ARRAYJOIN(values, ";")&''), VALUE('1') ) ) )

 

 

So, for your example.

  • TableA
    • Column2 contains the data to dedupe
    • Column4 contains data to display on first occurance in column3
    • Column3 is a Rollup field using the aggregation formula shown below.
    • Column5 a link to another table (TableB), all rows in TableA link to the record "DeDupe"
  • TableB
    • Contains one record "DeDupe"
    • Column2 is the linked column from TableA
    • Column3 is a lookup set to look up the data from TableA::Column2

 

IF( NOT( {Column2} ), '', IF( FIND( {Column2}, ARRAYJOIN(values, ";")&'', FIND( {Column2}, ARRAYJOIN(values, ";")&'' ) + LEN( {Column2} ) + 1 ), '', IF( FIND( {Column2}, ARRAYJOIN(values, ";")&''), {Column3} ) ) )

 

 


Yes, rollup fields can reference values in linked records. However, the link needs to be created first, which will often need an automation in order to manage the links. If you have a large number of records, the back-and-forth rollups can also bog down the performance of the base.

So, using rollups to detect duplicates can work, but is rarely my first choice. There are many ways to do things in Airtable, so use what makes the most sense to you.

Reply


Most helpful members this week

Terms & Conditions