Re: Trouble with formula.db
This WebDNA talk-list message is from 1997
It keeps the original formatting.
numero = 10945
interpreted = N
texte = >In the invoice.tmpl we have the customer enter all the billing and >shipping address information. In the final_invoice.tmpl we display the >relevant information, costs, etc. and have a purchase button for them to >hit. > >However, I cannot get the shipCost value to calculate from the formula.db.I cannot debug your big formula without actually having a copy of your databases and templates, so instead I've tried to simplify what you're doing into a different way of calculating the shipCost. It seems that most of your WebDNA is basically a lookup based on weight and country, so I've made a formula that starts there. It doesn't do all that yours does, but it's a good starting point.It has a couple 'tricks': one is to use a database search to look for ranges of weights, and the other helps you look in different columns of the database depending on which country you're shipping to.Here's a shipping cost database that has different countries and weights:------ MultiCountryShipping.db ------- MaxWeight USA Mexico Sweden Italy 0.0 4.50 7.00 9.00 8.00 0.5 5.00 7.50 9.50 8.50 1.0 6.00 8.50 10.50 9.50 5.0 9.00 10.00 12.00 11.00 1000.0 10.00 11.00 13.00 12.00E.G. For weights between 0.0 and 0.5, the shipping cost to Italy is 8.00Now the formula for shipCost looks like this:[search db=MultiCountryShipping.db&leMaxWeightdata=[totalWeight]&MaxWeightsort=1&MaxWeightsdir=de&MaxWeighttype=num&max=1][foundItems][Interpret][[ShipToCountry]][/Interpret][/foundItems][/search]Let's break this down step by step.We want to pick a cost for a weight that is in any of the ranges 0.0 - 0.5 0.5 - 1.0 1.0 - 5.0 5.0 - 1000.01) Search for all weights that are Less Than or Equal to the totalWeight. If the totalWeight is 3.5, then the found items will be {0.0, 0.5, 1.0}, and we should pick the cost for 1.0 (not 5.0, because that's outside the range).2) Problem: this finds 3 answers, not 1. We want the only the highest weight. Solution: Set max=1 so only 1 answer comes back3) Problem: Setting max=1 will return 0.0 from the list of {0.0, 0.5, 1.0}, but we want the highest weight instead. Solution: Sort the results in descending order so the first weight we see is highest4) Now we need to read across the table to the country and pick a cost.5) Problem: simply putting [FoundItems][ShipToCountry][/FoundItems] will display literally USA or Sweden instead of the interpreted value of that column name. Solution: Use [Interpret] tag to force WebCatalog to interpret the WebDNA6) Problem: Simply putting [Intrepret] around [ShipToCountry] looks like [Interpret]USA[/Interpret] which evaluates to just USA. Solution: Put extra [] around the tag, so [[ShipToCountry]] -> [USA], which now looks like a tag WebCatalog can interpret in the context of [FoundItems] as a field name from the database.Question: Why couldn't we simply use [Lookup] instead? Answer: Because lookup is too simplistic -- it can't search on ranges or sort results. That's why it's so fast. But you may be able to come up with an even simpler solution if you're careful to arrange the records in the right order and use > or < comparisons inside [Lookup]Grant Hulbert, V.P. Engineering | ===== Tools for WebWarriors ===== Pacific Coast Software | WebCatalog Pro, WebCommerce Solution 11770 Bernardo Plaza Court, #462 | SiteEdit Pro, SiteCheck, PhotoMaster San Diego, CA 92128 | SiteGuard 619/675-1106 Fax: 619/675-0372 | http://www.smithmicro.com
Associated Messages, from the most recent to the oldest:
>In the invoice.tmpl we have the customer enter all the billing and >shipping address information. In the final_invoice.tmpl we display the >relevant information, costs, etc. and have a purchase button for them to >hit. > >However, I cannot get the shipCost value to calculate from the formula.db.I cannot debug your big formula without actually having a copy of your databases and templates, so instead I've tried to simplify what you're doing into a different way of calculating the shipCost. It seems that most of your WebDNA is basically a lookup based on weight and country, so I've made a formula that starts there. It doesn't do all that yours does, but it's a good starting point.It has a couple 'tricks': one is to use a database search to look for ranges of weights, and the other helps you look in different columns of the database depending on which country you're shipping to.Here's a shipping cost database that has different countries and weights:------ MultiCountryShipping.db ------- MaxWeight USA Mexico Sweden Italy 0.0 4.50 7.00 9.00 8.00 0.5 5.00 7.50 9.50 8.50 1.0 6.00 8.50 10.50 9.50 5.0 9.00 10.00 12.00 11.00 1000.0 10.00 11.00 13.00 12.00E.G. For weights between 0.0 and 0.5, the shipping cost to Italy is 8.00Now the formula for shipCost looks like this:[search db=MultiCountryShipping.db&leMaxWeightdata=[totalWeight]&MaxWeightsort=1&MaxWeightsdir=de&MaxWeighttype=num&max=1]
[founditems][interpret][[ShipToCountry]][/Interpret][/foundItems][/search]Let's break this down step by step.We want to pick a cost for a weight that is in any of the ranges 0.0 - 0.5 0.5 - 1.0 1.0 - 5.0 5.0 - 1000.01) Search for all weights that are Less Than or Equal to the totalWeight. If the totalWeight is 3.5, then the found items will be {0.0, 0.5, 1.0}, and we should pick the cost for 1.0 (not 5.0, because that's outside the range).2) Problem: this finds 3 answers, not 1. We want the only the highest weight. Solution: Set max=1 so only 1 answer comes back3) Problem: Setting max=1 will return 0.0 from the list of {0.0, 0.5, 1.0}, but we want the highest weight instead. Solution: Sort the results in descending order so the first weight we see is highest4) Now we need to read across the table to the country and pick a cost.5) Problem: simply putting
[founditems][ShipToCountry][/FoundItems] will display literally USA or Sweden instead of the interpreted value of that column name. Solution: Use
[interpret] tag to force WebCatalog to interpret the WebDNA6) Problem: Simply putting [Intrepret] around [ShipToCountry] looks like
[interpret]USA[/Interpret] which evaluates to just USA. Solution: Put extra [] around the tag, so [[ShipToCountry]] -> [USA], which now looks like a tag WebCatalog can interpret in the context of
[founditems] as a field name from the database.Question: Why couldn't we simply use
[lookup] instead? Answer: Because lookup is too simplistic -- it can't search on ranges or sort results. That's why it's so fast. But you may be able to come up with an even simpler solution if you're careful to arrange the records in the right order and use > or < comparisons inside
[lookup]Grant Hulbert, V.P. Engineering | ===== Tools for WebWarriors ===== Pacific Coast Software | WebCatalog Pro, WebCommerce Solution 11770 Bernardo Plaza Court, #462 | SiteEdit Pro, SiteCheck, PhotoMaster San Diego, CA 92128 | SiteGuard 619/675-1106 Fax: 619/675-0372 | http://www.smithmicro.com
Grant Hulbert
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:
[INCLUDE] Limitations (1998)
Shipping charges (1998)
[WebDNA] Trigger stops executing (2017)
date (2002)
Big Databases (1997)
RE: protect tag not working (1998)
Configuring E-mail (1997)
2.0 Info (1997)
[WebDNA] DNA suffix (2008)
Include vs. lookup? (1998)
Webcat2, WebCommerce, Mod 10 etc. (1997)
Stripping attachments in shared pop (2005)
Pass a form (2003)
WebCatalog Beta Documentation (1997)
Mac Vs WindowsNT (1997)
show all problem (1997)
Browser Info.txt (1997)
[WebDNA] Was: wiki Now: Object-oriented programmng (2009)
3 card formulas! (1999)
New cart in ShoppingCarts (2003)