AnsweredAssumed Answered

Form calculation from different table rows and columns?

Question asked by MattBrady on Aug 5, 2011
Latest reply on Aug 9, 2011 by MattBrady


Form calculation from different table rows and columns?


I'm relatively new t calculations and am doing ok except when trying to do relational calculations.

I have 3 fields:
1. Base Weight (user entry field)
2. Destination (Drop down menu from "To" column of a linked FMDB)
3. Rate (This is an auto calculated field that I'm having difficulty with)

The linked DB "Freight_Rates" has 4 columns:
1. To (same one used for the drop down from "2" above)
2. Over 1000kg
3. 501 to 1000kg
4. 1 to 500kg 

What I'm trying to do is calculate the rate based on Destination & Baseweight.

For example: User enters Base Weight of 550 and Destination of Sydney (DB=Freight_Rates colA row3)
Auto calculate to work out that the Base Weight is greater than 501 and less than 1000 and therefore uses column 3 (501 to 1000kg) and then relates it to row 3 (Destination::Sydney) = Rate for "501 to 1000kg" for destination "Sydney"

Thanks, my head's looping :|