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:

[Andrew_Davies]

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?

[Matej_Mudrik]

hello @Andrew_Davies, i have done it with automation (didnot found any better solution), and its functioal - but i want to know, if there is any way to do it using formula :winking_face:

[TheTimeSavingCo]

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

[Alexey_Gusev]

Hi,
there are some ways to achieve “something close”, but I don’t clearly
understand your goal. Formula field and checkbox fields are different. Of course , formula can itself represent checkbox.
For example, You can make EAN primary in T.1, mark all present products by checkbox and add it to table 2 as lookup. New links will be without checkbox.
You can also, instead of “move ean to primary”, do it by calculated(formula) field,

That will protect the table from creating new records by linking nonnexistence values.

[Matej_Mudrik]

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).

[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” 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 ?

[Alexey_Gusev]

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’

[Matej_Mudrik]

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

[Alexey_Gusev]

that’s no problem. use list length