5 Replies Latest reply on Jan 17, 2014 6:27 AM by philmodjunk

# Calculating mileages from Actual and Estimated readings

### Title

Calculating mileages from Actual and Estimated readings

### Post

I would like to be able to record a vehicles mileage in our database and have the latest reading display.
So far, so good.
I have a Vehicles table and a VehicleMileage related table; at the moment I use a Summary field with the Max of the ActualMileage to I get the latest reading.

However, to complicate things; we receive monthly approximate miles travelled for each vehicle, and I would like to create an Calculated Mileage that is based on the last actual mileage.
I've put the monthly mileages in a separate field in the VehicMileage table, each in it's own record.

I think I need something like this:

CalculatedMilage = s_MaxActualMileage + Sum ( MonthlyEstimated (if after date of last mileage) )

i.e.

Actual Mileage

Monthly Estimated

Calculated

info

20,004

20,004

20004

2,462

22,466

20004+(2462)

1,912

24,378

20004+(2462+1912)

24,404

24,404

24404

2,415

26,819

24404+(2415)

2,014

28,833

24404+(2415+2014)

28,756

28,756

28756

As you can see sometimes we may have over estimated and sometimes underestimated. But I want to find the last Actual Mileage + the sum of the Monthly miles if they're after the previous Actual.

Hope I haven't confused things too much!
Phil

• ###### 1. Re: Calculating mileages from Actual and Estimated readings

So once again we encounter the "I want a sum if" request. And FileMaker does not have such a function.

But it should be possible to set up a relationship via a self join that only accesses estimated mileage that was entered after the last actual mileage entry.

Am I correct that this table also has a field for the date and another for the Vehicle ID?

• ###### 2. Re: Calculating mileages from Actual and Estimated readings

Yes, I have other the fields.

Just needs a different perspective, doesn't it?
I'm slowly getting round to the FM way of thinking!

Thanks very much.

• ###### 3. Re: Calculating mileages from Actual and Estimated readings

It might also be interesting to see what can be done with ExecuteSQL for this problem...

• ###### 4. Re: Calculating mileages from Actual and Estimated readings

That might be an option. I'm having difficulty with the self join relationship way of getting to the right number.

Have you got any useful resources for ExecuteSQL newbies like me?

• ###### 5. Re: Calculating mileages from Actual and Estimated readings

SeedCode offers the free SQL Explorer that can help you write SQL queries with the correct syntax.

And FileMaker released a new PDF manual on SQL with FileMaker 13: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf