Looking for some help with barcode functionality

Paul Osenenko

I see Smartsheet can use barcode scanning in the mobile app, but I can't find any good information about how to use it. What I would like to do is have a setup where the person at the end of our assembly line can scan a BC on packages to enter them as complete. Our packing labels have a code on them that consists of the PO#, the order number, and item number (1 through however many items are in that order). Below is a screenshot of my build schedule report. Ideally if the operator could scan a code with let’s say 1044127,26049,8 and the system would match to the row containing PO# 1044127, order# containing 26049 and then index the cabinets complete column by 1. The item number 8 in this example would be irrelevant except I would like to be able to reject duplicate scans, as each one is unique with a different item number. Is this possible with Smartsheet, and does anyone know how to go about accomplishing this?

Thanks,

Paul

Kelly Moore

Hey @Paul Osenenko

Sorry for the lengthy post but you asked multiple questions so I tried to answer them in order. Because of the length, it might appear the solution is hard. There will be multiple steps but you should be able to proceed in a straight forward way. Please don't hesitate to ask questions at any point.

In terms of how you could use barcodes, if your scanning device is able to access the smartsheet mobile app, then the barcode can be scanned into a field. You would need to have a field for this - either a new column or one commandeered from the manual process you are converting from. In the mobile app you click on the field in the sheet and you have the option of scanning into it. For the sake of this example, I'll call this field Barcode- it's a Text/Number field. Once the data is in the field, we can interact with the data the way we would interact with any data in a sheet.

In your example above where your ideal barcode would be 1044127,26049,8 then, in terms of data, this is just a text string. By joining the relevant columns existing in your row into a text string, one could compare the barcode against the data already in the sheet. To do this, we need a helper column so that your PO # and Order # could be joined together to make this comparison. You can name the column whatever you like. For clarity, I'll call it Joined Helper.

*In the formulas below, change the column names in the formulas to match the actual column names in your sheet.

Joined Helper column = [PO Number]@row+","+[Order #]@row

This forms a string that should exactly match the format of the barcode data up to your comma duplicate scan characters. (If your ideal barcode isn't the exact format, let me know and I'll update all the formulas.)

Then, in your Completed Checkbox column

=IFERROR(IF(MATCH(LEFT(Barcode@row, FIND("~", SUBSTITUTE(Barcode@row, CHAR(44), "~", 2)) - 1), [Joined Helper]:[Joined Helper], 0) > 0, 1),"")

This looks at the relevant characters of the barcode field and sees if there are any matches in the [Joined Helper] column. If there is, the value would be greater than zero so it checks the box (that's the 1). If there isn't, there would be an error, however the IFERROR tells the cell to stay blank. The CHAR(44) is the HTML/ASCII code for a comma. I find it's easier to use that code rather than "," as it gets confusing within a formula.

Your completed checkbox is now checked.

It will take one or two helper columns to keep count of where you are in the order, depending on if you want to see the builds increment by one, or only keep track of the total number. (For example, 1 then 2, then 3 - or just all showing the same current total, ex. 3,3,3). To keep track of them one by one, you will need the system generated autonumber column [Row ID]. This approach assumes you are not sliding rows around on your sheet - if you are, this won't work as written. I'm assuming you want to see the one by one counts.

=COUNTIFS([Joined Helper]:[Joined Helper], [Joined Helper]@row, [Completed Checkbox]:[Completed Checkbox], 1, [Row ID]:[Row ID], <=[Row ID]@row)

This says to count the Joined Helper cells that match your current Joined helper row, where the checkbox is checked and the Row ID is equal to or less than your current row.

Help me understand the difference between rows 1 and 2. Would these have a different PO #s? This might be problematic if the Joined helper text strings are identical.

Would this approach work for you?

Kelly