You need redirection a bit ... your Freight_Rates table needs *three fields: To, Weight and Rate. For Sydney, there would be three records (not fields). Enter only the ceiling base weight for each rate, such as:
Now in your graph, join the tables as:
MainTable::Destination = Freight_Rates::To
MainTable::Base Weight > Freight_Rates::Weight (new field for rates)
While in that window (below viewing Freight_Rates options), check 'Sort Records' and sort the Weight field descending order. Now ... whenever you enter a weight and destination, the proper freight rate will be the only related record so you can refer to it directly in scripts, calculations and, probably in this case, use an auto-enter (do not replace) calculation of: Freight_Rates::Rate ... or you might use a Lookup instead. I hope I explained it but if you wish, I can provide link to example file. :^)
*Freight_Rates (and ALL tables) should contain a uniqueID which is FM-generated, auto-enter serial number even if you do not use the ID for relationships. If you ever have to retrieve from backup, update, merge, import etc, you will need this record identifier (and do not think FM's internal RecordID will help you). All tables should also have creation and modification timestamps, for many reasons only one important one being data recovery.
Thanks for this. I see I've got to think slightly different here. I'm getting this now however and example file would be extremely useful... I find it helps me learn better if I have examples to pull apart.
I appreciate your help with this.
Thanks again LaRetta,
Got that sorted now.
The auto enter I can't work out now :(
I now have the two tables with relationships defined:
3. To (drop down related to "Weight table" "To")
4. Weight (drop down related to "Weight table" "Weight")
5. Rate (auto-enter based on Freight::To = Weight::To and Freight::Weight = Weight::Weight then Freight::Rate = Weight::Rate)
6. Freight Total (simple calculation)
Now Freight::Rate does not auto fill and I'm unsure as to why.
I have tried auto lookup and calculate but I must be entering the wrong string.
I would appreciate any help understanding this. :)
Item 5 - you indicate you have Freight::Weight = Weight::Weight and that is incorrect. Look again at my relationships which uses > and not =.
Existing records will not pre-fill because this process is set up to happen when a record is created and then not change. You don't want it to change if next year, rates change so it cannot be a calculation. After correcting the relationship, see if it works properly on newly created records. It works properly in my file, right? If you still can't find the issue, you will need to provide a link here to your file (you can upload to any free share site such as 4shared.com).
" ... then Freight::Rate = Weight::Rate) "
Within the auto-enter calculation, all you should type is Weight::Rate
Did you type Freight::Rate = Weight::Rateby chance?
Yes... item 5, you were correct, I was looking in the wrong place. Stopped, started again and found the error.
Thanks very much for your help :)