Need help with expression setup

AppSheet AppSheet Q&A
1

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.

2

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:

1 Like
3

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)

4

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:

[Guide on How To Create a Community Post](https://community.appsheet.com/t/guide-on-how-to-create-a-community-post/35855/14) Tips & Tricks ?

Hi @LeventK I allow to make a humble contribution to this thread, on suggestion from @Suvrutt_Gurjar. You may want to integrate it in your first post ? It’s more a how-to-do than a how-to-think-and-behave though. For people who are willing to share expressions, indicate the tip to enclose their expressions with that: [image] and indent as well. So, for short comparison, this: IF(AND(condition1,condition2), resultTrue, resultFalse) would become: IF( AND( condition1, condition…

1 Like
5

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

6

The listed expression checks against a List of all values in the first Table. Which value/record are you wanting to check against?

[Referencing a Column value in a Table, versus referencing ALL Column values in the entire Table](https://community.appsheet.com/t/referencing-a-column-value-in-a-table-versus-referencing-all-column-values-in-the-entire-table/50771) Tips & Tricks ?

Having answered this question about 1 million times now, I guess I should make a tips-and-tricks post about it. Expressions of the form Table[Column] return a List type data value that contains the values in Column for ALL records in the entire Table. Most likely, If I’ve referred you to this post, the answer to your question is simply to change it to just: [Column] One of the most common places I’ve seen new app builders use this, is within SELECT() expression, like: SELECT( Table[Col…

2 Likes
7

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:

Eli1:

Condition a) The email in the row matches up to an email I am storing in User Settings (under “Email”).> Condition b) The location in row matches up with the location in table 2.> Condition c) The timestamp in row is greater than or equal to the start time in table 2.> Condition d) The timestamp in row is less than or equal to the start time in table 2.

8
  1. What Table is the Bot supposed to be running on.
  2. What kind of Event is the Bot using? Schedule or Data Change?
  3. If Schedule, is this a ForEachRow situation?
1 Like
9

  1. The bot is running on a table called “Positive cartridges”.

  2. It is using a data change. @Marc_Dillon

10

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]
  )
) )
2 Likes
11

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,

@Eli1

12

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?

2 Likes
13

Thanks @Marc_Dillon ! I believe it is working now.

2 Likes