I tried to use my programming background to make total cost = MTABLE::"m"+damage" but its invalid to do that way.
You could use the following calculation:
Evaluate ( "MTABLE::m" & damage )
to facilitate this.
However, I would suggest you take a look at your data model. Whenever I see something like, "field1, field2, field3" or similar, I immediately smell a flaw in the data model. Your percentages should probably live in a separate table rather than in a table with the prices, joined back by discount to the main table.
The % is located in a separate table MTABLE with the fields M1-100 which represents the % damage each Mx field has a data value with is a $ value. on the main table form they enter in damage number with out the %. I need it to then take that number and find the m value to look up. if the damage is 10 Id want it to look up m10 on the other table and grab its value to show on another field on the main form called total damage.
because the value for each % is not linear each % must have a unique price.
The Evaluate ( "MTABLE::m" & damage ) did not work in the total damage calculation to grab the value from the table.
perhaps you are right Mike maybe I am approaching this incorrectly but I am not sure of another way to do.
Any help would be appreciated.
Each M value (1 - 100) should be a record in a related table. Just basic normalization principles.
If the calculation I gave you isn't working, it's probably because either your relationship is not set up correctly, or the value in the table isn't evaluating correctly. Post your Relationships Graph and the calculation in question.
That could be the issue as there is not relation ship currently between tables.
my understanding is with relationships I would need to link a field from one table to the other correct?
I put the mx number in another table just because I will be updating them from time to time and figured it would be easier to import new prices monthly rather than key them in manually into the main form table.
Yes, you have to connect the two table occurrences (not tables) on the Relationships Graph for table A to be able to see data from table B. This should typically be done using a unique key field in one table, and a corresponding field in the other table (which may or may not be unique).
I suggest you grab a copy of the FileMaker Training Series and go through it. You're lacking some significant basics that will be required for you to succeed.