Help with Auto-Calc Field

[mkohler28]

I have a field called Charge Amount and I'm trying to evaluate between three different records to determine which amount to use.

 

I have a field with a weight in it, 300 lbs is the value and the Amount filed in one db.

 

In another db I have three related records that look like this:

 

1) Minimum Rate of $20.00

2) Maximum Rate of $500.00

3) Per Lb Rate of $.20 per lb

 

I am trying to evaluate what rate should be used and populate the Charge Amount field.

 

So looking at the three records I am going to determine what the per lb rate is first, in this case it's $60.00 (.20 x 300), then I have to compare that to the Minimum rate if it's more than $20.00 I use the per lb rate otherwise I use the Minimum, I also compare it to the Maximum rate and if it's less than that I use the per lb rate otherwise the Maximum rate of $500.00.

 

What's the best way of doing this to evaluate in one field?

[touchMe]

try... If(weight * rateLB > maxCharge, maxCharge, If(weight * rateLB

[mkohler28]

I have figured that part out already and works when evaluting data in the same record but I have three separate records to evaluate against.

[touchMe]

I have figured that part out already and works when evaluting data in the same record but I have three separate records to evaluate against.

 

are the three 'records' in the same file/table? - or are they in different files/tables?

[touchMe]

I should have added... if the three records are in the same file/table then they are related, if not and they are in different files/tables, what is the relationship?

[mkohler28]

The three records are in a separate file, the relationship is:

 

Tariff ID = Tariff ID

and Charge Type = Charge Type

and Charge Code = Charge Code

 

Tariff ID (TAR0000000001)

Charge Type (ACCESSORIAL)

Charge Code (DELIVERY)

 

I want the calculation to change after the user picks the Charge Code