Formatting of Numbers scanned in to webform via barcode scanner

J.H.W.

Hello

Wondering if someone can help with the below scenario:

I have a number of sheets with webforms that require team members to enter a SKU, some are used by the floor team entering expiry dates and they have the physical product in their hand and are able to scan the barcode. others are used by the purchasing team who may be copying the sku from the POS system into the webform, not actually scanning.

I then have a 'look up' sheet, which is an export of the product list from our POS system, that the other sheets with the webforms will look up the SKU in the 'look up' sheet and return extra details such as the name, category, supplier etc.

I have exported the product list as an excel file so it shows the barcodes as Numbers Stored as Text with the hidden apostrophe at the start as i found this was how the scanned barcode numbers were stored and the VLOOKUP will work. However it means that on all the webforms i've got to have a note, "If you are manually entering the SKU, please put a apostrophe at the start like '300630." My note is ready probably 50% of the time.. mostly i have to go back and fix the #NO MATCH errors by manually putting the apostrophe in and it subsequently delays the workflows.

Does anyone have any suggestions on how i can set it up so the VLOOKUP works regardless of whether the barcode was typed or scanned in?

Krissia B.

Hello @J.H.W.

When you scan a barcode for Smartsheet, it will add an apostrophe at the beginning of the value. This is to ensure for the values that starts with a zero, the zero will be retained and be displayed with the number. 

 

But since with your case, there is no issue with VLOOKUP working as long as there's an apostrophe included however its not showing up when users enter in the SKU manually rather than scanning.

An alternate solution so you dont have to include a note for users to add an apostrophe is by using a formula & a helper column to convert the SKU to add an apostrophe regardless. You can then use this helper column for your VLOOKUP.

 

I'm adding screenshots below of what this would look like as well as the formula im using.

 

Formula used: =IF(AND(SKU@row <> "", NOT(CONTAINS("'", SKU@row))), "'" + SKU@row) which basically reads IF SKU is not blank & does not contain an apostrophe, then add an apostrophe in the beginning of the SKU value.

 

More in the links below.

NOT Function

CONTAINS Function

Combine (Concatenate) Text or Values from Two or More Cells with a Simple Formula

Cheers,

Krissia