Trouble with date/time range on bot execution

[Patrick_Paul]

I have an app that brings in training class data from an outside source.  It has a field that contains dates and times for each class in an unusual format.  I extract the dates into one virtual column, [Virtual Date], and the times to another [Virtual Time].  Each row in the class schedule table is related to a store record.  The store table has a "Related Virtual Classes" list field that compiles all the related classes.  All of this works as expected.

I have been attempting to create a bot/process that will email the manager of a location when the date/time indicated for the class is one hour away.  (The times are always at the top of the hour)  I cannot manage to get it to send the one-hour-prior reminder email at only one hour before.  Everything I try either does nothing, or sends it out 1 hour before *and* at zero hours before (the time of the class), which is a no-go because Reasons.  

Currently I have the EVENT configured as: Hourly, Central Time (time zone all the classes use), for each row in table (because I need to check every store in the company for possible upcoming classes), condition:

isnotblank(Select([Related Virtual Classes][_RowNumber], and([Virtual Date]=today(),abs(totalhours(([virtual time])-timenow()))<1.00,abs(totalhours(([virtual time])-timenow()))>0.00)))

Weirdly, it will fire off the email even when the value is zero, which *should not happen*.  I have the template set to provide the results of the calculations above for debugging purposes.  I cannot figure out why.  I have tried all sorts of variations on the condition like <=1.00 and >0, just making it =1.0, nothing does the trick.  Ideas?

[Suvrutt_Gurjar · Accepted solution]

---

@Patrick_Paul wrote:

isnotblank(Select([Related Virtual Classes][_RowNumber], and([Virtual Date]=today(),abs(totalhours(([virtual time])-timenow()))<1.00,abs(totalhours(([virtual time])-timenow()))>0.00)))

---

You may want to consider following options.

There maybe more issues that need to be resolved for the reasons I will mention below. But one thing you may want to try is removing ABS() wrapping. Due to ABS() wrapping, I believe  if the time now is 5:00 PM , the records having time stamp before 6 PM as well as well as records after 4 PM will be included in the expression. 

A simplified expression equivalent is below for explanation purpose.

ABS( 6 PM -5 PM)= 1 and ABS (4 PM-5 PM)=1  Actually 4PM-5PM = -1 but because of of use of ABS(), it will disregard the negative sign and include it in the bot eligible records.

Some other points to note are 

1. Bots may not fire at the exact instance, especially bots schedules at the round hours such as 4:00 PM 4:30 PM because they may compete with other bots.  Please go through the section below in the referred help article. This can especially be matter of important time difference for hourly bots because a time difference of even 5 minutes is 8% time difference.

Understand bot scheduling and retry - AppSheet Help

2. Since the bot records selection window is also one hour in your expression a record that has time entry just one minute above the expression window will also fire, thereby giving the user notice of just 1 minute. In any case, since the record selection window is 1 hour, the "play" in the user receiving notification will also be 1 hour.  So it may be better to reconstruct the expression as below

isnotblank(Select([Related Virtual Classes][_RowNumber], and([Virtual Date]=today(),(totalhours(([virtual time])-timenow()))<1.50,(totalhours(([virtual time])-timenow()))>0.50)))

So the user will receive notifications anywhere between 1.5 hours to 0.5 hours before.

[Patrick_Paul]

Removing the absolute value and "trimming" the range logic fixed the issue, thank you!

[Suvrutt_Gurjar]

Thank you for the update. May we request you, if possible to post the final expression that is working so that it may help any future reader of the post thread?

[Patrick_Paul]

Here we go:

isnotblank(Select([Related Virtual Classes][_RowNumber], and([Virtual Date]=today(),(totalhours(([virtual time])-timenow()))<1.10,(totalhours(([virtual time])-timenow()))>0.75)))

[Suvrutt_Gurjar]

Great, thank you.

[AleksiAlkio]

Would it be easier to calculate just the hour component from your time and then use that with the condition should the Bot trigger the email or not? If you trigger the Bot like few minutes after the full hour, it will always trigger it.

[Patrick_Paul]

Thanks for the suggestions.  I have been on other projects and I am now swinging back around to this problem.  I will try these out and report back.  Thanks!