5 Replies Latest reply on Aug 11, 2014 9:31 AM by philmodjunk

    specifying advancing numbers



      specifying advancing numbers


           Hi all, 
           I am having trouble with calculating the monthly advance of electricity  meters. I have used a portal link to separate table. I type in the monthly meter read and this is saved as a record on the separate table, I then enter a new read each month. My problem occurs when I want to work out the advance between the 2 most recent entries. I cant find a way of telling the calculation to look for the most recent and the second most recent entries in the table (and then subtract the second from the first). I am hoping that someone can point me in the right direction...again, it would be much appreciated.
           Many thanks for your time,

        • 1. Re: specifying advancing numbers

               Do you have this set up in your tables and relationships. (but with your table and field names in place of mine...)


               Meters::__pkMeterID = Readings::_fkMeterID

               (For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained)

               If so, then a calculation field in Meters can be defined as:

               Let ( c = Count ( Readings::_fkMeterID ) ; Last ( Readings::Reading ) - If ( c > 1 ; GetNthNumber ( Readings::reading ; c - 1 ) ; 0 ) )

          • 2. Re: specifying advancing numbers



                 Thank you very much for helping me, 

                 I have copied the formula as described replaced the tables and field names with mine, at first I got the error message saying that GetNthNumber was not a function - So i changed it to GetNthRecord - which well, put it simply didn't work. It found the first entry (record) but then always stayed as the first entry rather than the last??


                 Any other suggestions? You seem to be very knowledgeable, should I be doing this another way?



            • 3. Re: specifying advancing numbers

                   That should be GetNthRecord and with that change this should work.

                   Instead of "putting it simply" please describe HOW it didn't work for you. Explaining your exact implementation of this and how it failed can help us get you on the right track.

              • 4. Re: specifying advancing numbers

                     Thanks for looking into this Phil, I have made a few variations to my layout so this function is no longer required, but thanks for your input. 


                     I am however stuck on another issue, which I have searched for on the forum and you seemed to have been able those in a similar circumstances. I must apologise for my ignorance with Filemaker, I am very new and unfortunately have had this project thrust unto me by the powers that be, along with a deadline, so I haven't had the time to really get to grips with the functions or scripting. So if you are able to help, please can you dumb it down to the most basic level.

                     I currently have 1 table with the following fields

                     - ID

                     - Meter Serial number

                     - Meter Location 

                     - Current Read

                     - Previous Read

                     - Advance


                     The ID is the Primary Key, The meter serial no is a unique number to the meter, and the Meter location is one of 9 areas in a factory. I would like (if possible) to have the Meter Location field as a "search" or "filter", so the client selects one area, from a dropdown box or similar, then only the records that have meters in that "area" would be displayed. So the client selects "Area 6" and only the records from area 6 are displayed. If you could help I will be most grateful!


                     Thanks again.

                • 5. Re: specifying advancing numbers

                       I saw your other post on this question and the advice provided by another forum member is the advice that I would have posted here.