Compare 2 fields in two tables (same base)

Hi @Matej_Mudrik and welcome to the forum!

Some people more experienced than me may have a better solution but I would approach this with an automation.

In one of your tables, make an automation that searches for records based on the barcode - maybe triggered when a new record is created?

You can then set the automation to check the check box.

It would of course rely on the barcodes being in exactly the same format etc

Hope that helps?

Hmm, a possible workaround that is very fragile and I do not recommend:

Table 1 setup:

• Primary Key is the EAN
• Linked field to Table 2
• Count field that counts the number of records linked to Table 2

Table 2 setup:

• Linked field to Table 1
• Lookup field to get the number of records linked
• Formula field to check whether the number of records linked is 1

When you scan, save the number to the linked field

If a record with the barcode value exists in Table 1, it will be linked to it.
If no record with the barcode value exists in Table 1, then one will be created.

In Table 2, create a formula field that checks whether 1 or more records are linked to the barcode in Table 1.

If only 1 record is linked, that means that the record in Table 1 was just created, i.e. the barcode did not exist in Table 1 until you just scanned it. As such, the result is “Not checked”

If more than 1 record is linked, that means the record in Table 1 existed before you scanned the barcode. As such, the result is “Checked”.

I can’t think of a way to move the value of a formula field into a checkbox field though, sorry

I’ve created it here. To view the formulas, duplicate the base by clicking the title of the base at the top of the screen and then clicking the three horizontal dots on the right

this seams to be working, but i think its really very fragile as you write, but thanks for it
@Alexey_Gusev i dont have it as primary in T1… i have it in item description table…
mine goal is to set "some identificator - doesnot matter if it is number, checkbox or anything else) - the wish is "to get filtered items, in this table, which doesnot find ean in other table)
that means “i have to do something with it” (photos etc) or look whats wrong with it (ean change, bad ean code, bad invoicing etc).

this seams to be working, but i think its really very fragile as you write, but thanks for it
@Alexey_Gusev i dont have it as primary in T1… i have it in item description table…
mine goal is to set "some identificator - doesnot matter if it is number, checkbox or anything else) - the wish is "to get filtered items, in this table, which doesnot find ean in other table)
that means “i have to do something with it” (photos etc) or look whats wrong with it (ean change, bad ean code, bad invoicing etc).

I got it. I just was surprised “how do you want to make action (enable checkbox) by formula”. now it’s clear.
You can just add lookup of table1 EAN here, in table 2, and use classic
IF(code_from_scanner,
IF(code_from _scanner=EAN_lookup,‘ :white_check_mark: ’,‘ :no_entry: ’),
:purple_square: ) - last means ‘not scanned yet’

ok, i was trying to make it using automations and i was happy, because it worked…but now i am sad :frowning: because i use the find item action…and… it is not functional, when it found “zero” i mean when i dont find the ean in the other table…:(((
so i find something with script here, but i am big noob and i dont know how to use it…because it doesnot use the condition “when i found nothing” is the result i want… any idea how to get around it ?

Thanks, but it says “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.” have renamed the fields same as you did (i am lazy, just want to try).
The problem is (i think) that i dont get items from the ean from other table - it is lookup, but it doesnot find any items…so i cannot compare…

feel stupid, because this is normal thing (i think) and i cannot go over it

Well, it’s really not so hard. But at first weeks I had the same feeling…

regarding field names - yet more advice. if you have complex formula with brackets and spaces in names create need for more brackets, change them to x,y,z to make formula smaller. when you turn them again, formula will update.

Sorry, i forget some important thing to mention. Lookups (and other ‘array types’, like multiselect) often cause problems in formula. You see nothing in EAN_lookup, but it’s empty array, different from usual null.
You should convert it to text by CONCATENATE( '' , EAN_lookup)

or
''&EAN_Lookup

And yes, empty Lookup field is another problem. You should link records in order to make it work. That’s why i wanted to clarify that part:

“…compare the value from table 2 with EAN in table 1
if the result is true, make checkbox on,…” . Because if you have no links, you should at first do lookup, or search, to define records link between 2 tables…

Hmm, a possible workaround that is very fragile and I do not recommend:

Table 1 setup:

• Primary Key is the EAN
• Linked field to Table 2
• Count field that counts the number of records linked to Table 2

Table 2 setup:

• Linked field to Table 1
• Lookup field to get the number of records linked
• Formula field to check whether the number of records linked is 1

When you scan, save the number to the linked field

If a record with the barcode value exists in Table 1, it will be linked to it.
If no record with the barcode value exists in Table 1, then one will be created.

In Table 2, create a formula field that checks whether 1 or more records are linked to the barcode in Table 1.

If only 1 record is linked, that means that the record in Table 1 was just created, i.e. the barcode did not exist in Table 1 until you just scanned it. As such, the result is “Not checked”

If more than 1 record is linked, that means the record in Table 1 existed before you scanned the barcode. As such, the result is “Checked”.

I can’t think of a way to move the value of a formula field into a checkbox field though, sorry

I’ve created it here. To view the formulas, duplicate the base by clicking the title of the base at the top of the screen and then clicking the three horizontal dots on the right

Can we look at the lookups - because i think, the problem is in mine head (and i dont give it up, because i know it :).
So…the situation
table 1:
primal key, many colums, last one is EAN
table 2:
primal key, some columns, ean-from-scanner
what i do is i add new columns
link to other record (table 1, not allow to multiple record, because i want in this case only 1, choose the field EAN in table 1)
it creates 2columns - the primal one from table 1 and the EAN from table 1
but both colums are look empty (and they are in fact)
i think thats because i am doing something wrong (maybe i only still think in excel way)
what i think that this is “because the table has no choose the value of the primal key” (or i didnot)
well thats because the EAN is not the primal key - so… should i make new table, which will have EAN as primal key ? but that will be stupid (i think) because it will bring the redundant data (if i copy the table), or am i thinking bad ?

Yes, you are right. You should not think Excel way, but think SQL way instead.

(No, you don’t need to create another table)

Table is a set of records (rows) with defined data type in each of fields (columns).
PK is not visible in UI (instead airtable using “record name”, which is the value of primary field), PK can seen by formula RECORD_ID(), but you don’t need it. When you trying to set in Table1 link to some record (or many records) from Table 2, the “natural” way is to use their record IDs.

Here, Record ID on top of picture is a SINGLE record ID to be updated (taken from trigger)

But the main point - to create link to Table_2, you should put ARRAY of record id_s (or List of record IDs) into link field.

• airtable will show you record name inside link field.
  And you can fill it by putting record name, instead of record ID, but airtable will search for ID (“under cover”), at it’s less reliable way, because record name (primary field value) can be not unique.

• value of link field is ALWAYS ARRAY of links. If you link single record, it’s array of 1 element. If not linked, it’s empty array. “Find records” output is ARRAY. That’s why you can’t use it sometimes, where you need single record ID (it’s possible by additional workarounds)

Here, just an example how you automation schema can looks like

To conclude, my airtable experience started from misunderstanding of important thing and it took more than month to acknowledge - airtable do not preserve constraints of “Link multiple records”(on/off) or “Limit record selection to a view” at a table level.
It’s just a helper for those who select linked record by manual way, to get filtered list of records more efficiently, and not display “+” if you choose “Link multiple records”=OFF and field already has one link.

You can still put 2 or more links there by Automation if your “Find Records” step find many records, and then perform manual investigation.
Can be done in following way:

and then use Formula on Count field, you can use IF construction, I prefer SWITCH when choices more than 2 (linter is lying , works ok), it’s up to you

so…to be true, i started thinking in sql (maybe) :slightly_smiling_face:

its not english, but its only for imagination, that i am trying to think in relationship

mine automation send some error (unknown error) but i dont know if there is no problem with the order of runnig actions - i am using record id (just because i want to know what exactly the test tests and what i use):

actually i did not add any more rows, because i get that unknown error, will try to make it like you (so 1st if its no t0, than if the length is 0 and will see…
the formula section i will try later, because mine head is today too tired and tomorow i get some audit at work, so i will be off. will try to change the action order and add new…and will see…
didnot use the switch formula, but have read about it and i like the idea - but currently using only some pretty basic if, so…i will learn it using later :slightly_smiling_face:
edit: the actually error when i have same EAN in table 1 and same in ean_scan in table two,

no change after switch order of conditional acions (still unknown error) - if i run automation on choosen record manualy, no problem, everything ok… :frowning:

the answer is : actually none in any table.

i dont think i have any problem with db scheme - mine mind is set to be analyzing and low level thinking :grinning_face_with_big_eyes: but i dont (and i cant) do programming - its for me not interesting (i know, i can do things, but i cannot force myself to do learn it - have tried with a lot of programming languages, but…no success :slightly_smiling_face: - thats only mine problem - i know it and i dont want to change it :))
this db, you showed me its nice - for me…for somebody will be like The Fall of the House of Usher in realtime :))) and what it really show its evolution - it has some informations at the beginning and…its growing…
Back to the filter question - i dont need any actually, but for users i will do different views ofc- i use filter only when i look for some info, which i need to find quickly, or something for report, but then i turn it off (same as i do in excel - but i am individual, and it doesnot mean that it is bad or good - its just different… still no luck with automation, so waiting untill it will be solved somehow…because without that i cannot move up. But i am trying other things i know i will have to use and need them :slightly_smiling_face:

the answer is : actually none in any table.

i dont think i have any problem with db scheme - mine mind is set to be analyzing and low level thinking :grinning_face_with_big_eyes: but i dont (and i cant) do programming - its for me not interesting (i know, i can do things, but i cannot force myself to do learn it - have tried with a lot of programming languages, but…no success :slightly_smiling_face: - thats only mine problem - i know it and i dont want to change it :))
this db, you showed me its nice - for me…for somebody will be like The Fall of the House of Usher in realtime :))) and what it really show its evolution - it has some informations at the beginning and…its growing…
Back to the filter question - i dont need any actually, but for users i will do different views ofc- i use filter only when i look for some info, which i need to find quickly, or something for report, but then i turn it off (same as i do in excel - but i am individual, and it doesnot mean that it is bad or good - its just different… still no luck with automation, so waiting untill it will be solved somehow…because without that i cannot move up. But i am trying other things i know i will have to use and need them :slightly_smiling_face:

I mean that filter

like that example

I think the problem is with types of data not compatible with each other. And I think you are put wrong thing into ‘Dynamic’ right part of equation (because I did the same until I struggled to do several attempts on testing base and finally understand how all those things must work)

About programming - you are right. if you don’t feel kind of enjoy of it - like some persons have constructing lego, or brush artists from their paintings etc. , you shouldn’t waste your time and efforts, instead doing things where you feel comfortable.

i dont think, that i give wrong thing into dynamic - it shows the value (as help), but look at it… i think i have found the problem, but will look at it tomorow - will test it and say write, what i have found (or what i have tryied and didnot work :slightly_smiling_face:

so… i have found solution for my error in automation.
Its not the solution, which says “this is bad”, but its the solution “go around” == i dont know whats wrong, but if i do it this way, it will be functional as i wish/need"
so the problem is in this step:

and the description is

which says “invalid filter on EAN”
its not invalid filter on EAN (maybe it is on server side), but the solution i found is in the conditions:

i noticed, that if i wrote somehing into ean-scanned, it beggins to run the automation even before i end up filling the field (= i have some numbers in this field, but not all actually) and it runs even when it is not filled (no enter added, still in “edit field”)

so the problem in fact is not in the ACTION, but in trigger

wrong one (gives good result ONLY when run as test== automation failed):

good one : (ofc better one, not really the best) == automation run in automode and gives the results i wish:

so the problem was in me (as i think at beginning)

i have send this to support at airtable, so i give them this as solution.

@Alexey_Gusev many thanks for your patience and time - was really helpfull for me
@all : sorry for mine english