I have implemented an Automation Bot that is on a schedule to fire off an email @ 8PM daily consisting of a template I made for our Carryover loads. I want the condition to only fire off the email if there is an order that has a [Pickup} = TODAY(), and the [Status] = “CARRYOVER”. But unfortunately, when I attempt to write the expression for this, the Expression Assistant is telling me that column values are not available. Both [Pickup] & [Status] are columns on my spreadsheet that I need to filter in order to acheive this. Can anyone point me in the right direction?
Try it this way:
AND(
IN(TODAY(), Table[Pickup]),
IN("CARRYOVER", Table[Status])
)
Change “Table” to the name of your tables
2 Likes
The Expression Assistant accepted the formula, but it still fired off the email even though there are currently no orders that have [Status] = “CARRYOVER”. Not sure if maybe I need to add additional statements to narrow down the filter or what?
How many rows can be with that status and with today’s date at a time?
You may want to use ForEachRowInTable and change the expression to:
AND(
[Pickup]=TODAY(),
[Status]="CARRYOVER"
)
2 Likes
I previously had it set ForEachRowInTable and the app was sending multiple
emails. (1 for each order that matched the filter) So when I had 3
“CARRYOVER” orders, it was sending 3 emails. I need for the app to only
send 1 email when scheduled.
For firing just 1 email, you could try the scheduled BOT filter condition something like below
IN( [Table Key],
TOP(
SELECT( Table Name[Table Key],
AND([Status]=“CARRYOVER”, [Pickup]=TODAY())
)
,1 )
)
Assumption: All these columns [Status] and [Pickup] are in the same table.
Please do enable ForEachRowinTable setting in the BOT.
Your report template can be something like
<<START: SELECT(Table Name[Table Key], AND([Status]=”CARRYOVER”, [Pickup]=TODAY()))>>
| Column 1 | Column 2 | Column 3 | … | Column n |
|---|---|---|---|---|
| <<[Column 1]>> | <<[Column 2]>> | <<[Column 3]>> | … | <<[Column n]>> |
<< END >>
2 Likes