Technical References - formulas.db

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

numero = 295
interpreted = N
texte = 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.

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:

[biotype]

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

AWS Raw WebDNA LAMP-Plus WebServer

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

WebDNA Libraries

A list of available libraries for WebDNA...

F.A.Q

A compilation of some user's questions...

WebDNA reference

...

Tips and Tricks

A list of user-submitted tips ...

Related Readings:

[thisurl]

[thisurl] displays the URL of the current page...

[findstring]

[FindString Source=...

[showif]

[showif Comparison]Show This HTML[/showif]...

[listfields]

Lists all the fields in the specified database...

[format]

To apply formats for Dates or Times other than the current date and time...

[object]

Embeds the results of an external function...