1 2 3 Previous Next 37 Replies Latest reply on Jul 1, 2017 1:56 AM by CarlSchwarz

    Fuel Log kilometre between fills

    sherab4

      I have a Fuel Log. Each entry has Odometer readings. How do I do a calculation showing the kilometres travelled between each fuel entry. So I need a calculation field to deduct the last Odometer log entry from the current one to give the kilometres for each log entry.

        • 1. Re: Fuel Log kilometre between fills
          bigtom

          I am assuming you have a creation date or entry date for all the records so you can identify the latest entry before the current one is entered. Do you have this?

           

          What was I thinking? You just need the max distance.

          • 2. Re: Fuel Log kilometre between fills
            bigtom

            I would do this as a scripted process using global fields that get committed to a record when submitted. This makes it easy to grab the existing max value for distance to use in a calculation before the max is set to a new value.

            • 3. Re: Fuel Log kilometre between fills
              bigtom

              You can use Max ( ) from the parent table (maybe Vehicle?). You just need two variables and a subtraction ($$current_dist - $$prev_dist).

               

               

              I suppose the only global field you really need is for distance but it makes sense to do them all at the risk of not having a complete record.

              1 of 1 people found this helpful
              • 5. Re: Fuel Log kilometre between fills
                sherab4

                Not sure how to set up a variable for a previous record.

                • 6. Re: Fuel Log kilometre between fills
                  sherab4

                  I can see options for record numbers, next records, but not previous records.

                  • 7. Re: Fuel Log kilometre between fills
                    BruceRobertson

                    You need to know how to subtract 1.

                    • 8. Re: Fuel Log kilometre between fills
                      philmodjunk

                      There are similar posts on this already in the forum.

                       

                      Much depends one whether you are doing this for one vehicle or a fleet.

                       

                      Option 1 is to use GetNthRecord as an auto-enter calculation to pull the previous record's odometer reading into the field of the next new records so that you can subtract it from the current record.

                       

                      Option 2 is to use a self join relationship in order to auto-enter the same value. This option can be a bit more secure and can handle multiple vehicles more easily as  vehicle ID can be part of the relationship used.

                      1 of 1 people found this helpful
                      • 9. Re: Fuel Log kilometre between fills
                        CarlSchwarz

                        bigtom wrote:

                         

                        I would do this as a scripted process using global fields that get committed to a record when submitted. This makes it easy to grab the existing max value for distance to use in a calculation before the max is set to a new value.

                        That doesn't work when the odometer clocks over and goes back to the beginning.

                        Oh yeah, that's the truckie in me talking ( what do you call that in America, semi, prime mover, big-rig? ), the older ones regularly go over the million so if that is the case here it is worth handling now.

                        1 of 1 people found this helpful
                        • 10. Re: Fuel Log kilometre between fills
                          bigtom

                          CarlSchwarz wrote:

                           

                          bigtom wrote:

                           

                          I would do this as a scripted process using global fields that get committed to a record when submitted. This makes it easy to grab the existing max value for distance to use in a calculation before the max is set to a new value.

                          That doesn't work when the odometer clocks over and goes back to the beginning.

                          Oh yeah, that's the truckie in me talking ( what do you call that in America, semi, prime mover, big-rig? ), the older ones regularly go over the million so if that is the case here it is worth handling now.

                          You are correct. We usually get some WhiteOut and add a 1 to the left of the rolling digits. You can also have a flag in the vehicle record to add to 1M or 100k for older vehicles and get in front of the problem. This is also a reason I do prefer using the entry date as a reference and not the milage number. Sometimes you need to account for an office entering the data from receipts out of order or gross error in the milage entry.

                           

                          GetNthRecord works on the record order or the relationship sort order. As long as you pay attention to that it is good aside from weird things it can do with the first entry if sorted descending.

                           

                          • 11. Re: Fuel Log kilometre between fills
                            fmpdude

                            It's not clear from your posting if you know how to calculate the values with paper and pencil. This kind of programming assignment is one of the first programs I was assigned in Pascal using files to read and write results. It's one of those classic fun programming problems. In my case, we had to calculate fuel efficiency

                             

                            So, assuming you already know how to calculate results given the data, rather than use GetNthRecord, for simplicity, I would just add another field to each row where you write the last odometer reading. Then, it's simple to create a calculated field with the km difference.  Using that approach, it's also simpler to look at the data and, by inspection, verify its results.

                             

                            Plus, this extra-field technique is generic. GetNthRecord is FMP only, which may or may not be an issue.

                             

                            ------

                             

                            Re-reading your posting....

                             

                            Or, for future data collection, just .... SKIP THE OVERALL ODOMETER--> Just use the trip odometer between fill-ups. Avoid any of the "roll-over" issues. Plus, it's easier to have just the miles (and gallons of gas) since the last fill up. If you use that technique, there's also no subtraction needed. Using this technique you get the results you asked for automatically by the car's trip meter.

                             

                            -----

                            I would at least calculate miles per gallon (or liter or whatever) since you have all the data you need for that, too.

                            • 12. Re: Fuel Log kilometre between fills
                              sherab4

                              I can't see an option for Previous record in the GetNthRecord script options. Plus I have the portal sorted in descending date order, is this a problem.

                              • 13. Re: Fuel Log kilometre between fills
                                BruceRobertson

                                It's all numbers. You subtract 1.

                                If get( currentRecordNumber) is 57 then previous record is get( currentRecordNumber) - 1 .

                                That is; 56

                                • 14. Re: Fuel Log kilometre between fills
                                  philmodjunk

                                  I can't see an option for Previous record in the GetNthRecord script options

                                   

                                  You calculate it:

                                   

                                  Let ( R = Get ( RecordNumber ) ;

                                         If ( R > 1 ; GetNthRecord ( Odometer ; R - 1 ) )

                                        )

                                   

                                  This assumes that the record from which you want to copy the previous odometer reading precedes it in the current found set of record. This isn't always practical, so a self join approach can be a better option and is one that can also enable you to specify the vehicle so as to manage this data for a fleet of vehicles.

                                  1 2 3 Previous Next