4 Replies Latest reply on Sep 25, 2013 8:42 AM by philmodjunk

    Difference between field values in different records

    kyleschutter

      Title

      Difference between field values in different records

      Post

           Hi All,

           I am new to filemaker so bear with me. I have gone through all of the functions and many webpages and haven't come up with a solution.

           I have a table of meter readings with credit remaining on the meter and I need to subtract the previous meter reading from the current meter reading to know how much credit was used in a period. I have a table of 22,000 meter readings and need to know how much credit was used since the previous readings.

           It seems like there must be some kind of self reference solution for this.

           Additionally, I will need to exclude answers where the difference is negative or more than 2, but I can figure that out with an If statement.

           Thanks for your help.

           Example:

                                                                                                                                                                                                                                                                                                                                                                         
                          Meter ID                     Time stamp                     Balance                     Credit used
                          M0001                     time1                     5                     some calculation goes here
                          M0002                     time2                     3.5                      
                          M0001                     time3                     6                      

            

      Screen_Shot_2013-09-21_at_5.43.27_PM.png

        • 1. Re: Difference between field values in different records
          philmodjunk

               GetNthRecord can be used to refer to the preceding record in a found set or related set of records. A self join relationship can also be used to access say the preceding reading with the same meter number.

               What exactly do you mean by "excluded"? What result should be returned if the difference is negative or greater than 2?

          • 2. Re: Difference between field values in different records
            kyleschutter

                 PhilModJunk,

                 Thanks for your reply. What calculation would I write exactly to find the previous meter reading for a meter? I cannot figure out how to find a set of records unless I go to layout mode or write a script. It doesn't seem possible to find related records directly in a calculation field. Could you write an example calculation so that I can understand? thanks

                 The "excluded" values should be flagged so that the operator of the database knows that something went wrong. Probably something like:

                 If( result>2 OR result <0; colorRBG(255;0;0); result)

                  

                  

            • 3. Re: Difference between field values in different records
              kyleschutter

                   I tried using GetNthRecord ( meter; Get (RecordNumber) + 1) but it turns out that RecordNumber doesn't change when I do a sort (it is fixed according to the way I imported the data). I am now trying to get find a way to run a script that will number each record based on my sort order.

              • 4. Re: Difference between field values in different records
                philmodjunk

                     GetNthRecord is not fixed nor is Get(RecordNumber). They are based on the order of records in your found set. Perhaps you used an auto-entered calcualtion instead of an unstored calculation field?

                     

                          I cannot figure out how to find a set of records unless I go to layout mode or write a script.

                     There are many ways to access a specific group of records. I don't see how you can do that in layout mode at all, but a script is one option for finding records. But you can also define a relationship that matches only to a specific set of records, that's how you get a specific set of records to appear in a portal, for example and then there are a number of ways to work with that set of related records.

                     And in FileMaker 12, you also have the option of using the ExecuteSQL function to query your database and extract data from the results of that query.

                     

                          If( result>2 OR result <0; colorRBG(255;0;0); result)

                     I see that you want a color, but what VALUE do you want returned? The color can be done with a conditional format, it does not need to be part of this calculation. My best guess is that you either want a value of 0 returned if the value is outside the valid range or you want the next record to skip past this invalid value and compute a difference from the record that preceded the invalid one. But either is just a guess, I can't tell yet exactly how you want to compute a difference when the preceding value is invalid.