Compare 2 fields in two tables (same base)

[Matej_Mudrik]

Hello here,
i am trying to find solution for simple action, but i cannot find it
so the situation:

table 1
product (unique)
EAN (= barcode)

table 2
barcode (from scanner)
result (checkbox)

what i wish to do:
scan with barcode, save the number to table 2 (=fill the value in barcode)
compare the value from table 2 with EAN in table 1
if the result is true, make checkbox on,
if false, leave the field not checked

so is there any way to do it with formula ?
Thanks for answer and forgive me mine english, and programming skills (noob forever :slightly_smiling_face:

[Alexey_Gusev]

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…

[Matej_Mudrik]

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 ?

[kuovonne]

Adam_TheTimeSavingCo:

When you scan, save the number to the linked field

This may not be possible, depending on how the barcodes are being scanned. For example, if you are using an Airtable form or the Airtable mobile app, you cannot scan to a linked record field.

Matej_Mudrik:

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”

Put the update action that checks the checkbox in a conditional action based on the results of the find, so the record is updated only if one or more records were found.

[Alexey_Gusev]

Matej_Mudrik:

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

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

[Matej_Mudrik]

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,

[Matej_Mudrik]

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

[Alexey_Gusev]

Could you please show your filter?

Also, you reminded me some last year activity, when I just meet Airtable and started to learn JS. If you ever met bases, large enough to be unreadable by schema app, you can spend some time with command line and “how to install node.js” and “how to build custom apps” (actually, not “build”, but “improve existing”). I’m not started to learn React yet, but find a place where adding just one line to ‘parse schema.js’ , received app, that shows only linked fields.
This is the same base, created NOT by me, I would rather say, how it should not be created ))

[Matej_Mudrik]

Alexey_Gusev:

Could you please show your filter?

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:

[Matej_Mudrik]

ofc if you ask for filter in action, its this:

if i make the test - it shows the right solution - found 0 records)
have tryed it with contains (i know its stupid), but it gives me still the same result
actually trying it with other column (if the ean found, write 1, if not write 0… and still same :((
that means if i run test it has everything ok…when i run automation as “on” always error :(… if i try it as “test automation”, choose the record and everything is ok .(

[Alexey_Gusev]

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.