datetime arithmetic

[kgrapes]

this seems fairly simple, which is why it's driving me to levels of insanity.

IFS(
[RECENTCOUNT].[DATETIME] > (NOW() - "000:30:00"), LINKTOVIEW("DROP CASH"),
[RECENTCOUNT].[DATETIME] < (NOW() - "000:30:00"), LINKTOVIEW("COUNT DRAWER DROP")
)

if you can help me understand why this expression does not function as intended, please let me know. the time does not calculate properly, always reading it as false. seemingly incapable of actually calculating the time, which makes no sense because from my understanding my syntax is correct.

recentcount is a virtual column referencing the most recent entry of another table 

[Aurelien]

Hi @kgrapes 

Your expression looks fine.

Did you try splitting your expression in small pieces to evaluate each part individually?

I would go for:

[test1] ===> [RECENTCOUNT].[DATETIME]

[test2] ===> (NOW() - "000:30:00")

[test3] ===> [RECENTCOUNT].[DATETIME] > (NOW() - "000:30:00")

 Also, can you try reversing the expression this way, for testing:

[test4] ===> ([RECENTCOUNT].[DATETIME] + "000:30:00") > NOW()

[kgrapes]

not having any luck still, interestingly if i change it from NOW() to TIMENOW() it reverses the resulting view, no clue why. have ripped this apart and broken it down in every way i can think of and it's simply not working. i havent been able to get any time expressions using durations to work. 

[Aurelien]

---

@kgrapes wrote:

i change it from NOW() to TIMENOW() it reverses the resulting view

---

This is because TIMENOW() will ignore the current date.

Have you thought about contacting AppSheet Support?

[dntrplytch]

Just a wild guess here - if the column is indeed named DATETIME, could it be conflicting with the the reserved type DATETIME?

[Joseph_Seddik]

---

@kgrapes wrote:

recentcount is a virtual column referencing the most recent entry of another table 

---

I don't believe you can correctly use a virtual column for that. Instead, build an automation to fill in the ref each time a new entry is created.

[AleksiAlkio]

May I ask where do you have this formula?  With an action?

[kgrapes]

yes, as an action. if they hit the button taking them to the view it should force them to count if no count has been added in the past 30 minutes, otherwise it will take them directly to drop. 

the issue trying to be solved, is that gsheets is being used so data synchronization is sometimes less than functional. in the instance they count, and go to drop and that drop page has yet to pull the information from their count (despite having forced sync written into the views) i need them to have the ability to hit "drop" again and be taken directly to drop without counting. there are several locations, each in one table. so they need to specify where they are. the original formula compared [recentcount] (maxrow from another table with conditions) with [recentuser] (maxrow from login table/same table action is used) which in my mind seemed more likely to work but did not. i think what was mentioned above- with virtual columns not really working here is the issue i'm facing.

i've just woken up and trying to think of how i can incorporate that data as a column in the login table is.... confusing. but i will try to play with that and see where that gets me.

[kgrapes · Accepted solution]

so, after exploring everything and nothing making sense i decided to see what would happen if i tried to actually view these references in the app. all references, but this reference were working. so i went back to my formula in the virtual column

MAXROW(CASH, DATETIME, 
AND(
 [LOCATION ID] = [RECENTUSER].[LOCATION ID], 
  OR([TYPE] = "COUNT", [TYPE] = "COUNT BEFORE DROP"),
   NOT([TYPE] = "COUNT BEFORE ADJUSTMENT")
)
)

it had some confusing syntax previously, so i simplified it to this. after doing that, everything is working as intended.

so we were all wrong! lol, it was just a bad formula in the ref to begin with.