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:
