Send email if a value does not exist in the column

[Mayank_Gupta]

Hi, I have created a Bot which sends email whenever a new entry is created through a Entry form.
Email should be sent only when the value entered in “Fedex Number” field does not already exist in “Fedex Number” Column of the table. Basically, email should be sent for only new Fedex Numbers entered. I tried the following condition to check if Fedex Number already exists in Fedex Number column or not:
IN([_THISROW].[Fedex Number], Eldon_Laboratory[Fedex Number])

But it is always returning True. i.e. it is always sending email irrespective of new or old Fedex Number. Can someone suggest what am I doing wrong.

[WillowMobileSys]

The Bot runs on the AppSheet server not on the device/computer (at least I think that is still the case for the type of Bot you are describing - Bots are very new!).

I think what may be happening is that the row is added to the table and THEN the Bot runs. This means that the new row will ALWAYS exist in the table by the time the Bot runs and you cannot depend on presence or non-presence of the row (i.e. Fedex Number).

The best way to handle this is to have an explicit column named something like [Send Email?]. It does not need to be shown but set the Initial Value to TRUE. Add a task to the Bot to re-set this value to FALSE after the email is sent and adjust the Bot condition to run when [Send Email?] = TRUE

When a row is saved, the initial value of [Send Email?] is set to TRUE triggering the Bot. The new task in the Bot re-sets this value to FALSE preventing any additional email being sent upon future edits of the row (unless of course you purposely set the value TRUE again!)

[Former Community Member]

@Mayank_Gupta you can also use the before/after values in your expression condition. See a similar question posted here.

[Mayank_Gupta]

Hi, thanks for your reply but this is not what I am trying to achieve. My entry will always be a new row but it may or may not have a same value as previous rows. I want to check whether it is a new value or not.

[WillowMobileSys]

prithpal:

@Mayank_Gupta you can also use the before/after values in your expression condition. See a similar question posted here .

Yes, this will work as well IF the Fedex Number never changes. However, what if the wrong number was entered and it needed to be corrected? You may want another email sent in that case but it likely should be tailored to the fact the number was corrected, meaning a DIFFERENT email Bot sends the updated message.

[Mayank_Gupta]

Hi, thanks for your reply but I am facing the same issue with creating a new Column. I created a new column and applied the same formula in it. Even that column is returning True always. Does it mean that it is considering my current entry as already in the database? If yes, is there any way to find a value only in the previous all rows i.e. rows excluding the current entry row

[Mayank_Gupta]

No Worries Mate, thanks for your replies. I found a way of doing this. I made a count of the rows having the same fedex number and if that returns 0, I am running the email bot. Its working now

[WillowMobileSys]

Mayank_Gupta:

I made a count of the rows having the same fedex number and if that returns 0, I am running the email bot. Its working now

Something’s not right. If your IN() function was returning TRUE that would mean the FEDEX number already existed in the table Eldon_Laboratory table. If you are getting a value of zero from a COUNT() function executed at the same place in your app under the same circumstances that would mean the rows DOES NOT EXIST in the Eldon_Laboratory table. This is a discrepancy.

@prithpal Is it true that Email generation tasks from Bots still run on the AppSheet server AND that the Bot would not be triggered until AFTER the row has been added to the table? I.E. the new row is added first and then a check is made to see what Bots/Workflows should run?

@Mayank_Gupta If my above question to @prithpal holds true, then I would not trust your COUNT() function, it should be returning 1 not 0. Also, I have just realized that we don’t have explicit details of WHERE you are physically implementing these expressions. I am assuming it is the Bot Event Condition to run.

[Mayank_Gupta]

Hey mate, even I don’t know how or why IN didn’t work but the COUNT worked. I don’t know if this can be the reason… Earlier, I was using IN function inside the Bot but now I have implemented the COUNT function while I am creating the Row from Form. So I think your logic that bot runs only after row is added is true

[Former Community Member]

@Dan_Bahir FYI

[Steve]

Mayank_Gupta:

IN([_THISROW].[Fedex Number], Eldon_Laboratory[Fedex Number])

[_THISROW]. should not be used here.

[Mayank_Gupta]

Thanks a lot for replying. Can you refer me a resource from which I can understand where we are supposed to use THISROW and where we are not

[Steve]

The simple rule is: don’t use it except within a FILTER(), LOOKUP(), MAXROW(), _MINEROW(), or SELECT() expression.

See also:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

[Mayank_Gupta]

Thanks a lot