8 Replies Latest reply on May 20, 2015 10:55 AM by gmaendel

# calculating time that a field has been TRUE.

### Title

calculating time that a field has been TRUE.

### Post

I have a table with a dateTime, pumpOn GPM etc. etc.

Lets say I have five Records...

dateTime-------------pumpOn---GPM

5/5/15 6:00:00 AM           0            800

5/5/15 7:00:00 AM           1            800

5/5/15 7:15:00 AM           1            800

5/5/15 8:30:00 AM           1            800

5/5/15 9:00:00 AM           0            800

How would I calculate how long the pump has been on? 1.5Hrs

I've tried searching google for anything on a calculation like this, but I'm not exactly sure what to search for.

thanks in advance for any help.

• ###### 1. Re: calculating time that a field has been TRUE.

You can set up a number field with an auto-enter calculation that uses the getNthRecord function to bring forward the value of the timestamp field of the previous record if the pump was on in that record and calculate the elapsed time in seconds. Then a summary field can total the values in this field to compute total on time for your pump.

Let ( [ R = Get ( RecordNumber ) ;
PoPrev = If ( R > 1 ; GetNthRecord ( PumpOn ; R - 1 ) ) ;
Ts1 = If ( PoPrev ; GetNthRecord ( dateTime ; R - 1 ) )
] ;
DateTime - Ts1
) // Let

• ###### 2. Re: calculating time that a field has been TRUE.

Thanks a lot Phil! I will try this tomorrow and let you know how I do.

I would've never thought of doing it that way, I'm relatively new to the Filemaker way of programming.

• ###### 3. Re: calculating time that a field has been TRUE.

Hmm on further thought, you might use a time field instead of a number field for this. That way, you can format your elapsed time as a time value with hours, minutes and seconds.

• ###### 4. Re: calculating time that a field has been TRUE.

Phil,

The calculation has a minor problem, even if there's only one record with the PumpOn bit set to true, it'll enter the timestamp of the current record, making the summary field out by a lot. this should be an easy fix by making sure that there's two sequential records with PumpOn bit set to true.

I'll test this tomorrow as it's getting pretty late.

Thanks again for the help.

• ###### 5. Re: calculating time that a field has been TRUE.

I think you just need to check the pump on status of the current record. It's already checking the pump on status of the previous record so you are right, that is a simple thing to add.

Let ( [ R = Get ( RecordNumber ) ;
PoPrev = If ( R > 1 ; GetNthRecord ( PumpOn ; R - 1 ) ) ;
Ts1 = If ( PoPrev and PumpOn ; GetNthRecord ( dateTime ; R - 1 ) )
] ;
DateTime - Ts1
) // Let

• ###### 6. Re: calculating time that a field has been TRUE.

Phil, Here's the problem I'm having.

On the first record it inserts the timestamp of the current record. I'm not sure why this is happening, it's supposed to minus the dateTime from itself which would make it zero.

• ###### 7. Re: calculating time that a field has been TRUE.

Actually, it's designed to copy over the time stamp. It's not defined to subtract this value from itself. It will subtract Null from itself.

Let ( [ R = Get ( RecordNumber ) ;
PoPrev = If ( R > 1 ; GetNthRecord ( PumpOn ; R - 1 ) ) ;
Ts1 = If ( PoPrev ; GetNthRecord ( dateTime ; R - 1 ) )
] ;
If ( PoPrev  ; DateTime - Ts1 )
) // Let

This assumes that the first time the status changes to "off" from "on", you want to capture one more time interval

• ###### 8. Re: calculating time that a field has been TRUE.

Perfect! that's exactly what I was looking for! thanks a million Phil!