Blog posts of '2015' 'November'

RSS
Option record's RateExpression supports SQL statement- Friday, November 27, 2015

In the Rate Expression of an Option (or OptionExit) type record, put in a SQL statement (or call a stored procedure).  It must return a table of Shipping Options - columns: Name VARCHAR, Description VARCHAR, Rate Decimal. 

For example, if you can set up Warehouses as locations for local Pickup:

SELECT w.Name,
       Address1
         + case when isnull(address2,'') = '' then '' else ' ' + Address2 end
         + ', ' + a.City + ', ' + s.Abbreviation + ' ' + ZipPostalCode  as Description,
       0.00 as Rate
  FROM Warehouse w
  JOIN Address a ON a.Id = w.AddressId
  JOIN StateProvince s on s.Id = a.StateProvinceId;

Type

Name

Expression

Rate Expression

Option

Warehouses for local pickup

true

SELECT w.Name, Address1 + case when isnull(address2,'') = '' then '' else ' ' + Address2 end + ', ' + a.City + ', ' + s.Abbreviation + ' ' + ZipPostalCode as Description, 0.00 as Rate FROM Warehouse w JOIN Address a ON a.Id = w.AddressId JOIN StateProvince s on s.Id = a.StateProvinceId;

The customer sees: 

3

Shipping method

  •  
    10 Main St, Farmingdale, NY 11735
  •  
  •  
    15 Park Ave, New York, NY 10021

Tags :  SQL
Comments (2)