How to manage inventory?

cKy_96 · 01/19/22

Hi,

We have a storage room with inventory that's organized with barcodes for each item type. We are trying to have a barcode scanner that's connected to a workstation and have it interact with the smartsheet that has the record of our inventory.

The idea is to have a barcode that links to the smartsheet

Once we scan the barcode thats linked to the smartsheet we would then scan the barcode for the inventory item. When the barcode for the item is scanned we want the cell to be selected in smartsheet.

Then once that cell is selected we wanted to be able to change the quantity amount by scanning a barcode.

Is it possible to have a barcode that highlights the quantity amount cell for the item we selected and then we scan a barcode to check out a certain amount or check in a certain amount.

Jeff Reisman · 01/21/22

The good news is that Smartsheet can accept input from a bar code scanner. The bad news is that it's difficult to get Smartsheet to change to other cells without manually intervention. I don't believe there's a way to scan a barcode and have the system find the row that matches that bar code, and bring you to the quantity cell for that row.

I think the closest you may come to what you want is to use an entry form to collect your data on one sheet, and use INDEX type formulas on another sheet with a list of item numbers, in order to pull the newest quantity for each item. For your entry sheet, you'll want Item Number, Quantity, the system-generated Created date, a text/number type "Date" column. Use the following as a column formula to extract the date-only portion of the Created date:

=DATEONLY(Created@row) +''

(The plus and two single quotes at the end converts the extracted date value into text.)

We're also going to create a unique combination of item number and the date which gives each row a unique identifier, which we we can recreate in the main sheet in order to match up quantity values. Create a "UniqueID" column in the entry sheet, and use this column formula:

=[Item Number]@row +"-"+ Date@row

This creates something like 123AQ562-1/21/22 for example, where 123AQ562 is your item number.

On your entry form, scan the item number bar code into a cell, scan the quantity bar code into the next, submit. The dates and UniqueID will populate in the background

On your sheet with your main item list, list the individual unique item numbers, and use a formula like this in a helper text/number type "Date" column:

=MAX(COLLECT({Entry Form Date}, {Entry Form Item Number}, [Item Number]@row)

This should pull the the most recent Date for the latest entry for that Item Number from the entry sheet.

Use the same UniqueID formula as above to create the "UniqueID" on your main sheet.

In your Quantity column, use an INDEX/MATCH to pull the latest quantity for from the entry sheet:

=INDEX({Entry Form Quantity}, MATCH(UniqueID@row, {Entry Form UniqueID}, 0))

The end result is that each time you scan in your items and their inventory in to the entry sheet, your main inventory sheet will update the quantity to the latest value.

Note: When scanning a barcode with a text value into a text field in Smartsheet, if the bar code value is all caps, and the Caps Lock button is on or Shift key is pressed, the value will record as lower case. This is because to Smartsheet, the scanner is just seen as a way to enter values as if from a keyboard.

Jeff Reisman · 01/21/22