1 2 Previous Next 26 Replies Latest reply on Dec 25, 2016 12:14 PM by seapy

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

    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.

       

      Robert

        • 1. Re: How do I access a number in the next (sorted) record for calculation in the current record?
          erolst

          Hi Robert -

           

          seapy wrote:

          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.

           

          lest you get lost in the labyrinths that FileMaker menus and dialogs can be:

           

          GetNthRecord ( yourField ; Get ( RecordNumber ) + 1 )

           

          returns the value from yourField in the next record of the current found set. So try

           

          Let (

            nextPrice = GetNthRecord ( ticketPrice ; Get ( RecordNumber ) + 1 ) ;

            nextPrice - ticketPrice

          )

          1 of 1 people found this helpful
          • 2. Re: How do I access a number in the next (sorted) record for calculation in the current record?
            seapy

            Thank you, will try that  later today.  I had a feeling it was simply my lack of familiarity with the commands and language.

            • 3. Re: How do I access a number in the next (sorted) record for calculation in the current record?
              philmodjunk

              "...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."

               

              There will be compatibility problems between the current version of FileMaker GO and FileMaker 11. FileMaker 12 introduced a new file format that is used by the GO version(s) currently available in the APP Store.

               

              You'd need to either upgrade to a newer version of FileMaker Pro or export the data as CSV from GO, send it to your computer, and then import the data into 11. And that wouldn't let you create the GO solution in the first place.

              • 4. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                seapy

                If I can overcome the headline issue and no insurmountable issue presents itself, I may well upgrade to the latest version to retain compatibility with FMG, I have various projects which could make good use of Filemaker.

                 

                Is there any word or expectation when the update to FMP16 is likely to happen?  Would be just my luck to get v15 and  v16 be released!

                 

                Robert

                • 5. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                  drakeen

                  You can also do it as an auto-enter calculation so that when a new record is created, the “Previous Milage” field is auto-populated from the last record (assuming the records are sorted appropriately by date), just like a TimeStamp and/or GPS coordinates can be auto-calculated every time you go to add a new record to that table. This way if the record sort order changes later, you'll still get the correct MPG calculation for that particular record regardless.

                  • 6. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                    philmodjunk

                    Just didn't want you to invest in a log of development only to discover that you couldn't use FM GO as you planned.

                     

                    I wouldn't use GetNthRecord in an unstored calculation to pull data from another record. This quickly "mushrooms" and you run out of system resources as the number of log entries grows--especially in FM Go. An auto-enter option, as previously recommended will avoid that.

                     

                    Another option that you might want to explore is to use a summary field set to compute a running total. This will be slower to update than an auto-enter calculation that copies the value from the previous record, but it also will update dynamically if you go back and have to correct an error in a previous entry after the fact. This is one of those classic trade-offs--whether you go with a nonstored solution that is slower to update but is always up to date or a stored value option (auto-enter) where any later edits have to also update a group of records in your table to get correct values, but which displays much more rapidly.

                    • 7. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                      seapy

                      You are proposing using the milage from the *previous* record, but the first record has no previous... thereby triggering an (ongoing?) error?  By using the following or *next* record the error might be more readily handled or even ignored as it is in Excel.  The MPG should be calculated from the fuel used on that iteration of use (milage), not the previous (milage)?  The fuel purchased is not always the same each time. The MPG is not strictly correct but it does average out over time.  The monthly and annual figures are accurate enough.

                       

                      Does the choice of previous record milage or next record milage affect the access of the recorded milage in the adjoining record?

                       

                      I have in fact tried to create an auto calculated field, that was my aim but it was the syntax or apparent lack of a suitable command which stopped me in my tracks.

                       

                      Thank you for your interest.

                      • 8. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                        philmodjunk

                        Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecordNumber ( FieldNameHere ; R - 1 ) ; 0 ) )

                         

                        Will handle the case where there is no previous record

                        • 9. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                          seapy

                          Well, I have tried your suggestion:

                           

                          "lest you get lost in the labyrinths that FileMaker menus and dialogs can be:

                           

                          GetNthRecord ( yourField ; Get ( RecordNumber ) + 1 )

                           

                          returns the value from yourField in the next record of the current found set. So try

                           

                          Let (

                            nextPrice = GetNthRecord ( ticketPrice ; Get ( RecordNumber ) + 1 ) ;

                            nextPrice - ticketPrice

                          )"

                           

                          The records weren't sorted on import and it scrambled the calculation.  Not easy to recover from that, the result needs to be calculated on the fly.  There are sometimes missing fuel tickets or errors of input which would be hard to correct in FMP.

                           

                          I am coming to the conclusion that using FMP to calculate the statistics is going to be a nightmare.  No doubt somebody who is very familiar with FMP and has an academic brain, would find it a doddle but I don't see spending a lot of effort re-creating what I already have working elegantly in Excel. Maths is not my first subject.

                           

                          I am still considering a 'flat' FMP input file to collect the data for Excel getting the basic raw data into FMP is certainly easier than it is with Excel, then export it from FMP  to Excel for analysis.

                           

                          Thank you so much for your attempts to help me achieve this but it now seems to me I was on the wrong path on this occasion.

                          • 10. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                            erolst

                            seapy wrote:

                             

                            The records weren't sorted on import and it scrambled the calculation. Not easy to recover from that, the result needs to be calculated on the fly. There are sometimes missing fuel tickets or errors of input which would be hard to correct in FMP.

                            Leaving aside the problems in performance that PMJ pointed out in other posts: the function I suggested will retrieve the value from the following record of the found set in sort order.

                             

                            Neither found set nor sort order have to remain what they were when you imported the records; you can omit single or multiple records manually, perform Find and Sort operations to determine the set and the order of your records, and thus the result of this (and other) calculations.

                             

                            eg once you add missing records and repeat the Find/sort operations for the impacted set (by person, or time period, or ...), you will receive different results. FM can only work with what you feed it (but the same is true for Excel, I assume ..)

                            • 11. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                              seapy

                              I will work with it some more, perhaps it will just 'click'!

                               

                              I'm not short on processing power, I don't think I will even cause my MP to raise a sweat with any of this...

                               

                              [img]https://c8.staticflickr.com/1/598/30942444823_0b15b55792_o.jpg[/img]

                               

                              Perhaps I'm giving up too easily but I don't want to go too far down a blind alley!

                              • 12. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                                siplus

                                Your long post (which uses magic words unknown to the many, like "sinclair zx81") goes right to the heart and opens wide the beer fridge in the basement, but fails to pinpoint what you really want to achieve.

                                 

                                Begin with what your eyes feel happy looking at, and we can backtrack together to what you really need in order to get that picture.

                                1 of 1 people found this helpful
                                • 13. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                                  erolst

                                  Herr Siplus, guten Abend!

                                   

                                  Always having no less than the big picture in mind. I like that.

                                  • 14. Re: How do I access a number in the next (sorted) record for calculation in the current record?
                                    seapy

                                    Herr Siplus, vielen Dank!

                                     

                                    I find it difficult to come straight onto a new (to me) forum with a complicated technical problem without giving a little of my background. So I opened the beer fridge, that's a good start!

                                     

                                    There is a saying that a picture tells a thousand words... This is what my eyes feel very happy looking at:

                                    Screen Shot 2016-12-20 at 07.26.12.jpg

                                    This is part of one page (per year) Sometimes there is more than one vehicle, maybe three at times.  Then I usually create another similar spreadsheet on the same page (tab).

                                     

                                    I want to use FMP to ease data entry which can be tedious and prone to error at times.  With FMP it is easier to regulate the data entered and stop silly input errors before they become embedded in the data.

                                     

                                    I would like to have some means of having an overall summary comparing different vehicles and different years, perhaps with charts, but walking before running seems to be sensible.

                                     

                                    The summary at the bottom is the data I am really after.  My headline question relates to extracting the 'inter' milage shown in the above spreadsheet, which I now realise isn't actually used in the calculation of each entry, only really needed in the monthly summary. I do use it during data entry because it reveals missing fuel ticket data and possible errors, so I do need that calculation even though it isn't used in the summaries.

                                     

                                    It's highly desirable to subtract the current milage from the next milage, in order to get the most accurate figures. At the end of each year I manually insert the next years starting milage to complete the calculation.  As you can see the final entry on 22 Aug results in an error due to the next line of data being absent. That is OK because unless you put in an averaged temporary figure I can't expect the spreadsheet to work with unknowns.

                                     

                                    I could have an "If empty insert Average" condition but I don't mind the error since I can close it manually at the end of the year.  That may be a complication with FMP?

                                     

                                    I hope that helps, I realise having the bigger picture is important but I didn't want to over burden the landscape.

                                     

                                    As for magic unknown words I still have my first 'Apple Mac SE' I bought new in 1987!

                                    1 2 Previous Next