7 Replies Latest reply on Jun 27, 2017 7:45 PM by gofmp15

    How to get data from prior record?

    acmeoutdoor

      Using FM 15, I made a simple fuel log to record gas purchases and calculate MPG. 

       

      I want to write a script that will get the value from the previous record's ENDING ODOMETER field (as determined by a RECORD ID field) and insert it into the BEGINNING ODOMETER field of a new record.    For example, if the newly created record has a RECORD ID of 17 I want the script to retrieve the ENDING ODOMETER value from RECORD ID 16 and insert that value into the BEGINNING ODOMETER field in RECORD ID 17.   The script I wrote however returns the ENDING ODOMETER value from RECORD ID 1 (the oldest record) and inserts it into the BEGINNING ODOMETER field of the newly created record. 

       

      The script I wrote is:

      Enter find mode [Pause: off] Perform Find [Restore]    ACTION - Find Records    CRITERIA - Fuel Log::RECORD ID:[>0] 

      Sort Records By Field [ Ascending ; Fuel Log::RECORD ID]

      Enter Browse Mode [Pause: Off]

      Go To Record/Request/Page [ with Dialog: Off ; Max ( Fuel Log::RECORD ID )] 

      Go To Field [Select/Perform ; Fuel Log::Odometer End ] 

      Copy [Select] 

      Go To Record/Request/Page [ Last] 

      Paste [Select ; Fuel Log::Odometer Start] 

      Enter Browse Mode [ Pause Off ] 

       

      What am I doing wrong?  Do I need a script or is there an easier method?

        • 1. Re: How to get data from prior record?
          ch0c0halic

          Use a lookup?

           

          Create a field previousRecord = RecordID - 1 //assumes the RecordID field is a number.

          Set up a relationship of previousRecord = RecordID

          Set up the beginningOdometer field as a lookup using this relationship and the endingOdometer field.

          • 2. Re: How to get data from prior record?
            philmodjunk

            Your script doesn't create a new record. Either you created it first and ran the script or you aren't getting a new record at all. Either way, your script doesn't work as it tries to go to the last record ( and you should just specify "last" rather than using the calculation option as your max function isn't likely to work in this context).

             

            But here's a simpler, non scripted method to look up the previous record's Odometer Reading:

             

            Set up the following auto-enter calculation:

             

            Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecord ( Previous Odometer Reading ; R - 1 ) ) )

             

            This assumes that you have either an unsorted found set (which will automatically list the records in creation order) or sorted by RecordID with the focus on the last record immediately before creating a new record in this table.

            • 3. Re: How to get data from prior record?
              gofmp15

              The first problem is that in using Get(recordid) to create a calculated field, you don't know if the highest record id exists or was it deleted.

               

              So, before creating your new record

               

              show all records

              sort by id field

              go to last record

              set variable $x to id

              new record

              set field y to $x

               

              there is another technique I'm tired. It involves getting the max value in the field and then searching for that...

              • 4. Re: How to get data from prior record?
                philmodjunk

                My suggested method does not use get ( RecordID ), but Get ( RecordNumber ) and there is always a Get ( RecordNumber ) equalt to 1 in a given found set.

                • 5. Re: How to get data from prior record?
                  gofmp15

                  The request was for the last record not the last one in a found set, which might be valid.

                   

                  A calculated field using Get(recordid) will always show the last record id no matter what client created it. It also will reveal missing records in the table which is how I proved to a client that records were deleted long before I was hired so get off my back about missing records... 

                  • 6. Re: How to get data from prior record?
                    philmodjunk

                    The original post states:

                     

                    I want to write a script that will get the value from the previous record's ENDING ODOMETER field

                    • 7. Re: How to get data from prior record?
                      gofmp15

                      ..."(as determined by a RECORD ID field)"

                       

                      I rest my case.