AnsweredAssumed Answered

How do I access a number in the next (sorted) record for calculation in the current record?

Question asked by seapy on Dec 18, 2016
Latest reply on Dec 25, 2016 by seapy

Hi, I am trying to re-create in FMP11 a large Excel spreadsheet where I record my vehicles and their MPG, fuel costs etc.  It goes back to 1984, I have all the fuel tickets, about 60-70% are currently entered.  The spreadsheet gives me statistics on the vehicle, MPG, cost, usage and which filling stations I use most etc.  Over the years this data has been in databases and spreadsheet of various computers, from Sinclair ZX81 to Apple MacPro and many in between.


Many moons ago I ran a small business on FM3, I migrated to FMP6, then bought FMP11 but hardly used it. Am retired a few years now but like to keep active.  I find the data entry in Excel spreadsheets tedious and more prone to error than a rigid data entry screen in Filmaker, then I saw that there is now an iPhone App, Filemaker Go, which I am assuming would allow me to enter the data at the filling station before I leave, therefore keeping the data entry right up to date.  The milage is the main item I need to record since the cost and Litres are (usually) on the fuel ticket. I could also timestamp the record on the fly, perhaps location data can be used to identify the filling station?  Possibly allowing me to display the filling stations I visit on a map...


I have entered 2016 data and imported 2015 from the Excel spreadsheet to a FMP11 database (DB).  I have three databases linked.  First the filling stations, with their details, telephone, postcode, which road number they are on and so on. Secondly my various vehicles which have obviously changed over the years sometimes more than one for a period. And finally the fuel ticket DB, listing the date, time, litres, cost, filling station and miles.


I have been trying to use the fuel ticket DB as the master, referring to the filling stations and vehicle DB's for auto entry in the lists.  That all works well of course.  However it all grinds to a standstill because to do the crucial MPG calculation I need to subtract the current fuel ticket milage from the next fuel ticket milage to establish how far the fuel from the current ticket had driven the car.  I have dug and delved in all the menus and formatting dialogue boxes I can find, I can't find any way to access the next (chronologically sorted) record data.


Might it be easier to create another 'calculate and display' database to deal with  the MPG calculations?


In terms of structure I found creating the spreadsheet in Excel quite easy, it's just that the references can get mixed up during data entry and the structure need replicating during entry which, while it isn't the end of the world, it's all extra work.  I thought that Filemaker might make it easier to enter the data and retain consistency.  If I can master this MPG calculation procedure in FMP11 and it works well, I would buy FMP15 and that would presumably 'talk'  with Filemaker Go, via Wi-Fi when I get home, which doesn't seem to want to acknowledge FMP11, not surprisingly!


If you got this far and are still awake, Thank you!  It's an ongoing work of mine which I would like to refine and perfect but because I am not 'up to speed' with FMP11 I need a little help please.  Simply importing from the Excel spreadsheet does not carry across the structure or calculations which make the spreadsheet work in FMP11! It simply imports the data.