In an attempt to make a "scan inventory" system in Airtable, I have been playing around with the following automation:
I have a base with all our products listed in "All products" and another linked table "Scanner" that links back to "All products"
The primary field "Scanned" is setup with this formula:
IF(Barcode, "OK", "Waiting")
So if the "Barcode" column is empty we show "Waiting", and if not we show "OK"
I then have the following automation setup:
So basically, whenever "Scan status" changes from "Waiting" to "OK", we want to update that record by inserting the "Barcode" value into the linked column "Link to All products".
This works everytime I test it inside the Automations tab, but when I try testing it by scanning some real life barcodes, it only works for some records:
I have of course verified that all the records actually do exist in the "All products" table.
The error I am getting in the automation history tab is:
And so my question is basically, why does it sometimes work and sometimes not? What do I need to change regarding my permissions to make it work for all records?
All columns in both tables are set to the following:
On a sidenote.. You might ask, why not just scan directly into "Link to All products"?
And the answer is, that I cannot get my barcode scanner to make the correct escape sequence which would be "[barcode] + ENTER + SHIFT+ENTER" which would automatically select the found record, and add a new line in the table.
Without having direct access to the base it's hard to say for sure, but I strongly suspect that you're simply not giving the automations time to run to completion. Airtable's automations are, in my opinion, somewhat fragile. If you have an automation that, say, responds to a change in field and responds by updating value in some other field and record, *that automation's execution may be aborted if you click into the trigger field of a second record before the automation is completed for the first record*.
Diagnosing this should be easy: Trigger the automation (change Scan Status to "OK" in some record) *and then wait and don't even touch your computer*. If it seems to be taking a long time, be patient and wait. I mean, you don't have to wait for half an hour, but give it 30 secs or even a minute. Does it run to completion? Rinse and repeat several times. When you wait, does it always run to completion? If so, then my suggestion is probably right.
I have some automations that have this problem and I've simply advised users to wait until they see that the automation has been completed.
Any chance you're getting hit by invisible characters / trailing whitespace either from your barcode scanner's input or in your original `Products` table?
I sometimes get that error when the primary field of the table I'm linking to is a formula field and the value I'm attempting to link to doesn't exist. You mentioned you already verified that all the records do exist in the "All products" table, so I was wondering if invisible characters might be the issue here instead
I believe that the permissions issue is due to the fact that you are linking to a synced table. Because the table is a synced table, the automation cannot create a new record with the given primary field. This supports Adam's idea that there might be some hidden characters for the affected barcodes in one of the two tables.