SerialMagic How To: Low cost inventory counting solution

SerialMagic facilitates an inventory counting and data collection solution with a barcode scanner, and Open Office Calc (or Excel)

More ways to use SerialMagic

This document outlines how a LaserChamp barcode scanner, SerialMagic Professional software, and OpenOffice.org Calc, are used to create a lightweight inventory solution on a Windows machine.
The first step is to create a Calc spreadsheet with two sheets.  If you do not have OpenOffice.org’s toolset, you can download them for free from www.openoffice.org

This example uses the sheet you can download from here. Feel free to modify it for your own use. (If you want to use Excel for this project, the Excel formatted version is here - note how much more efficient OpenOffice calc format is - the identical sheet in Excel is 9.2 times larger than OpenOffice). The first sheet will have columns for item, quantity, and UPC code or other barcode.  Other columns can be added as desired; for example date and time. The second sheet will be the UPC database, and contain the product lookup information for the barcodes. One column will contain the barcode (in this case the UPC code); the other column will contain the corresponding item name.

The Calc “VLOOKUP” function is used to provide the item description on one sheet by linking to the database on the second sheet. The screenshots below show the two Calc spreadsheet views. Note: cells used to store the UPC codes (on both sheet views) must be formatted for “Text,” otherwise Calc will modify the codes, and the lookup will fail to match

The next step of the solution is setting up SerialMagic Professional to accept scans from the LaserChamp Bluetooth barcode scanner. When SerialMagic and the scanner are connected, SerialMagic will transmit scanned data into the application with cursor focus. In this example, the SerialMagic Action Profile (SMAP) will be configured to do 'Actions' each time a barcode is scanned. First, SerialMagic will display a prompt dialog to “Enter Quantity.”  After the user enters the information, SerialMagic will post the quantity in the Quantity column of the spreadsheet, move to the UPC column, and enter the UPC code. The item column will then be populated based the “VLOOKUP” result, so that when a match occurs for the scan from the database, the item description will be displayed for that scan. Finally, SerialMagic will automatically move the cell focus to the next row in the spreadsheet.

With the Calc spreadsheet, and SerialMagic Action Profile of Inventory Count set up, now the system can be used. Start SerialMagic and verify connection to the scanner. Launch the Calc spreadsheet used for your inventory counting / data collection. Place the cursor in the first field marked “N/A.” Scan the bar code on one of the products. The Quantity Prompt will appear. Enter the quantity of the item in stock and the Item, Quantity, and UPC columns should populate with the appropriate data. The cursor will appear in the next row, ready for the next UPC to be scanned.