Technical References - formulas.db

The formulas.db provides a secure way to manipulate pricing and shipping values

When using [addlineitem] (and only [addlineitem] - formulas.db is not consulted when using [setlineitem]) to add products to the shopping cart, WebDNA gets the monetary values in one of two ways: either from like-named fields in the product database, or from a calculation in the formulas.db. To prevent hacking, WebDNA doesn't allow these values to come in from a form submission without a password; but you can still customize pricing, shipping, and tax by creating a formula to calculate a different values based on available variables, such as [username], [state], or any form, text or math variable that is active on that page. For instance, a coupon code could apply a discount, tax could depend on state, or free shipping given if the order's total is above a certain minimum.

The formulas.db contains two fields -- name and formula -- followed by your records:

name tab formula 
price tab (your custom formula here) return
unitShipCost tab (your custom formula here) return
etc.

Every time a product is added to the shopping cart, WebDNA calculates the item's price, taxable, and unitShipCost as follows:

Look for a file called formulas.db in the same folder as the shopping cart template itself, and look for a formula called "price." If found, then evaluate the WebDNA expression using current lineitem values as well as any other variables available. Set the price of the product based on the calculated formula.

If no formula or formulas.db is found, simply use the "price" field from the product database corresponding to the item's SKU.

The same process applies to "unitShipCost" and "taxable" for line items.

The same process applies to the header fields "taxRate" and "shipCost", except that if no formula is found, a form variable can be used.

variableDescription
pricelookup SKU in database, or calculate from formula
(applied only to the lineItem being added)
unitShipCostlookup SKU in database, or calculate from formula
(applied only to the lineItem being added)
taxablelookup SKU in database, or calculate from formula. T or F
(applied only to the lineItem being added)
taxRateCalculate from formula or use variable (applied to entire order file).
shipCostCalculate from formula or use variable (applied to entire order file).


Example to apply a preferred member discount:

price tab [math][lookup db=catalog.db&lookInField=sku&value=[sku]&returnField=price][showif [getcookie status]=preferredmember]-{0.15*[lookup db=catalog.db&lookInField=sku&value=[sku]&returnField=price]}[/showif][/math]


This example looks up the price, then if the user is a preferred member, it looks up the price again to calculate 15%, and subtracts that figure.

formulas.db can be simple fixed values as well. For example, some Shipping Cost Strategies could be as follows:

If your charges for shipping are......then the formulas.db would contain:
$6.95 + $2.00 per additional itemshipCost tab 4.95
unitShipCost tab 2.00
(When there is 1 item in the cart, the shipTotal will be 4.95+2.00 = 6.95. Each additional will add $2.)
$15.00 flatshipCost tab 15.00
unitShipCost tab 0.00
$9.95 base charge + each item has its own handling charge (often based on weight)shipCost tab 9.95
"UnitShipCost" field in your product database contains shipping cost for each item.
Omit unitShipCost formula from formulas.db, or make it a simple lookup.
$15.00 flat in the state of NJ, $35.00 everywhere elseshipCost tab [ShowIf [ShipToState]=NJ]15.00[/ShowIf][HideIf [ShipToState]=NJ]35.00[/HideIf]
unitShipCost tab 0.00
15% of the subtotalshipCost tab [math][subTotal]*.15[/math]
unitShipCost tab 0.00


Note that for the header fields taxRate and shipCost, values can come from variables. It is often easier and more efficient to define a variable for these, or even hard-code them into your template.

Note that there are ways around using the formulas.db altogether if your style of coding resists stashing these things within a database. By using a password variable (see [addlineitem]), you can manipulate the price, unitshipcost and taxable values right in your template as well. This allows you to set values in a local preferences file instead of using the formulas.db (or even hard-code values in place). Or maybe your product database doesn't even have fields for taxable or unitshipcost. For instance, if taxstate and USUnitShipCost are part of a list of custom text variables in an [include] file called pref.inc and you use [include pref.inc] at the top of all your website pages:

[showif [country]=us]
[setlineitem file=ShoppingCarts/[cart]&db=[db]&index=[lineindex]]password=(your password here)&UnitShipCost=[USUnitShipCost][showif [ShipToState]=[taxstate]]&taxable=T[/showif][showif [ShipToState]![taxstate]]&taxable=F[/showif][/setlineitem]
[/showif]


... this will set your shipping and taxable values for US customers (use [hideif [country]=US] to display the code for everyone else). Or use [if] [then] [else] for specific code for US, Canada, and everyone else.

Using the password technique also allows the administrator to enter ad hoc values (adjust shipping, throw in freebies, etc.) in shopping carts before running them through the final credit card process.

Terry Wilson

DOWNLOAD WEBDNA NOW!

Top Articles:

AWS Raw WebDNA LAMP-Plus WebServer

Amazon Web Services (AWS) README for Machine Image ID...

WebDNA Modules

A list of the currently available modules...

[biotype]

BioType is a behavioral biometrics WebDNA function based on ADGS research and development (from version 8...

WebDNA Libraries

A list of available libraries for WebDNA...

Tips and Tricks

A list of user-submitted tips ...

Technical Change History

This Technical Change History provides a reverse chronological list of WebDNA changes...

Related Readings:

[deletefolder]

Deletes a folder from your website...

triggers

Triggers provide a mechanism for doing something on a regular timed basis...

[copyfile]

Copy a file with the content you want on your webspace...

DB encryption

Database Automatic Encryption (from WebDNA 8...

[encrypt]

[encrypt] and [decrypt] allow you to store sensitive data in your databases without risk of exposing it to prying eyes...

[cookie]

Cookies are a great way to remember visitors...