Warehouse Automation (I) - Create A Low-cost Smart Bar-Code Scanning Solution

Purpose -
To implement a low-cost smart bar-code scanning solution for the warehouse inventory control, automatic data upload process through MS ActiveSync synchronization.

H/W (PDA with built-in Barcode scanner) - 

• Motorola(Symbol) PPT8800 or MC series, you can find PPT8800 under $200 at eBay, however, I used MC70 for this practice
• Networked PC with Windows XP, connect the PDA thru USB port
• Server hosting the database which keep all S/N information, I used a Linux server with MySQL for this practice. You can connect to any database depneds on your environment, even to those non-database applications like Quickbooks, Peachtree, Navision, SAP B1, Lotus Notes ... etc. As long as you set up the connector correctly (normally ODBC)
  

S/W - 

• TracerPlus Pro - barcode scan S/W ($215, trial version available)
• Tracer Desktop - layout editor (free)
• MS ActiveSync 4.5 - connect PDA and PC for synchronization (download from MS)
• GoodSync - since ActiveSync only sync data under My document folder we use GoodSync to sync scan data which is under program folder

Example file - download
This is a TracerPlus Desktop project file, it includes two scan forms, one for outgoing invoices S/N scanning, he other for receiving S/N scanning. Some checking mechanism have been added to avoid double scan or wrong formats.
Fields of Outgoing Invoice S/N Form :
INVNO: Invoice#
PN: P/N
SN: S/N
DATE: scanned date
SCANNER: scanned by

Fields of Receiving Orders S/N Form :
REFNO: PO#
PN: P/N
SN: S/N
DATE: received date
SCANNER: scanned by

Implement Data Synchronization and Database Connector -
set up the GoodSync to upload the scan data to file server everytime when the scanner is attached to the PC, this is a one-way sync








ODBC setting: the export files from TracerPlus we have predefined as CSV, therefore, we use the "MS Text Driver" for our ODBC System DSN, 

Once the ODBC ready, we need to decide how to connect to the target database to add the new S/N information. We should have a front-end process to filter the scan data make sure they are before we update the database.

I implemented three scheduled tasks by different tools which just wan to show you the basic concept to automate your inventory management -

• update MySQL S/N table by Using PHP and Cron jobs
  
• use Invoice# as key to update Quickbooks invoices and add the S/N information into Memo field by using QODBC, ASP and Windows scheduled tasks
  
• use Invoice# as key to update Lotus Notes S/N control database by using Lotus LEI (Lotus Enterprise Integrator)

On the following article, I will publish more details about how to implement these three data pumping tasks.