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


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.