The Business case for Integration
One of the most frequent inquiries we get from customers is what can be done to reduce our cost in managing our website? The business will have back end systems (QuickBooks, MAS90, Fishbowl, etc.) that they use for their operations that require data (orders, shipping, products) to be synchronized with the website. Typically they have a designated person who is responsible for the website sites content and products and a couple of customer service reps who are responsible for orders and interacting with customers. Initially, the amount of time maintaining the website may be small, but as orders increase and the need to keep content relevant increase, they get squeezed for time. That’s when we get questions on what can be done to reduce the time spent maintaining the site and the amount of double data entry being done. The answer is to integrate ASPDotNetStorefront with the back end systems.
Options for Integration
There are several vendors that provide off the shelf ASPDotNetStorefront integration packages for popular back end systems. One of the most popular back end system’s we get requests to integrate with is QuickBooks. For this scenario, we typically recommend Atandra’s T-Hub Advanced (http://www.atandra.com/ecommerce-quickbooks-integration-with-thub-overview.php). It integrates product management, order management and shipping information between QuickBooks and ASPDotNetStorefront. We then assist in the setup, configuration and training. Setup typically consists of installing files on the website, installing the T-Hub client on the Business’s PC’s, connecting it to Quickbooks, and then setting up a periodic batch job on the Business PC to synchronize QuickBooks and ASPDotNetStorefront. This works for this scenario, but there are issues that can preclude this solution. The first and foremost one is there are only a few back end systems that are supported with off the shelf solutions. Second, custom business requirements may preclude the use of off the shelf packages, ass the packages may not support the option required. When using an off the shelf package, you are using the rules they wrote to and expect, deviating from them may cause the system to fail or corrupt data between the two system. Corrupted data is not fun. Third, it ties your version of ASPDotNetStorefront to the version of the package. If a new version of ASPDotNetStorefront is released, you may not be able to upgrade until the package has a version that supports the new release. Fourth, cost can be an issue. These packages will require an initial payment and may require a recurring licensing fees, this may be prohibitive for an organization. Additionally they may require upgrade fees to support new versions of ASPDotNetStorefront.
An alternative to an off-shelf integration solution, is write a custom integration. This can be done by hiring a company like Beacon Technologies, or if you have the knowledge and resources by yourself. One of the most frequently requested integration’s is synchronizing the inventory from the back end system to the website, especially if the business has multiple sales channels (Brick and Mortar Store(s), Phone/Catalog Sales, Websales). If a back end system can export an inventory list, than a simple program can be written to import the data into ASPDotNetStorefront. With the following steps, you can create a simple page (Inventory_Uploader) to read the inventory from a CSV file and update your ASPDotNetStorefront database.
Programming a simple Inventory Upload Page
ASPDotNetStorefront saves inventory data in two different tables depending on how a product is configured. If it is a general product inventory is stored in the “ProductVariant” table. If the product is configures to support inventory tracking by Color/Size feature on the product manager, then the data is stored in the “Inventory” table. Four this example, we will not be using the “Track Inventory By Size and Color” feature.
Open your Web site in Microsoft Visual Studio:
After the website is loaded, In the Solution Tab, find your Admin folder and right click, select “Add New Item”.
Select the “Visual C#” template and Web Form. Enter “Inventory_UpLoader.aspx” and save. In the Solution window, find the new page and open in the editor.
We’ll need to edit this file to support the ASPDotNetStorefront Namespace. Modify the inherits attribute to:
Inherits = “AspDotNetStorefrontAdmin.Admin_Inventory_Uploader”
and add the MasterPageFile attribute:
Because the file uses a master template, you need to remove the standard HTML Tags and replace them with a content place holder.
The inventory loader’s user interface will consist of a File Select Button, an Upload Button and then an area to report the results of the processing. Fo the results I want to know how many records were sent, the number processed correctly and then any errors encountered.
In the Solutions Window, find the Inventory_Uploader.aspx.cs code file and open it in the editor. We’ll need to add an ASPDotNetStorefront library and some additional Microsoft .Net libraries.
Add the ASPDotNetStorefront Namespace
Modify the Class definition to inherit from ASPDotNetStorefront Base Admin class instead of the standard page class.
Add the function to handle the “Upload” button click to the class.
When the button is clicked, we will want to get the name of the file and then upload it to the server for processing.
After uploading the file, the program will need to open the file and process the lines. For this example the file is a simple CSV file with two fields: SKU and Inventory.
After uploading the file to the server. The file will be opened and then each line will be processed.
As each line is read, the Product SKU and Inventory are parsed out.
After reading the SKU and inventory from the file we will need to determine which record to update in the database. Depending on the business rules, the logic to do this can be complicated, so I like to put this in its own function.
add the following function to the class:
The purpose of this function will be to map the SKU sent in the feed file to a variant record in database. To do this it is critical to know how your SKU’s are configured in the back end system. In fact prior to setting up products in ASPDotNetStorefront it is worth looking at whether you SKU model will support multiple variants for products or Color/Size Options for products. For this demo, the products are configured to support having multiple variants but not color/size options. The rules are pretty simple:
- The first part of the SKU is the main product SKU.
- If the product has variants, the variant suffix is separated by a hyphen.
If you wanted to support Color/Size inventory management, then rules would need to be defined for these options and the search logic modified.
Using the previously defined rules for the SKU, we can write the code to parse out the main product’s SKU and retrieve the variant suffix if applicable
After parsing the SKU data, we can search ASPDotNetStorefront for the variant records in the database. This SQL assumes that the variant suffix will be unique. If that case was not true, than we would need additional rules to find the unique record and adjust the code accordingly.
If a record is found, we can then update it with the inventory provided in the feed file.
To download the source files Inventory_Uploader.
The source is for version 220.127.116.11 of ASPDotNetStorefront, if using an earlier version of 9.X than modify line 18 in Inventory_Uploader.aspx.cs to “string dbConn = WebConfigurationManager.AppSettings[“DBConn”];”