Barcodes scanned into a webform not converting to a number automatically in the sheet!?

Campus&Co. Illawarra

Hoping someone can help with this promptly as I haven't got an answer from my support ticket from the 19th. We have a grocery store and the floor team scan the barcode of the stock coming into the store into a form and select the qty and expiry date. This goes to a landing page which has a formula in several columns to look up the barcode in a product list and return the name and category etc. Once the #NO MATCH has gone and the barcode is matched to a product the row moves into a main register. However just in the last week or so the barcodes haven't been automatically converting to number format and the apostrophe that's usually hidden isn't hiding so I have to go into the sheet and back space the apostrophe and re-enter it for every submission so the look up formulas work!! 🤯

Why would this be and is it a glitch that can be fixed promptly?

Thanks.

Jeff Reisman

https://community.smartsheet.com/discussion/90407/barcodes-scanned-into-a-webform-not-converting-to-a-number-automatically-in-the-sheet

Some things to check:

Has there been a change to the type of barcodes?

What's the condition of the scanner lens? If it's dirty that can cause misreads.

If it's not one of those things, you can make a change to your sheet. Create a new column to scan your barcode values into, and in the original barcode value column, use the VALUE function against the new column. Any numerical values showing up as text (the leading apostrophe) will be converted to numeric value.

Campus&Co. Illawarra

@Jeff Reisman Thanks for your reply. No, no change to the barcode type, still regular grocery barcodes as they have been for the last 12mths.

Mostly just android devices that are being use, phones or tablet and it happens on numerous devices so wouldn't be a scanner related issue.

I'm not really looking for a workaround as it has been functioning fine for 12mths and suddenly changed overnight.

On further investigation, I've worked out that when scanning directly into the sheet on the app, using the in app scanner the numbers format correctly. However when scanning into a webform, using the in app scanner and submitting it to the sheet via the form it shows differently in the app to on the computer.. see images...

Screenshot_20220426-172712_Smartsheet.jpg

20220426_172723.jpg

On the computer it shows 2 apostrophes (note it is 2 apostrophes, not talking marks) so to correct it so the look up works I remove one of the apostrophes. On the app it shows only one apostrophe which to correct I have to backspace it and re-enter it. I don't even have to save it after removing it, literally backspace and put it back in.

Appreciate if someone in the dev team can confirm its a bug and fix it asap!! MT!

Eric Dinius

I too have been encountering a lot of barcode scans coming in with multiple apostrophes, which the VALUE function will not filter out. This is causing a lot of manual editing. I agree with the Please Fix ASAP.

Thanks!

Campus&Co. Illawarra

@Shaine Greenwood Sorry to rope you into this one but I noticed you first announced the in app barcode scanning on a post a few years ago so hoping you can help us here. There doesn't seem to be any response from my support ticket that I've followed up nor does there seem to be any live support options from smartsheet to report potential bugs, if that's what it is causing this issue? Would really appreciate your prompt and helpful response to this. We can have upto 300-400 items scanned in a week and we don't employee anyone to spend time doing the jobs the programs we pay for should do!!

Jeff Reisman

@Campus&Co. Illawarra Sorry I'm just now getting back to you, I missed the notification on your earlier comments. We may still find a workaround that works while waiting for bug resolution.

Since VALUE won't help due to the two apostrophes, what about using SUBSTITUTE in your INDEX/MATCH formula to get rid of the apostrophes altogether?

=INDEX({Name Lookup Sheet Name Range}, MATCH(SUBSTITUTE(SKU@row, "'", ""), {Name Lookup Sheet SKU range}, 0))

The syntax is SUBSTITUTE( text to look in, text to find, replacement text, [number of instances to replace - optional] ). So in the above, for text to find I have a single apostrophe inside two quotes, and then just two quotes to replace it with nothing. The result is a plain text value of the number. If you want that to be a true numeric value, just wrap the SUBSTITUTE(SKU@row, "'", "") in a VALUE function: VALUE(SUBSTITUTE(SKU@row, "'", "")). The SUBSTITUTE formula as written gets rid of all apostrophes in the SKU@row, no matter how many or where they are.

A. Aron

I am having the same issue. I tried the SUBSTITUTE method, wrapped in the value, but now how do I handle leading zeros being dropped. I understand the need for work arounds, but I have to agree with @Campus&Co. Illawarra, please fix the forms bar code scanner.

Jeff Reisman

@A. Aron

The VALUE function will strip leading zeros. Just use

=SUBSTITUTE([UPC # - Required]@row, "'", "")

Krystal Garcia

Was wondering if there was a fix to this bug over the year?

Campus&Co. Illawarra

@Krystal Garcia Yes I finally got help through the support department and the implemented a fix!

lola.brooks113611

https://community.smartsheet.com/discussion/comment/406789#Comment_406789

Are you having this issue currently? I tried the fixes in this post and it still didn't allow me to lookup values in formulas. I even tried a helped column.

Krystal Garcia

@Campus&Co. Illawarra can you share what the fix was? I am getting ready to create/implement a similar process and want to be ahead of this bug

@lola.brooks113611 I am not yet, but was hoping there was an easy fix to follow step by step for it as I create this process

lola.brooks113611

https://community.smartsheet.com/discussion/comment/407529#Comment_407529

This thread had a few different fixes. The thing that worked for me was using value in a helper column.