Case, Calculation, look-up

[Stanley Bezhanski]

Hello, everybody

I'm new to the forum and I have one case, which I could solve.

I will try to explain it as much as I can.

I want to create a relationship between Post Codes, Wight and Rates. My idea is - when I enter a Post Code and Weight - the rate automatically to show up. So I create additional table with all zips codes and rates. For my rate quote field, I checked "Calculated Value" and following formula as Case

Case ( Max ( WEIGHT;DIM_WEIGHT) <= "100"; rateszips 2::onehunlbs; Max ( WEIGHT;DIM_WEIGHT) > "1000";rateszips 2::onethoulbs; Max ( WEIGHT;DIM_WEIGHT) > "2000";rateszips 2::twothoulbs; Max ( WEIGHT;DIM_WEIGHT) > "3000";rateszips 2::threethoulbs;Max ( WEIGHT;DIM_WEIGHT) > "5000";rateszips 2::fivethoulbs;)

I want every number between 0 and 100 in max of "Weigh" or "Dim_weight" with combination of zip code to look for "onehunlbs" field on my Zips table. It shows the the right rate if I enter a number in the range of 0 and 100, but if I enter a number above 1000 .. it doesn't change the rate. What could be the problem. Is it something wrong with my formula ? Any Ideas ?

Thanks in advance!

[comment]

I would create a separate record for each rate (with fields for PostCode, FromWeight and Rate), then lookup the rate using a relationship defined as:

YourTable::PostCode = Rates::PostCode

AND

YourTable::Weight ≥ Rates::FromWeight

with Rate records sorted by FromWeight, descending.

[Stanley Bezhanski]

Thanks for the advice!