Using a products database is typically reserved for those with a need to do inventory management. If you are not doing inventory management you most likely will not need to use a products database. Use the quick-links below to navigate the various aspects of the products database features of Cart32.
Cart32 can integrate with virtually any database engine. There are two primary options: External Database or Internal Database.
- Internal Database: The internal database option will tell Cart32 to create tables within the master Cart32 database. These tables will be named something like CLIENTCODE_products. So no matter what DB platform you have chosen for Cart32 (Access, MySQL, MSSQL) you can store the product tables for each client within it. This option helps to consolidate all of the information in a single database. Using an internal database also means the fields are automatically mapped. More information on this is below in the “how to setup” section.
- External Database:The external database option will tell Cart32 to look for an MS Access database file or a System Data Source Name (herein called a DSN). To use an Access database you simple put the file path in the field for Access Database Name. If you want to use a DSN, simply have your Server Administrator (probably us if you host with us) set up a DSN to your database and then fill out the field for System DSN with the DSN name you/we created. This can be linked to any DB format you choose.
Currently, no. We do not have an integration engine that can tie to a spreadsheet or delimited text file (CSV, TSV, etc). There are free tools that exist for converting spreadsheets, CSVs, Access DBs, and other low-level information systems into more powerful formats like MySQL, MSSQL, Oracle, etc. A quick Google search should reveal several options.
Setup The Database
The first step is to set up the database link. This involves creating to opening a link to an existing database so that Cart32 can read the information inside of it.
- Create or make available your database. If it is MS Access upload it to your templates directory. If it is a real DB server such as MySQL, MSSQL, etc., set up the DSN. (If you are going to use an internal DB, skip this step)
- Log into your Cart32 Administration. Navigate to the Store and Pages > Classic Products > Setup Product Link tab.
- Change the Product Table Setup dropdown box to Use External Products Database. (If you are using an internal DB, select Use Built In Products Database). Click Save.
- Fill out the field for Access Database Name (if using MS Access) with the file path, or fill out the field for System DSN (if using a DSN). Those using an Internal DB will leave these fields blank.
- Click Save.
Map The Fields
With the database linked, we must now choose which table has the products in it and specify which fields to map together. For example, Cart32 uses “qty” for quantity. Therefore, if your products table does not have the quantity field named “qty” it will fail. So we map all of these.
(Note: If you are using an internal products database, you should not have to do these steps.)
- Open the dropdown box labeled Product Table. Select the tablename (or view) that has your product information in it. (Note, if you use a highly normalized table structure you will have to merge the data into a single table or view.)
- If the page does not save automatically, click the save button.
- You should now see a very long list of fields you can map columns to. Map each field to the corresponding database column. For example, if your table has a column called “cost” for the product cost, you would map the Price field to Cost in the dropdown. This must be done for each field you want to use until you reach the T# and P# fields (discussed below). Here is a list of the fields which MUST be mapped at a minimum for the products DB to work:
- ID: Unique identifier for each row
- Item: Field containing the item name
- Price: Field containing the price of the item
- QtyOnHand: Field containing the quantity of a given product available (only needed if using inventory management)
- PartNo: Field containing the part number for the item
- Weight: Field containing shipping weight (only needed if using a real-time shipper, most companies do)
- Now that all the core fields are mapped, we can map the option fields. Using a products database allows us to specify up to 20 options for each product. This is done through the T# (T1, T2, T3, …) and P# (P1, P2, P3, …) fields. You need to map as many fields as your maximum number of options for any single product. So, if you have mostly 2-3 options per products, but one of them has 7 options you’ll need to fill out P1-P7 and T1-T7. Follows these steps for each option field mapping:
- The T# field specifies the Type of option (text box, dropdown, checkbox, etc). You must have a column in your database table for this information to go to. If you do not have these fields, please add them.
- The P# field specifies the Parameters of the option. These are the options themselves. For example if your option was a dropdown labeled “Color” your parameter field will end up storing an array of color choices such as “red;blue;green”. Before this can happen, you must have these P# columns in your database so Cart32 can store information in them. You need to have a P# field for each T# field you want to use, and then you must map it like we did the other fields above.
- Click Save.
Adding Products Or Changing Quantities
After you have set up your database link and mapped the proper fields, you will need to add products. If you’re using an existing products database you should see your products already listed (on the Products –> List Products tab). However, if you are just setting up a new database to use, or if you have updates to do you will need to follow these steps to add or edit products:
- Log into your Cart32 Administration and go to the Products –> List Products tab.
- To add a product, click Add A Product. This will insert a new product with a partno of 000 and a name of NewProduct.
- Click edit on a product to modify the properties of the product. Here you can set up the price, qtyonhand, partno, etc. You can also set up the options here. These options work the same way as the HTML Form code does for Cart32. So, if you have a dropdown with a color option for Red;Blue;Green, you would have the following in your T/P fields:
- T#: d-Color
- P#: Red;Blue;Green
- Click save on the popup screen when you are done making your changes.
How Do I use Inventory Management?
If you have set up your products database within Cart32 you can enable some very basic inventory management as well. To make these changes log into your Cart32 Administration and go to the Products –> Setup Product Link tab. From there you will have the following options:
- Use Inventory Management: Mark this checkbox to enable the basic inventory management feature. This will decrement the QtyOnHand field for each item ordered when an order is made on your site. It will prevent overselling as well.
- Decrement Inventory on CC Approval Only: Mark this checkbox to require orders to be approved (using real time transactions) before decrementing the QtyOnHand field for an item when an order is placed on your site.
- Allow Negative Qty: Mark this checkbox to enable customer to purchase an item even if the QtyOnHand is 0 or less.
Does This Affect My Form Code?
Using a products database only affects your form code in good ways. The main difference is that Cart32 will ignore all but the Qty, Partno, and option fields in your HTML forms when you use a products database. Everything else (price, weight, tax, etc.) will be pulled out of the database. This can make setting up HTML forms faster.
If you already have HTML forms on your site for products you do NOT have to change them if you start using a products database. Cart32 will simply ignore some of the fields (as mentioned in the paragraph above), but they certainly don’t hurt anything. Here is an example product form code for someone using a products database:
<form method=”post” action=”https://www.cart32.com/cgi-bin/cart32.exe/test-additem”>
<input type=”hidden” name=”partno” value=”abc123″ />
<input type=”text” name=”qty” value=”1″ />
<input type=”submit” value=”Buy Me” />