5 Replies Latest reply on Jan 4, 2017 11:18 AM by philmodjunk

    Grab information from previous record


      I am creating a gas mile comparison table for our drivers, and to reduce human error, the miles is whatever the odometer says when adding gas at the pump ( and amount of gas added for full tank is how much was used previously). I made a new field in database for storing gas-miles comparisons, but I do not know how I can grab data from the directly previous record (since an automatically created serial key can errors, if an entry is deleted due to human error). what I need that gas-miles number to be is equal to: ($prev = previous entry miles)


      gas::gaspermile = (gas::miles - $prev) / gas::gas



      yes. I picked horrible names. I am bad with cars to begin with, and couldn't immediately think of better identifications for those fields.

        • 1. Re: Grab information from previous record

          This question seems awfully familiar. I seem to have answered this exact question just a few days ago.


          set up a number field to auto-enter the odometer data via auto-enter calculation. A simple, stored calculation field or auto-enter calculation can then compute the value you want here.


          Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecord ( OdometerReading ; R - 1 ) ; 0 ) )

          • 2. Re: Grab information from previous record

            I did see something in google about "getNthRecord" in a post, but it appeared to be unreleated at the time since I wouldn't think that "getNthRecord" would be useful. Thank you. After looking at what it really is, your equation makes total sense without explanation.


            I modified it to fit my needs, but I receive an error that an operator is needed somewhere within the GetNthRecord line. I tried creating a variable, the only 2 days I know ([ $prev = 0;] and [ prev = 0;] but those created errors, and I went to every link on several searches on how to do so (ignoring none from their title) to no correct answer (including removing "set variable" function from scripting which cannot be done in calculation.



            Let(R = Get ( RecordNumber );

            If (R > 1;(GetNthRecord ( Miles; R - 1 ) - Miles) / Gas; 0));




            (To me, that looks air tight from my programming experience. This is why scripting and calculations are difficult to grasp- despite "loads of classes" out there that are EXPENSIVE/cost).

            • 3. Re: Grab information from previous record

              Your If function is not correct. But I had a reason for not recommending this approach.


              What you have written only works as an unstored calculation and sets in motion a "chain" of calculations. If you create record 5000, the chain will reach all the way back to record number 1 and calculate values on each record in turn before you get to record 5000, this leads to very slow layout updates and also can produce calculation errors due to exhausting available system resources.


              Use the original expression to copy the odometer reading over from the previous record via an auto-enter setting, then refer to that field in your original calculation so that you now have a stored value that does not encounter this update issue.


              Let(R = Get ( RecordNumber );

              If (R > 1;(GetNthRecord ( Miles; R - 1 ) ) - Miles) / Gas; 0


              • 4. Re: Grab information from previous record

                I got it working using the calculation field instead of number field, so it ONLY calculates when it is created. I don't need it updating or being tampered with.

                • 5. Re: Grab information from previous record

                  At the moment that the record is created, you do not yet have a value for Gas. That was where it looked to me that your set up would break down if this is a stored or auto-entered value, but on further thought, I think that will update OK.


                  Do keep in mind that the "Previous record" is solely controlled by the current order of records in your found set. If you create a new record in the middle of the found set, it will copy from the record immediately above it so make sure of your current record before creating a new one.