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

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

More ways to use SerialMagic

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 6 Smartphone.  

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 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.

Note: that the Excel spreadsheet must be in "Edit Mode" in order to scan items into it.

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.” After the user enters the information, SerialMagic will post the quantity in the Quantity column of the spreadsheet.

Then it will 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.

You can download the SerialMagic Action Profile and import the SMAP from SerialMagic Professional using the SMAP Import feature.
Right click here and choose "Save Link As..." or "Save Target As..." depending on your browser.

The screenshots below outline how to configure the SerialMagic Action Profile for your inexpensive mobile inventory counting solution.

With the Excel spreadsheet, and SerialMagic Action Profile of Inventory Count set up, the “Set to active,” Smartphone menu option 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 Excel sheet used for your inventory counting / data collection. Place the cursor in the first field (may be 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.