Introduction:
I wanted to start by detailing a custom software project I have been working on for the past year or so for Vickerey.com in Boulder, CO. The package, which has come to be known as SkyBlue, works in conjunction with a software package called Stone Edge Technologies Order Manager. Stone Edge is used to import and manage orders from an online store. In the case of Vickerey, Yahoo!Store, which is the backbone of their website.
Aside from importing orders, Stone Edge does a multitude of things such as managing customers, payment processing, inventory, purchasing, receiving, picking / packing, shipping, returns and exchanges, and much more. According to their website it is used by over 2,500 retailers and is compatible with over 40 shopping cart systems.
SkyBlue is written and created in Microsoft Access with VBA, the same language that Stone Edge Order Manager is written in.
Purpose:
Stone Edge Order Manager has many functions and can do a lot for a business but like every software package, it is by no means perfect. This is why Vickerey decided to create SkyBlue. Vickerey had a desire to capture a large amount of data about their products to be used on their website. The key to Vickerey's success is constantly changing content and SkyBlue provides the tools to allow for these changes.
SkyBlue provides a robust product editor that works with Stone Edge by using Stone Edge data as well as providing its own tables to store additional information about inventory. The product editor features the ability to edit hundreds of attributes about a product from Headlines, captions, pricing, images and even quantity on hand.
SkyBlue also has the ability to update practically all information about a product in a Yahoo!Store, including images, completely changing the content of a website through the upload of .csv files that it outputs. This feature saves huge amounts of time for users of any Yahoo!Store.
Features Rundown:
Along with capturing additional inventory data and outputting .csv files, SkyBlue also provides:
Inventory Search / Product Editor / Publish Que - These functions will all be detailed in this section. The following will be detailed in Section 2.
Inventory Search / Product Editor / Publish Que - These functions will all be detailed in this section. The following will be detailed in Section 2.
Sales Information - Sales Information provided in a form that allows searches by Date, SKU, Supplier, Category and Season. The Sales Detail form provids a SKU Sales breakdown and totals for QOH Cost, Sold Price, Sold Cost, Gross Margin, Units Order and Units Shipped
Sales Tax Form - Displaying total sales and a complete breakdown of all State sales tax categories available for a searchable period of time. All tax rates are adjustable. The form also displays amounts paid in each category through orders and the calculated amount owed based the state tax table.
QOH Snapshot - Displays a detailed view of all current Quantity on Hand.
Stock Perfection - Alerts users as to which SKUs have come in-stock and have went out-of-stock
Yahoo Perfection - Downloads a current .csv of the available inventory listed on the website from the Yahoo!Store and compares Stone Edge inventory data to this import to determine discrepancies in pricing and QOH.
Functionality / Features:
Main menu / Startup Screen:
The tabs are available because Vickerey uses two company files in Stone Edge, one for Vickerey.com and another for moleskineus.com by which they sell paper products. SkyBlue allows seamless editing of the data in both companies. There is also an Admin tab with restricted access to Sales Tax Information, Sales Reports and the Publish Ques which output the .csv files.
From the main menu a user can click the Product Editor button which brings you to the initial product search screen:
Inventory Search:
As you can see, in the top section, there are many different ways to search for products, these fields correspond to both Stone Edge and SkyBlue data. Once a product is found, the user double clicks the product in the grid to bring them to the Product Editor screen.
Product Editor - General Tab:
This is the first tab for the product editor, fields are saved in both Stone Edge tables as well as SkyBlue tables seamlessly without the user knowing. The General Tab includes general product information that can be found on the website such as Headline, Caption, Style Pts, etc.
The Product Editor in SkyBlue works similar to a website where data is first loaded to the screen from the database, edited, and saved back to the database when the 'Save' button is hit. SkyBlue rarely uses "Bound" data throughout the program, which is a very efficient technique in preventing users from accidentally changing data that they do not intend to change.
Product Editor - Pricing Tab:
The Pricing Tab is used to store pricing information about a Parent part and all of its sub-components. Data is stored in the grid as a temporary table and saved back to Stone Edge when formatted the way the user intends.
Product Editor - Sizing Tab:
The sizing tab is used to edit a product's Sizing information. Users can create their own custom chart for a SKU or use a pre-defined Brand Size chart that is shared by multiple SKUs. I will skip a description of the Paper and Bath & Spa tabs.
Product Editor - Images Tab:
The images tab provides a visual display of all the images associated a SKU. Each thumbnail can be clicked and opened in Internet Explorer. Images are stored in a series of folders based on if they are approved to be uploaded to the website or not. Tasks can be assigned per image.
Product Editor - Yahoo Tab:
The Yahoo Tab is where a user can enter information about all the sub SKUs that are associated with a Parent SKU.
Product Editor - Tasks:
You will notice that every tab has a section for Tasks. Users can create a task with a Task Status which corresponds to each Tab in the SkyBlue Inventory Editor. Tasks are used to communicate which SKUs need to be updated on the website through the Publish Que via a .csv file. When a Task has a status of Approved, it will appear in the Publish Que and can be exported as a .csv.
Here is a detailed view of the Task Form:
Users can change status, delete and make notes about a particular task. Each Task is related to a Parent SKU. Tasks with a status of Approved will appear in the Publish Que awaiting a .csv upload.
Task Search:
You can also search for Tasks and open the SKU associated with the Task in the search Screen.
Publish Que:
The Publish Que is the final piece in the life cycle of an inventory part. Here an administrator can export a .csv file with all the updated data used to change the content on the website. The Publish Que only displays Tasks with a status of Approved allowing users to change content as much as they want until the content is ready by assigning an Approved Task to a part.
Here is an example of an exported .csv file from the Publish Que, opened in MS Excel:
This file can then be uploaded to Yahoo to directly change the content on the website.
Here is a product page after update:
Category Editor
SkyBlue also has the ability to edit entire Categories of items within this form:
Attributes from the Category Editor can be edited and used to update the website just like a product.
After editing a Category and uploading the .csv file to the Yahoo Store the new Category information will be updated on the website.
