Searching Based On Conditions

Patrol Allow=

IFS(
COUNT(SELECT(Booting[Properties], MINUTE(TIMENOW()-[Time])>30)) = YES,
COUNT(SELECT(Booting[Properties], MINUTE(TIMENOW()-[Time])<30)) = NO)

Like so? Though I don’t see how it would connect the time of the previous entry to the time now or am I missing that? Does there need to be a “Thisrow” included?

Yes you must still use this row. I presume time is the time the event was captured. Sorry I am on holiday and have no access to PC.

This is what I have. It looks like it tests correctly but I could be wrong:

FS(
COUNT(SELECT(Booting[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])>30)) = YES,
COUNT(SELECT(Booting[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])<30)) = NO)

If it runs ok then I presume it is working. I cannot test but it appears ok. It will find any event time at the required row and find difference from current time. Based on difference it will return either yes or no. Hope it works ok. :+1:

The formula works, but for some reason, the column isn’t showing up in the app. When I take the formula out, the column appears, but when I put the formula in the column disappears. Strange? I saw in my original formula some issues so I changed it and the behavior is still the same.

IFS(
COUNT(SELECT(Security[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])>30)) = YES,
COUNT(SELECT(Security[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])<30)) = NO)

Hi Tirrel,

Try define the time column in the spreadsheet as text not datetime.

I presume patrol is also text ?

Sorry cannot test as noted.

Oh I am not sure the count() is required. This will just count all the yes / no. You just want al yes / no based on time difference.

Patrol must be text as it is an output.

Hi,

So I changed time to text as well. So should the new code be:

IFS(
(SELECT(Security[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])>30)) = YES,
(SELECT(Security[Properties], MINUTE(TIMENOW()-[_THISROW].[Time])<30)) = NO)

The way to do this is to first filter the last time entry for each and every property. This may be done by creating a list of the latest time for each property (PROP1-----PROPn)

LIST(
TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1) ,
TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1) ,
TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)
)

The LIST will therefore contain the last or latest time entry for each property in the order that the properties are listed as shown above.

Next we find the time difference between current time and the last entry for each property and we need to convert the hours to minutes and add to the minutes so we do the following;

LIST(
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1))) ,
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1))) ,
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))
)

The above code converts hours and minutes to total minutes in the current day to prevent any roll over problems each hour. However there is a problem when each day rolls over and for example 00H00 - 23hxx will yield a negative number. So we have to use an IFS to overcome this. When positive no change but when negative we must subtract 23*60+59 = 1439. So 00h10 - 23h40 = 1439 + 10 - 1419 = 40. so we get

LIST(
IFS(
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1))).>0,
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1))),
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))<=0,
1439 + HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP1” ,TRUE) , TRUE), 1))) )
,
IFS(
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1))).>0,
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1))),
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))<=0,
1439 - HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP2” ,TRUE) , TRUE), 1))) )
,
IFS(
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1))).>0,
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1))),
HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))<=0,
1439 - HOUR(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))*60 +
MINUTE(TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE), 1)))
)
)

This returns the correct time in minutes accounting for roll over to the next day.

To make this work the slice must include all values from 23h00 the previous day and all day for the current day and this will account for roll over every day.

The other problem is that once a PATROL is completed it might need to be reset and if required one may add another line to the IFS to reset or show PATROL complete.

So under 30 minute it shows NO PATROL from 30 to say 40 min it shows PATROL and after 40 MIN it shows PATROL completed.

It looks complicated but it should work and it would help if a script could be developed.

Cheers

Thank you so much for breaking that down for me!

So there has to be an option for Patrol completed under the attribute patrol? Then it would be a list not a yes or no correct?

You may use anything you so wish.

In my testing I just return a list called PATROL1 as shown below

Here I have used three properties (parking lots). One may use INDEX(LIST,1) to get the first and INDEX(LIST,2) to get the second etc and hence determine when and when not to patrol each property (Parking Lot) from this as the times for each property (3 of them) are in minutes

In this case the virtual variable is PATROL1 then

IFS(INDEX([PATROL1],1)<30,“NO PATROL”, AND( INDEX([PATROL1],1)>=30 , INDEX([PATROL1],1)<=40), “PATROL”, INDEX([PATROL1],1)>40, “NO PATROL”)

It works nicely.

Hi,

So Ive been hacking away at this, what is (Select[Park] (Time))?

Park is the Table I assigned as shown below. I have an app called PLAYAROUND which uses the google spreadsheet named PARKINGTEST and a worksheet called Sheet1

Hence select(Park…) refers to worksheet Sheet1 which has the following columns shown below one of which is Time, which is the time any vehicle enters any specific property or parking lot.

So select(Park[Time]) selects all rows in Column Time of spreadsheet Sheet1 which is named Park in the app and will return it as an array or LIST as shown below (heading excluded by APPSHEET)

image

You have used select([Park]Time) which is incorrect syntax as shown below

The way to test is just create a virtual column and use the Expression Assistant and its test feature to see what gets returned as shown below

When you select Test you get

As you see it returns all rows of Column Time

So one may use the Expression Assistant to incrementally build any expression and see what is returned at each stage of the expression being designed.

It would be great if APPSHEET had a separate Expression scratchpad where one may experiment but the above serves the same purpose to help debug any expressions. For example in the expression below TIME() must be used to convert to a time to allow subtraction of the time portions of TIMENOW() and TOP() the former is time and the latter datetime so one gets an error many of which are not documented so using the Expression assistant allows one to do incremental expression development and debugging. Unfortunately APPSHEET only shows the highest level data that is returned and not all the underlying levels so one has to do onion layer type expression development starting at the core layer.

Hope this helps.

TIMENOW() - TIME(TOP(SORT(SELECT(Park[Time], [Property]=“PROP3” ,TRUE) , TRUE)

Forgive my extremely late reply to this, I spent the past hour reading through your workflow and I am updating mine to match. Thank you for all of your help sir!

Glad it helped :+1: