SerialMagic How To: Low cost mobile inventory counting solution for Windows Mobile

SerialMagic facilitates a mobile inventory counting and data collection solution with a barcode scanner, and Pocket Excel

More ways to use SerialMagic

Watch the movie on this topic. (12MB)

This document outlines how a LaserChamp barcode scanner, SerialMagic Professional software, and Pocket Excel, are used to create a mobile inventory solution on a Windows Mobile PDA or phone.  

The first step is to create an Excel spreadsheet with two sheets. This example uses the sheet you can download from here. Feel free to modify it for your own use. 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 Excel “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 Excel spreadsheet views. Note: cells used to store the UPC codes (on both sheet views) must be formatted for “Text,” otherwise Excel 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 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”, and will provide a numeric keypad with large buttons so that the user can enter the quantity without a stylus. After the user enters the information, SerialMagic will post the quantity in the Quantity column of the Pocket Excel 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.

The screenshots below outline how to configure the SerialMagic Action Profile for your inexpensive mobile inventory counting solution. Note: when entering the SerialMagic Action (SMA) for PROMPT-NUM, choose the "Show number pad" if you have a Windows Mobile device without a keypad, or just wish to use the touch screen to enter the quantity.

With the Excel spreadsheet, and SerialMagic Action Profile of Inventory Count set up, the tap-and-hold menu option “Set to active,” is used to make the Action Profile active. Now the system can be used. Start SerialMagic and verify connection to the Bluetooth scanner. Launch the Pocket Excel sheet 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, along with the numeric keypad. 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.