Hi Appsheet user community,
I have a fairly detailed questions involving user expressions.
I have two tables:
Table 1 (โRestaurant check in and out dataโ): Has a userโs emailโs (โEmailโ), a timestamp (โTimestampโ), and a location (โLocationโ).
Table 2 (โSheet1โ): Has a location (โLocationโ), a start time (โCartridge start timeโ), and an end time (โCartridge end timeโ).
What I am trying to do is build an bot that sends an email if four conditions are true.
Condition a) The email in table 1 matches up to an email I am storing in User Settings (under โEmailโ).
Condition b) The location in table 1 matches up with the location in table 2.
Condition c) The timestamp in table 1 is greater than or equal to the start time in table 2.
Condition d) The timestamp in table 1 is less than or equal to the start time in table 2.
You all probably have more experience with this then me, so if you have any expression suggestions, please write in and reply.
Solved! Go to Solution.
Hmmmm, ok, maybe I understand. Does this seem like what you need?
ISNOTBLANK( FILTER(
Restaurant check in.... ,
AND(
[email] = USERSETTINGS("Email") ,
[location] = [_THISROW].[location] ,
[timestamp] >= [_THISROW].[catridge start time] ,
[timestamp] <= [_THISROW].[end time]
)
) )
Hi @Eli1
Welcome to the community !
Did you have a look there ?
I think in the Event part, you need to set your conditions.
You may want to create a bot from a suggestion, and adapt it to your case.
Can you tell us what you tried so far ?
Thank you
Also, for reference:
Hereโs what I have.
IF(AND(CONTAINS(Restaurant check in and out data[Email], USERSETTINGS(โEmailโ)), Restaurant check in and out data[Timestamp] >= [Cartridge start time], Restaurant check in and out data[Timestamp] <= [End time], CONTAINS(Restaurant check in and out data[Location], [Location])), true, false)
Hi @Eli1
First of all, this kind of expression:
IF(condition,
true,
false
)
can be simplified by setting the condition directly, as it will return true or false itself.
So, in your case, your expression can be simplified like this:
AND(
CONTAINS(Restaurant check in and out data[Email], USERSETTINGS(โEmailโ)),
Restaurant check in and out data[Timestamp] >= [Cartridge start time],
Restaurant check in and out data[Timestamp] <= [End time],
CONTAINS(Restaurant check in and out data[Location], [Location])
)
You expression looks fine to me this way, are you having trouble at setting it in your event condition ?
Edit : Tip & tricks for copy-pasting expression:
Hi @Aurelien ,
The issue Iโm having is that I am getting this error:
Cannot compare List with Time in (Restaurant check in and out data[Timestamp] >= [Cartridge start time])
Thanks,
Eli
The listed expression checks against a List of all values in the first Table. Which value/record are you wanting to check against?
Sorry @Marc_Dillon , I am a little confused on your question. What I would like to check all values in a table to see if any rows have the characteristics described below:
The bot is running on a table called โPositive cartridgesโ.
It is using a data change. @Marc_Dillon
Hmmmm, ok, maybe I understand. Does this seem like what you need?
ISNOTBLANK( FILTER(
Restaurant check in.... ,
AND(
[email] = USERSETTINGS("Email") ,
[location] = [_THISROW].[location] ,
[timestamp] >= [_THISROW].[catridge start time] ,
[timestamp] <= [_THISROW].[end time]
)
) )
Hi @Marc_Dillon ,
I donโt think that that is working for my purpose. In this post (and the next one, just because I canโt attach two to a post), I attached a couple more photos, one of each sheet. Since a cartridge from PlaceA is in the Sheet1 table, I need it to send an email to that exampleA@gmail.com, since itโs Timestamp is between the Start and End time for the cartridge.
Thanks for all your help,
Nothing in your last reply indicates to me that my suggestion is not appropriate for your situation. Perhaps there is a misunderstanding somewhere, but I certainly canโt guess where.
I suggest maybe you review some help articles again about Bots and/or expressions. Maybe something will pop out and make sense, or at least enough to ask a more pointed question.
Maybe try this one?
Thanks @Marc_Dillon ! I believe it is working now.
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |