Skip to main content
Question

How to calculate run time from hour meter reading

  • February 2, 2019
  • 0 replies
  • 700 views
I own a small utility company and have several operators check in plants. They record the day's meter readings (cumulative hour meters and water meters) in QuickBase. I need to calculate run time based on the previous reading compared to the current reading. I have dozens of facilities, hundreds of meters to read and thousands of reads. Tables: Facilities, Equipment, Reads. The readings are taken on random dates (no defined interval). I have the read table related to itself so that the previous read can be "selected". This has been working but I would like to be able to import a list of reads but I don't know how to populate the record ID required for finding the "previous" read. I think I'm looking for a way to dynamically/logically calculate the key field.
    This topic has been closed for replies.

    There are at least 3 easy solutions I can think of as your table are properly laid out.
    .
    One just uses summary fields.
    One uses summary fields and reverse relationships.
    One uses the new Combined text summary field and regular numeric summary fields.


    I will describe the first solution for now.  It does assume that the meter reading value is a numeric field.

    None of these solution involves that self relating relationship. (nothing wrong with doing that in Quick base, but for these solutions we do not need to have that Relationship.

    Here we go. One the Relationship for 

    One Equipment has Many Reads.

    Summary Maximum of the [Read Date] field called [Last Read Date].

    Look that up down to the Reads table.

    Summary Maximum  of the [Read Value] subject to the filter that the [Read date] = is equal to the value in the field [Last Read Date].  Call it [Last Read Value]. Note that there will only be one Qualifying Read so it can be a Summary Maximum, Minimum or Average.  They will all be the same result. 

    Now the Equipment automatically knows the [Last Read Value] so we are half way there.

    New summary of the maximum of the Read Date subject to the filter that the Read Date is less than the value in the field [Last Read Date].  Call it [Previous Read Date].  Look that up down to the Reads.

    New Summary Maximum  of the [Read Value] subject to the filter that the [Read date] = is equal to the value in the field [Previous Read Date].  Call it [Previous Read Value]. Again, note that there will only be one Qualifying Read so it can be a Summary Maximum, Minimum or Average.  They will all be the same result. 


    All done.

    Of course, from time to time the Meter will "roll over" so when you subtract your meter readings you may get a negative result and will need to know how many digits in the Equipment Meter in order to calculate the difference.
     
    • ChuckPeterson
    • Author
    • Registered
    • February 3, 2019
    Thank you so much for your reply. Your explanation is how I had my table setup prior to moving to the reverse relationship solution. Some issues that I had at the time were that the Added Read (Record) would become the new maximum when saved...resulting in run time = 0. I had to use snapshot fields to preserve the read. It got confusing because the calculation would work prior to saving. The operator could see the run time. When the record was saved the calculation would change (because of the new Max). I had to create conditions to alter the view depending on if it was a new record, edit, view. This is when I changed to the reserve relationship solution. I also had issues with how the mobile app would process the New Record routine compared to the Desktop Version. This was many years ago.

    The operators are using the mobile app and many of our locations are in areas with poor/no connectivity. I am going to collect the reads with an app that can be used to collect the data. I will need to import the reads into the reads table. The problem is that I won't know the reverse relationship Record ID of the previous read.

    I can export a list from the equipment table. It will have the [Equipment ID] and Summary Fields [Last Read][Date of Last Read]. The operator can input the current read. I can then Import the data into the Read Table. I will have the equipment ID#, Read Date, Read Value. I won't have the Record ID# to populate the [Related Read Field] (Reverse Relationship)


    If I could use logic to know what the Record ID# would be maybe there is a solution?

    Thank you again.
    My solution should work for your needs. No snapshot fields required.
    • ChuckPeterson
    • Author
    • Registered
    • February 3, 2019
    OK Thank you. I will go through it in detail. It will be great to have this working!


    • ChuckPeterson
    • Author
    • Registered
    • February 3, 2019
    The State Requires us to log all of the reads and the corresponding run times. We also use the run times for diagnostics. If I understand your explanation, equipment will always know the last read and the previous read (of all the reads). Those values change as  subsequent reads  are added. I lose the interval run time history. That is why I added snapshots. I wil
    OK, I did not understand that you needed all the run times and not just the current one.  Let me think some more about this.

    My initial thought is to calculate the 2nd most Recent Read Date in the summary relationship a summary and then calculate the corresponding Record ID#, and then look that up down to the Read Records and snapshot that field.

    Then use the snapshot field for the self related relationship to the previous read.  
    Terms & ConditionsAccessibility statement