Function to know if a row duplicate in another table
0
3761
10
Okay. Make a [Table 3]. This third table will have only one record, which will in turn link to every record in [Table 1] and every record in [Table 2]. Common practice here is to make the value of the primary field in [Table 3] a period, but you can name the record anything you want.
Go ahead and link all the records to the [Table 3] record. To make sure all future records are linked to the record, filter your views to where {Table 3} = '.', and hide that field from view.
You will probably need to make a “match key” field in both [Table 1] and [Table 2] which combines all relevant data into one field which Airtable can use to compare records to each other. Make a formula field in both tables with a formula like this: {Field 1}&':'&{Field 2}&':'&{Field 3}&':'&{Field 4}.
Add a lookup field to [Table 3] which returns the match key in [Table 1], and another that looks at the match key in [Table 2]
Then, assuming [Table 2] is your forecast table, make a lookup which reports the match key lookup field of [Table 1] records in [Table 3]. Finally, make a formula field with this formula or something similar: IF(FIND({Match Key},{All Table 1 Match Keys}),'Duplicate','Not a Duplicate')
Here’s a sample base for reference:
Duplicate Check - Airtable
Explore the "Duplicate Check" base on Airtable.
There are many ways to do such check. For example, to check existing matches in multiselect, create additional field in both tables with formula
SUBSTITUTE(CONCATENATE({MS_Field}),‘, ‘,’+’), create empty table3, create (in tables 1 and 2) linked field to this table and copy-paste formula field in it.
so, you can see matches like that.
to be able to see matches in realtime, you need to setup automate checking new or updated t1 records for matches in t2 and vice versa
