How to use Table Shipping

The TableShippingADO component is designed to retrieve a single currency value from a database table based on a query that you specify, and to write this currency value to order.[_shipping_total]. To use this component, perform the following steps:

• υDetermine the basis of the calculation. For example, the basis might be the total weight of all the items in the order, and might also depend on the shipping method selected by the customer.
• υCreate a table in the site database that stores the shipping cost as a function of the basis.
• υCreate a query designed to retrieve this value to the site’s QueryMap Dictionary. If this query uses a product characteristic such as the sum total of the weight of the items in the order form, you may also need to modify the query that you use in the Product Information stage to ensure that this product information is present in the order form.
• υConfigure the TableShippingADO component’s property page to reference this query.

The sections that follow illustrate how to perform these steps. In this example, we will modify the custom site generated by the Site Builder Wizard so that it uses a table to determine shipping cost based on total weight and on shipping method.

Adding a Weight Attribute to the Product Table

In this example, the weight of each product is used to calculate the shipping cost, For this reason, weight must be specified for every item in the store. The following procedure describes how to add a column called weight to the product table.

To add a database column using SQL Enterprise Manager

1. On the Start menu, point to Programs, then Microsoft SQL Server, then click SQL Enterprise Manager.
2. Expand the nodes for the server, then Databases, then the name of the database containing the Commerce Server site databases, then Objects, then Tables. Double-click the product table for the site. (In this example, the short name of the site is healthy, so the table is called healthy_product.)
3. In the Manage Tables dialog, add a new column by entering weight in the Column Name field on the last row. For Datatype, select Float. Close the dialog and, when prompted to apply changes, click Yes.

Next, add the weight column to the query that is used by the QueryProdInfoADO component in the site’s Plan pipeline. In this example, the name of this query is product_info, and is defined in Global.asa.

To add the weight attribute to the product query

1. Using Windows NT Explorer, open the site’s Global.asa file using an editor such as Notepad.
2. Locate the script where product_info is added to the query map. It begins with the following commands:

   Set MSCSQueryMap.product_info = AddQuery("SELECT 
   

3. Add pf.weight to the list of columns returned by the SELECT statement. This ensures that the product weight is included in the item list for each product in the order form.

Creating the Shipping Cost Table

Add a shipping cost table to the database. The shipping cost table in this example uses the sum of the weight of each of the items in the items list, as well as the shipping method, as the basis of the shipping cost. The weight of each item is expressed in pounds. The shipping cost is expressed as an integer, in the smallest units of the currency (in this example, pennies). The following SQL script generates such a table.

CREATE TABLE dbo.healthy_shipping_costs (
    shipping_method varchar (20) NOT NULL ,
    min_weight float NOT NULL ,
    max_weight float NOT NULL ,
    cost int NOT NULL 
PRIMARY KEY (shipping_method,min_weight,max_weight)
)
GO
truncate table healthy_shipping_costs
go
insert into healthy_shipping_costs values ('shipping_method_1', 0.0, 2.0, 450)
go
insert into healthy_shipping_costs values ('shipping_method_1', 2.0, 3.0, 540)
go
insert into healthy_shipping_costs values ('shipping_method_1', 3.0, 4.0, 630)
go
insert into healthy_shipping_costs values ('shipping_method_1', 4.0, 5.0, 720)
go
insert into healthy_shipping_costs values ('shipping_method_1', 5.0, 6.0, 810)
go
insert into healthy_shipping_costs values ('shipping_method_1', 6.0, 7.0, 900)
go
insert into healthy_shipping_costs values ('shipping_method_1', 7.0, 8.0, 990)
go
insert into healthy_shipping_costs values ('shipping_method_1', 8.0, 9.0, 1080)
go
insert into healthy_shipping_costs values ('shipping_method_1', 9.0, 15.0, 1400)
go
insert into healthy_shipping_costs values ('shipping_method_1', 15.0, 100.0, 2000)
go
insert into healthy_shipping_costs values ('shipping_method_2', 0.0, 2.0, 300)
go
insert into healthy_shipping_costs values ('shipping_method_2', 2.0, 3.0, 390)
go
insert into healthy_shipping_costs values ('shipping_method_2', 3.0, 4.0, 480)
go
insert into healthy_shipping_costs values ('shipping_method_2', 4.0, 5.0, 570)
go
insert into healthy_shipping_costs values ('shipping_method_2', 5.0, 6.0, 660)
go
insert into healthy_shipping_costs values ('shipping_method_2', 6.0, 7.0, 750)
go
insert into healthy_shipping_costs values ('shipping_method_2', 7.0, 8.0, 840)
go
insert into healthy_shipping_costs values ('shipping_method_2', 8.0, 9.0, 930)
go
insert into healthy_shipping_costs values ('shipping_method_2', 9.0, 15.0, 1000)
go
insert into healthy_shipping_costs values ('shipping_method_2', 15.0, 100.0, 1200)
go

After creating a table like this one, add a query to the site’s QueryMap Dictionary. This query should be designed to take as parameters all the values that are used as the basis. In this example, these parameters are the weight of all the items in the order along with the shipping method. The query should be included among the queries initialized in your site’s Global.asa file, as shown in the following script:

Set MSCSQueryMap.ship_cost = AddQuery("SELECT cost FROM healthy_shipping_costs WHERE min_weight <= ? AND max_weight > ? AND shipping_method = ?")

Because the site’s QueryMap Dictionary is usually included in the pipe context that is passed to the OPP, the TableShippingADO component can be configured to reference this query.

Enabling Selection of Shipping Method

When you create a custom site using the Site Builder Wizard, the wizard’s Shipping and Handling page enables you to specify up to three shipping method names and costs. By default, two are defined. They are labeled Overnight and 2nd Day.

When the customer clicks Purchase on the basket page, the Shipping.asp page is displayed. This page enables the customer to select the desired shipping method by using a drop-down menu containing the shipping methods defined in the Site Builder Wizard. This page posts the customer’s selection as either the text shipping_method_1 or the text shipping_method_2.

When the form is posted, the receiving page (Xt_orderform_prepare.asp) adds the shipping_method name/value pair to the order form, with its value depending on the which shipping method the customer selected from the form on the page.

Configuring the Component

To configure the TableShippingADO component to use the query that you added to the site’s QueryMap described earlier, use the Pipeline Editor to insert the TableShippingADO component into the Plan pipeline. This component should be inserted in the Shipping stage, and should appear after the DefaultShipping component.

Next, double-click the component in the Pipeline Editor to display its property pages. When you are finished configuring this page, it should look like this:

The Apply When setting identifies the circumstances under which the value returned by the query is written to the _shipping_cost name/value pair. Here, the setting is Has any value, which means that the query is executed only when the order form contains a shipping_method value. This setting enables the Plan pipeline to be run in the Basket.asp page, before the customer has selected a shipping method and before the _shipping_cost name/value pair has been added to the order form. (If this field were set to Always, this component would fail on the Basket page because the query could not run successfully.) When the Plan pipeline is run again on the Payment.asp page, the shipping_method name/value pair now appears in the order form, and the ship_cost query is run.

In the Query box, type the name of the query to be executed. This example uses ship_cost, the name of the Dictionary entry that was added to the QueryMap Dictionary.

In the Parameter List box, type the values to pass to the query. The first two values must be the same (the weight of the total order), because the weight is used as the first two parameters in the query. The third parameter is the shipping method. To implement this example, enter the following parameter list:

sumq._product_weight  sumq._product_weight  order.shipping_method

In the Column box, type the name of the column that contains the shipping cost. In this example, this setting equates to the column cost that was defined as part of the shipping table. If no value is entered in this column, the component defaults to the value cost. In addition, if you specify a query that returns multiple rows of data, all rows after the first row are ignored.

When configuring the TableShippingADO component, keep the following in mind.

The TableShippingADO component should always be inserted after the DefaultShipping component in the Pipeline Editor. The DefaultShipping component ensures the integrity of the OrderForm’s _shipping_cost name/value pair by setting it to an initial value of zero (0). If you insert the TableShippingADO component, or any other shipping component, before the DefaultShipping component, the _shipping_cost name/value pair will be overwritten.

If your query references an item in the item Dictionary, you should make sure that the query that you execute in the Product Information stage is designed to retrieve that value from the product database. For example, if the TableShippingADO component references _product_weight as a parameter to the component’s query, then your product table should contain a column named weight, and your QueryProdInfo or QueryProdInfoADO component should execute a query that retrieves this column to storage in the item Dictionary.