Try having having a look up field which "looks up" the weight based on the ID. This should be in the main lifts table. then use this value in your calculation.
What is the relationship between the two tables? In which table are you attempting to calculate a result?
A possible issue here is that you may have a one to many relationship where you have one record in one table matching to multiple records in another table. If you then define such a calculation in the "one" or "Parent" table, the calculation will only refer to data from the "first" out of possibly many related records unless you either use an aggregate function such as sum or refer to a summary field from the related table.
I'm having a problem wrapping my head around a lookup field. I already have 200 entries that I'm scared of disturbing.
Here's a picture for more detail.
That should work without any issues. By any chance, given your mention of "200 entries", are either of these fields repeating fields?
I have 200+ hand-entered entries representing one excercise attempt (lifting) at a time. I'm not using repeating fields.
Pic shows the weight table in question. My zID fields are being multiplied by repetitions.
Is there an option in the calculate window to use the display value of the weights instead instead of its true zID value which bungles the calculation?
This is an extremely simple calculation and FIlemaker can do basic math I see no errors in your calculation setup.
Please upload a new screen shot of your table view, but only include three columns of data in it:
As requested, PhilModJunk, I'm including a screen shot which shows reps * weights =/= correct TotalWeigthPerExcercise.
Hmmm, I see what you've posted, but see no way that's possible for the calculation you have also posted for the calculation field... Care to let me look at a copy of your file? You can upload a file to a file sharing site such as Drop Box and post the download link here.
I solved my own problem.
The problem was in the calculation field setup. I needed to refer to the correct table before I specified the field.
The correct entry should have been:
Set * Repetitions * z WeightInPounds 2::weight
I thought that was the case, but would have needed to examine the actual file to confirm...