7 Replies Latest reply on Aug 9, 2011 11:04 PM by MattBrady

Form calculation from different table rows and columns?

Title

Form calculation from different table rows and columns?

Post

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"

• 1. Re: Form calculation from different table rows and columns?

Hi Matt,

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:

To:  Sydney
Weight: 0
Rate: 5

To: Sydney
Weight: 500
Rate: 10

To: Sydney
Weight: 1000
Rate: 25

Now in your graph, join the tables as:

MainTable::Destination = Freight_Rates::To
AND
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.

• 2. Re: Form calculation from different table rows and columns?

Hi LaRetta,

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.

• 3. Re: Form calculation from different table rows and columns?

Hi Matt,

Here is an example file: http://www.4shared.com/file/AnznFGm9/Freight.html

LaRetta :^)

• 4. Re: Form calculation from different table rows and columns?

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:

"Weight" Table
1. To
2. Weight
3. Rate

and

"Freight" Table
1. Product
2. Quantity
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. :)

• 5. Re: Form calculation from different table rows and columns?

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).

• 6. Re: Form calculation from different table rows and columns?

" ... 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?

• 7. Re: Form calculation from different table rows and columns?

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 :)