What is WebDNA

WebDNA is a scripting and database system designed to easily build web applications.

WebDNA and BioType

BioType service is a biometric keystroke dynamic system. It will be part of WebDNA 8.5

Download WebDNA

Download WebDNA freeware, try it and register later if you want.

WebDNA resources

The list of all WebDNA instructions.
WebDNA
Software Corporation
Search WebDNA Site
 Menu


HOME


DOWNLOADS


LEARN


EDUCATION


NEWS


COMMUNITY


STORE


SUPPORT


CONTACT

Re: Trouble with formula.db + more explanation

This WebDNA talk-list message is from

1997


It keeps the original formatting.
numero = 10963
interpreted = N
texte = >Re: Trouble with formula.db .... Grant's nice example removed!..sorry..I guess I should give Grant a better explanation of the complexity of the calculation we want for shipCost. Below is a sample of our country_cost_list.db. It contains besides the countries, a mail group_code (there are 6 of them {I only put 1 and 6 in the example below} that determine the cost rates in the other fields). The totalWeight should always be rounded up for whole pounds, only if it is over 0.5lbs. The addl_lb field contains the rate for totalWeights that exceed 5 lbs (e.g. If the totalWeight = 7.3 lbs for ISRAEL then the calculation is (3 * addl_lb) + 5 lb field or (3 * 6.5) + 47.5, if totalWeight = 3.45 for ISRAEL then you get 39.5. NOTE: There are some countries that have no rates and are not part of the mail group code(yet!). In these cases I always want zero (which we get now).Country Group_Code half_lb 1 2 3 4 5 addl_lb CANADA 1 16.5 18.75 22.5 26.5 30 34 3.75 CHINA 1 16.5 18.75 22.5 26.5 30 34 3.75 IRELAND 1 16.5 18.75 22.5 26.5 30 34 3.75 JAPAN 1 16.5 18.75 22.5 26.5 30 34 3.75 MEXICO 1 16.5 18.75 22.5 26.5 30 34 3.75 USA 1 16.5 18.75 22.5 26.5 30 34 3.75 ISRAEL 6 20 22.5 26.5 32.5 39.5 47.5 6.5 JORDAN 6 20 22.5 26.5 32.5 39.5 47.5 6.5 KENYA 6 20 22.5 26.5 32.5 39.5 47.5 6.5 ANGUILLA ANTIGUA BRITISH_VIRGIN_IS. COOK_ISLANDS DOMINICA FAEROE_ISLANDS Below is the whole formula:[hideif [totalWeight]>0.5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=half_lb][/hi deif] [showif [totalWeight]>0.5][hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif][showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif][/showif]What I do is use conditional math with the showifs and hideifs to determine which lookup form I should use to get the value! What makes this formula even tougher to read is we test our customer's input in cases in which the shipto address is the same as the billing address (the [shiptocountry] gets the value, specify_country). Otherwise the customer selects a shiptocountry name from a popup country selection (derived from above db). The part of the big huge formula above that handles the shipto vs. billing address is (it works):[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]Now lets break up the above formula by the conditional math tests:1) For less than 0.5 lbs (the simplest)[hideif [totalWeight]>0.5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=half_lb][/hi deif]2) For greater 0.5 lbs[showif [totalWeight]>0.5][hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif][showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif][/showif]which contains the sub conditional tests within the top level showif for2a) 1 to 5 lbs[hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif]2b) greater than 5 lbs[showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif] Now, what is puzzling is the fact that the above formula works if I place it in a .tmpl file (it results in displaying the calculated shipCost). However, if I paste the exact same formula in the formulas.db for shipCost it fails. So, is there some funny syntax incosistencies or limits between .tmpl code and code for the formulas.db?This has been a real stumper :-( Curt ---------------------------------------------------------------------- | William Curt Eggemeyer Curt@mail.bzzzzzz.com | | BeeHive Technologies, Inc. | | http://www.bzzzzzz.com with a WebCam in Pasadena, California | ---------------------------------------------------------------------- Associated Messages, from the most recent to the oldest:

    
>Re: Trouble with formula.db .... Grant's nice example removed!..sorry..I guess I should give Grant a better explanation of the complexity of the calculation we want for shipCost. Below is a sample of our country_cost_list.db. It contains besides the countries, a mail group_code (there are 6 of them {I only put 1 and 6 in the example below} that determine the cost rates in the other fields). The totalWeight should always be rounded up for whole pounds, only if it is over 0.5lbs. The addl_lb field contains the rate for totalWeights that exceed 5 lbs (e.g. If the totalWeight = 7.3 lbs for ISRAEL then the calculation is (3 * addl_lb) + 5 lb field or (3 * 6.5) + 47.5, if totalWeight = 3.45 for ISRAEL then you get 39.5. NOTE: There are some countries that have no rates and are not part of the mail group code(yet!). In these cases I always want zero (which we get now).Country Group_Code half_lb 1 2 3 4 5 addl_lb CANADA 1 16.5 18.75 22.5 26.5 30 34 3.75 CHINA 1 16.5 18.75 22.5 26.5 30 34 3.75 IRELAND 1 16.5 18.75 22.5 26.5 30 34 3.75 JAPAN 1 16.5 18.75 22.5 26.5 30 34 3.75 MEXICO 1 16.5 18.75 22.5 26.5 30 34 3.75 USA 1 16.5 18.75 22.5 26.5 30 34 3.75 ISRAEL 6 20 22.5 26.5 32.5 39.5 47.5 6.5 JORDAN 6 20 22.5 26.5 32.5 39.5 47.5 6.5 KENYA 6 20 22.5 26.5 32.5 39.5 47.5 6.5 ANGUILLA ANTIGUA BRITISH_VIRGIN_IS. COOK_ISLANDS DOMINICA FAEROE_ISLANDS Below is the whole formula:[hideif [totalWeight]>0.5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=half_lb][/hi deif] [showif [totalWeight]>0.5][hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif][showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif][/showif]What I do is use conditional math with the showifs and hideifs to determine which lookup form I should use to get the value! What makes this formula even tougher to read is we test our customer's input in cases in which the shipto address is the same as the billing address (the [shiptocountry] gets the value, specify_country). Otherwise the customer selects a shiptocountry name from a popup country selection (derived from above db). The part of the big huge formula above that handles the shipto vs. billing address is (it works):[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]Now lets break up the above formula by the conditional math tests:1) For less than 0.5 lbs (the simplest)[hideif [totalWeight]>0.5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=half_lb][/hi deif]2) For greater 0.5 lbs[showif [totalWeight]>0.5][hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif][showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif][/showif]which contains the sub conditional tests within the top level showif for2a) 1 to 5 lbs[hideif [totalWeight]>5][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=[math]ceil([ totalWeight])[/math]][/hideif]2b) greater than 5 lbs[showif [totalWeight]>5][math][lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=5]+(ceil([to talWeight])-5)*[lookup db=country_cost_list.db&lookInField=country&value=[hideif [ShipToCountry]=Specify_Country][ShipToCountry][/hideif][showif [ShipToCountry]=Specify_Country][Country][/showif]&returnField=addl_lb][/ma th][/showif] Now, what is puzzling is the fact that the above formula works if I place it in a .tmpl file (it results in displaying the calculated shipCost). However, if I paste the exact same formula in the formulas.db for shipCost it fails. So, is there some funny syntax incosistencies or limits between .tmpl code and code for the formulas.db?This has been a real stumper :-( Curt ---------------------------------------------------------------------- | William Curt Eggemeyer Curt@mail.bzzzzzz.com | | BeeHive Technologies, Inc. | | http://www.bzzzzzz.com with a WebCam in Pasadena, California | ---------------------------------------------------------------------- w curt eggemeyer

DOWNLOAD WEBDNA NOW!

Top Articles:

Talk List

The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...

Related Readings:

WCS Newbie question (1997) WebCat & WebTen (1997) frames & carts (1997) ShipCost Data Base (1998) WebCatalog Beta Documentation (1997) Weeks starting on Mondays (1998) [ot] iBook vs. 12" PB (2004) mailto: tag (2001) [WebDNA] MD5 Hash issue (2009) price carry over (1997) Re:HELP - NONE STOP DIGESTS. Digest for 4/24/97) (1997) [Way OT] Workstation Security (2002) Protect (1997) WebCat2b12plugin - [search] is broken ... not! (1997) Almost a there but..bye bye NetCloak (1997) [WebDNA] tag [validcard] fails on webdna 7.0. Do I need the commerce edition? (2011) searching multiple databases in single search (1997) WebCatalog can't find database (1997) Tip for making barcharts (1997) An easy one (1999)