Searching Based On Conditions

[blueocean]

Hi

I am relatively new to app sheet and the app I am creating is a parking app. Basically The app has a table called “Booting” and has the following columns:
Property (enum list)
License Plate (text input)
Flagged (yes/no)

I want the flagged column to be updated to yes or no based on the following condition: If A license plate occurs more than 2 times at the same property. This what I have so far:

“IF(COUNT(Select(Booting[License #], AND([Properties]=[_THISROW].[Properties], [Flagged]=[_THISROW].[Flagged]),False))>1,“yes”,“No Flag”)”

However I can not get it to auto fill the flagged category. Thanks for your help!

[gregdiana1]

This may be silly but you have used [Properties] and not [Property] and [Licence#] and not [Licence Plate]

Also use IFS but you have to repeat statement

IFS( COUNT(Select(Booting[License Plate], AND([Property]=[_THISROW].[Property], [Flagged]=
[_THISROW].[Flagged]),False))<=0,NO,
COUNT(Select(Booting[License Plate], AND([Property]=[_THISROW].[Property], [Flagged]=
[_THISROW].[Flagged]),False))>0,YES)

[blueocean]

Yea my mistake. The columns should be named:
Properties (enum List)
License # (text input)

So the code is correct in their labels but my column list wasn’t. My fault.

[gregdiana1]

I got it to work using the IFS but had to use 0 not 1. Count starts at 0 so if you use 1 it only flags after 3 time car enters any parking area.

If you use a virtual column using same count formula you may also keep a count of how many times the car has used any parking area.

[blueocean]

Wouldn’t a virtual column NOT show up in the back end on google sheets?

[Steve]

Correct, a virtual column is not recorded in the backend spreadsheet.

[gregdiana1]

Hi Tirrel,

You must define FLAGGED as Text and not Yes/NO otherwise it expects it as an input in which case no update. It is an input depending on other INPUTS so it is an OUTPUT.

I have defined Property, Licence Plate and Count as Columns in the spreadsheet as shown below

I then just added the same formula to the FLAGGED Formula as

Here is before

Here is after Save

Here is second entry and as you see FLAGGED is updated before saving

Here is after save

Hope this helps

[blueocean]

Hi,

So I have Flagged set as Text
I Have properties as enum
My result still does not auto update. What am I missing.

Below is my code relative to my app
FS( COUNT(Select(Booting[License #], AND([Properties]=[_THISROW].[Properties], [Flagged]=
[_THISROW].[Flagged]),False))<=0,NO,
COUNT(Select(Booting[License #], AND([Properties]=[_THISROW].[Properties], [Flagged]=
[_THISROW].[Flagged]),False))>0,YES)!

[gregdiana1]

I am using

IFS(
COUNT(SELECT(Park[Property], [Licence Plate]=[_THISROW].[Licence Plate], FALSE))<=0,NO,
COUNT(SELECT(Park[Property], [Licence Plate]=[_THISROW].[Licence Plate], FALSE))>0,YES)

You do not need the AND so

IFS(
COUNT(SELECT(Booting[License #], [License #]=[_THISROW].[License #], FALSE))<=0,NO,
COUNT(SELECT(Booting[License #], [License #]=[_THISROW].[License #], FALSE))>0,YES)

Should work.

[blueocean]

Shouldn’t it be
FS(
COUNT(SELECT(Booting[Properties], [License #]=[_THISROW].[License #], FALSE))<=0,NO,
COUNT(SELECT(Booting[Properties], [License #]=[_THISROW].[License #], FALSE))>0,YES)

I noticed you had License # 3 times instead of two so there was no reference?

[gregdiana1]

Yip you are correct sorry for the typo.

[blueocean]

Also, the expression works, but the column “Flagged” does not update to reflect this. Does it need to be a virtual column or does it need to happen on the back end?

[Steve]

You say the Properties column is of type EnumList. That suggests a single booting record might be associated with multiple properties. Can you explain how that works? Is a new booting row added each time a car is booted, or is an existing row reused if a car is booted multiple times?

[blueocean]

A new entry is added each time the car is booted. So the license # may be associated with multiple properties (Though unlikely), but the main this I am concerned about is repeat offenders on a single property.

[Steve]

If each row will only apply to a single property, the Properties column should be off type Enum rather than EnumList. The type determines how the proper expression is written.

[blueocean]

Adjustment made! Thanks!

[Steve]

Set the Flagged app formula to the following expression:

(
  COUNT(
    FILTER(
      "Booting",
      AND(
        ([License #] = [_THISROW].[License #]),
        ([Property] = [_THISROW].[Property])
      )
    )
  )
  > 2
)

[gregdiana1]

Steve, note that when one starts with a blank sheet count return zero so >2 will only work on the fourth and not second entry. The AND here is necessary as you use FILTER but not same for SELECT.

[blueocean]

So to both of you, thanks for your help. The code worked perfectly. I am setting up another one so this probably an easy answer but I want to make sure the behavior is correct.

If I have Properties as enum, and another column says “Allowed to patrol”, and I wanted to have a condition that said you could not patrol this property if the last entry was less than 30 minutes ago, wouldn’t it go like this:

Patrol Allow =
FS(
COUNT(SELECT(Booting[Properties], [Time Stamp]=[_THISROW].[Timestamp] - 30, FALSE))<=0,NO,
COUNT(SELECT(Booting[Properties], [Timestamp]=[_THISROW].[Timestamp] - 30, FALSE))>0,YES)

So Patrol allowed is the same style as “Flagged” from above, based on the condition that if the current timestamp is less than 30 minutes of the previous entry for this property, you will either get a yes or a no. In app sheet, is time measured by a straight numerical value as far as math goes?

[gregdiana1]

Rather use MINUTE(TIMENOW()-[Timestamp])<30 …>=

This uses difference between current time and last Entry.

[blueocean]

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?

[gregdiana1]

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.

[blueocean]

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)

[gregdiana1]

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.

[blueocean]

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)

[gregdiana1]

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.

[blueocean]

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)

[gregdiana1]

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.

[gregdiana1]

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

[blueocean]

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?

[gregdiana1]

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.

[blueocean]

Hi,

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

[gregdiana1 · Accepted solution]

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)

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)

[blueocean]

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!

[gregdiana1]

Glad it helped