6 Replies Latest reply on Feb 20, 2013 4:18 PM by philmodjunk

    Calculation script using data from previous entries

    beano129

      Title

      Calculation script using data from previous entries

      Post

           Hi all, 

           Right i've got a table for logging kms for a company car. 

           Each time the person logs somthing new he's adding the date and the reading of the odometer. 

           I want to perform a calculation per entry that will subtract the odometer reading from the previous entry from the reading on the current entry to give a total distance travelled. 

           I can't seem to find a scrip that will allow me to subtract values from previous entries from within the same field. 

           I'm sure this will probably be pretty simple! 

           Thanks all (Probably Phil) 

        • 1. Re: Calculation script using data from previous entries
          schamblee

               Abs(GetNthRecord(odometer;Get(RecordNumber)-1)-odometer)

          • 2. Re: Calculation script using data from previous entries
            philmodjunk

                 I think you'll need to tweak that one a bit:

                 If ( Get ( RecordNumber ) > 1 ; odometer - GetNthRecord ( Odometer ; Get ( RecordNumber ) - 1 ) ; Odometer )

                 And you may want to put some other value in place of Odometer--the value shown for the first record in your found set. You could use zero or just omit it to leave this field empty for the first entry.

            • 3. Re: Calculation script using data from previous entries
              beano129

                   This is excellent thank you very much everyone - i've eneded up making a very slight tweak to it and its now working exactly as I want it to. 

              If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( Odometer ; Get ( RecordNumber ) + 1 ) ; Odometer ) - Odometer

                   What i think I might need now, is to learn the language used - would getting a visual basis's book help me to learn some of the language or does filemaker have some of its own proprietry languages? 

                   If anyone knows a good book that would help me to learn some of the script language that might really help me rely less on this forum. 

                    

                   Thanks again all

              • 4. Re: Calculation script using data from previous entries
                philmodjunk

                     This is not visual basic so it won't help you at all to get a gook on that topic.

                     There are several books in print. You can research them on Amazon. I can't recommend any one book as the best book will depend on your prior experience with relational database design. Plus I am not personnally familiar with the contents of each.

                • 5. Re: Calculation script using data from previous entries
                  beano129

                       I just wondered if Visual Basis had any common language with FileMaker when it comes to calculation scripts in particular - i know that they are worlds apart but if there's a resource anywhere that can help me with this calcultion language i would love to get my hands on it. 

                       Thanks anyway way Phil

                  • 6. Re: Calculation script using data from previous entries
                    philmodjunk

                         There are numerous training resources available. Not only several book titles, but videos and other tutorials--even some on YouTube that you can investigate.

                         Forums like this are also a good resource, but the format makes them best suited for answering specific questions where the other resources are good for acquiring a general, "foundation" for your understanding of FileMaker and Relational Database Design.

                         And browsing through functions in FileMaker help often reveals new options/capabilities to the new developer.