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?
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:
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
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.
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).
ok, i was trying to make it using automations and i was happy, because it worked…but now i am sad 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 ?
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,‘ ’,‘ ’),
:purple_square: ) - last means ‘not scanned yet’
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,‘ ’,‘ ’),
:purple_square: ) - last means ‘not scanned yet’
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
ok, i was trying to make it using automations and i was happy, because it worked…but now i am sad 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 ?
that’s no problem. use list length
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…
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…
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 ?
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 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.
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.
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
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)
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
edit: the actually error when i have same EAN in table 1 and same in ean_scan in table two,
so…to be true, i started thinking in sql (maybe)
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
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…
no change after switch order of conditional acions (still unknown error) - if i run automation on choosen record manualy, no problem, everything ok…
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 ))
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 - 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
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 - 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
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 .(
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 - 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
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 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
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
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
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
Airtable is aware of this issue and this is how automations are designed, so it is unlikely to change anytime soon. If the trigger is that a field is not empty, that field is considered not empty at the first keystroke.
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…:(((
’,‘ 
’),
