3 Replies Latest reply on Jan 30, 2014 12:54 PM by philmodjunk

# Calculating second field via related table

### Title

Calculating second field via related table

### Post

Hello,

I tried searching for a solution, but wasn't even sure how to word my question....?

I'm developing a DB that creates running templates for our runners. Within my "templates" layout, I have a portal to a table called "Run". Within the portal, I select different running distances for different days of the week. The distances are currently pulled from a value list (1, 2, 3, 4, 5.... etc). Then I have a calculation field that adds all the mileage for the week. Everything works fine, except I also have some mile repeats in the value list. An example: On monday, run 3 x 0.5 miles. So the value list has "3x 0.5" as a value. This messes up my total mileage calculation, as it adds 3.5 to the total, instead of multiplying the 3 and 0.5 for a total of 1.5.

I figured I should go away from the value list, and make a table of mileage instead. My table has "ActualMileage" (actual mileage for the day, or "1.5" as per example) and "DisplayedMileage" (what the user will see on screen, or "3x 0.5" as per example) as fields. I just can't figure out how to make my total mileage calculation refer to the "ActualMileage" field when I select the "DisplayedMileage" value from a drop down list.

Any direction is greatly appreciated. Thank you.

Stuart

• ###### 1. Re: Calculating second field via related table

Seems like you are trying to put too much data in one field. I think that 3 x 0.5 means 3 laps around a half mile track. Invoices manage this all the time where you buy 3 gallons of milk at a set price of x dollars per gallon.

Define these fields: Laps, LapDistance, Total Mileage. Then you can calculation the total mileages as Laps * LapDistance and you can compute a total of the Total Mileage calculation field to get your portal total.

• ###### 2. Re: Calculating second field via related table

Thank you Phil;

The thought of breaking the "3x 0.5" into two fields crossed my lazy mind at one point. But then I tried hunting for a short cut with the calculation. As with everything else in FM; what seems to be more work ends up being easier in the long run. I'll make the changes.
Thanks again :)

• ###### 3. Re: Calculating second field via related table

Please note, that you could set up a calculation that could work from that text in a single field, but I think that separate fields makes for a better option.