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_lbCANADA 1 16.5 18.75 22.5 26.5 30 34 3.75CHINA 1 16.5 18.75 22.5 26.5 30 34 3.75IRELAND 1 16.5 18.75 22.5 26.5 30 34 3.75JAPAN 1 16.5 18.75 22.5 26.5 30 34 3.75MEXICO 1 16.5 18.75 22.5 26.5 30 34 3.75USA 1 16.5 18.75 22.5 26.5 30 34 3.75ISRAEL 6 20 22.5 26.5 32.5 39.5 47.5 6.5JORDAN 6 20 22.5 26.5 32.5 39.5 47.5 6.5KENYA 6 20 22.5 26.5 32.5 39.5 47.5 6.5ANGUILLA 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][/hideif][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([totalWeight])-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][/math][/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][/hideif]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([totalWeight])-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][/math][/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([totalWeight])-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][/math][/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_lbCANADA 1 16.5 18.75 22.5 26.5 30 34 3.75CHINA 1 16.5 18.75 22.5 26.5 30 34 3.75IRELAND 1 16.5 18.75 22.5 26.5 30 34 3.75JAPAN 1 16.5 18.75 22.5 26.5 30 34 3.75MEXICO 1 16.5 18.75 22.5 26.5 30 34 3.75USA 1 16.5 18.75 22.5 26.5 30 34 3.75ISRAEL 6 20 22.5 26.5 32.5 39.5 47.5 6.5JORDAN 6 20 22.5 26.5 32.5 39.5 47.5 6.5KENYA 6 20 22.5 26.5 32.5 39.5 47.5 6.5ANGUILLA 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][/hideif][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([totalWeight])-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][/math][/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][/hideif]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([totalWeight])-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][/math][/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([totalWeight])-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][/math][/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:

simple forum/bboard (2004) emailer (1997) [/application] error? (1997) Running 2 two WebCatalog.acgi's (1996) Loop weirdness (2003) [WebDNA] Screen Resolution - detection & redirect (2012) Sorting Numbers (1997) WebCatb15 Mac CGI -- [purchase] (1997) Nested search (1997) Getting Rid of Multiple Returns (2001) Did you hear about this? (1997) Great product and great job ! (1997) Tiny Store (1998) New command suggestion (was Modifying databases manually) (1997) mac hack (1997) PCS Frames (1997) Multiple Pulldowns (1997) WebCat2_Mac RETURNs in .db (1997) Emailer setup (1997) Decimal point in search.... (2004)