Jump to content
Sign in to follow this  
Stanley Bezhanski

Case, Calculation, look-up

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.