Function to know if a row duplicate in another table

[Omar_El_Gharbi]

Hello everyone!
I’m new to Airtable and I still have trouble using formulas, here is what I’m seeking to do:
I would like to know if a row in a table let’s call it table1 repat itself in another table (table2).
To do so if possible I want to have a detector in table3 and two other fields linked to table1 and table2 so it would be automated.
I think the automation is too complex for me to do, so I would appreciate if someone has an idea on which formulas to use or even know if it’s possible to know if a row repeats itself in another table.
Thank you for your help!

[Kamille_Parks]

If there is a possibility that a duplicate record exists in different tables, its usually a sign that [Table 1] and [Table 2] should really just be one table.

Can you explain your base structure a little so I can recommend a course of action? (Again, I will probably say just combine your two tables.)

[Omar_El_Gharbi]

Yes, sure you right I forgot to mention the table structure.
So we are trying to use airtable for accounting and forecast of operations.
Therefore in table1, we have actual operations from our bank statement.
Then in table2, I enter future possible operations, let say the for the next month.
Finally, I want to know if the forecast operation actually happened or no.
I was thinking of duplicate because I can’t think of a formula allowing us to do so.
Thank you for replying and have a good day!

[Kamille_Parks]

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:

Airtable

Duplicate Check - Airtable

Explore the "Duplicate Check" base on Airtable.

[Omar_El_Gharbi]

Thank you so much for your help!! I look forward to implementing your suggestion !

[JAN]

Hello, sorry to ask my question here, i find the problem very close to mine :slightly_smiling_face:

I am building a match making app for jobs (offers and requests).
I have two tables : first for job offers and second for profils
in both tables i use many fields but 4 have same options (multi select).
I would like to match those two tables based on the 4th fields, if all have same options then start an automation like emailing both sides to inform them that new item is matching.

Can this trick work for me you think ?

[R2G_Connect]

Hey @JAN ,
I’m doing something around matching as well and was wondering if and how you got this to work?
Would really appreciate any feedback or advice.

[Alexey_Gusev]

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

[R2G_Connect]

Alexey_Gusev:

SUBSTITUTE(CONCATENATE({MS_Field}),‘, ‘,’+’)

Hey @Alexey_Gusev thanks so much for your advice. I’m trying to replicate but not getting the same result for the SUBSTITUTE(CONCATENATE) formula. Could you create a formula that joins various multiselect fields pls?
Also any input as to how the automation should be set up would be helpful!

[Alexey_Gusev]

Hi,

fields like lookups and multi-select are arrays. function CONCATENATE converts them to several comma-separated values. SUBSTITUTE changes commas to any other symbol (+ is this case). For several fields, do the same CONCATENATE(field1, field2…) and then choose right SUBSTITUTE type.
Formula not working because forum changes brackets from straight to left-right, I should have pasted it this way:
SUBSTITUTE(CONCATENATE({MS_Field}),', ','+').

For automation, choose right trigger (‘when record created’ or ‘updated’ or both, according to your workflow).