I created a Virtual Column in Expense Report Repository called “MeetsBotCriteria” This checks the records in Expense Report for Correct Status and Correct Airfare Cost
MeetsBotCriteria: << Works Perfectly
AND([Traveler] = [_THISROW].[Traveler], [Expense Status] = “Cancelled”, [Airfare Cost] > 1)
The Condition Expression works… BUT only reports that LAST record entered in Expense Report Repository.
If there are 4 records no matter the Traveler Name it is only reporting the 4th record.
Even though Tester Guy who just entered a Request has a “Cancelled” Report in record one… the expression is only looking at Tester 003 in record 4 (the last record) and reports back ‘False’.
If I change Expense Status of the Last Record to “Cancelled” it will report ‘True’ and send an email.
Condition Expression:
AND(
ISBLANK([_THISROW_BEFORE]),
NOT(
ISBLANK(
LOOKUP(
[_THISROW].[Traveler],
“02a Expense Reports Repository”,
“Traveler”,
“MeetsBotCriteria”
)
)
),
LOOKUP(
[_THISROW].[Traveler],
“02a Expense Reports Repository”,
“Traveler”,
“MeetsBotCriteria”
) = TRUE
)
@WillowMobileSys Any thoughts?
I assume the “Condition Expression” is used in the Bot’s Event Condition property. Can you describe what kind of Bot it is (DAta Change or Scheduled) and what it is intended to do?
If the expression comes back “True” it sends an email.
I can get it to send the email… but only if I make the last record a true Statement.
Ok, so…when you add a row to the “01 Travel Requests” table, you want to send an email …IF… the Travelers “Expense Status” = “Cancelled” and the [Airfare Cost > 1). Do I have that right?
Assuming “MeetsBotCriteria” does indeed have the expected value (more on this below) then the expression you would use in your Bot Condition would be simply:
LOOKUP([_THISROW].[Traveler],
"02a Expense Reports Repository",
"Traveler",
"MeetsBotCriteria"
) = TRUE
If the row is not present the result will be FALSE.
My understanding is that this is implemented as Virtual Column in the “02a Expense Reports Repository” table? If it is then I don’t think it’s returning the result you expect. This expression as written can only look at the current “02a Expense Reports Repository” row. What table does [Airfare Cost] belong to?
It seems you only need this criteria for the Bot to run. Assuming the [Airfare Cost] column is on the “01 Travel Requests” table, I think you could eliminate the Virtual column and perform the check in the Bot condition like this:
AND(LOOKUP([_THISROW].[Traveler],
"02a Expense Reports Repository",
"Traveler",
"Expense Status"
) = "Cancelled",
[Airfare Cost] > 1
)
I may have some of the details wrong. If so, let me know.
I hope this helps!
Thank you for your assistance… here is some more clarifying data.
You asked: “Ok, so…when you add a row to the “01 Travel Requests” table, you want to send an email …IF… the Travelers “Expense Status” = “Cancelled” and the [Airfare Cost > 1). Do I have that right?”
Mostly correct.
Row Added to 01 Travel Requests by Traveler 001
Bot goes and checks 02a Expense Reports Repository for Traveler 001 with a “Cancelled” Expense Report which has an Airfare Cost of > 1.
The > 1 doesn’t really matter, as long as it finds a Cancelled Status and an Airfare Cost for Traveler 001, it should return True and send an email.
If Returns a False… nothing happens and life is good.
In your final Condition It could not find [Airfare Cost] because it was looking in 01 Travel Requests vs 02a Expense Reports Repository.
I used the “Condition Expression” with ‘MeetsBotCriteria’
It didn’t work… Then I changed the last record to ‘Cancelled’ and it worked.
So… the issue is still… it is only checking or stopping at the last record in the table.
Ahh, I keep forgetting that the LOOKUP function doesn’t work when using the [_THISROW] qualifier. No clue why. And I would still put this all in the Bot Condition like this:
AND(LOOKUP([Traveler],
"02a Expense Reports Repository",
"Traveler",
"Expense Status"
) = "Cancelled",
LOOKUP([Traveler],
"02a Expense Reports Repository",
"Traveler",
"Airfare Cost"
) > 1
)
OR an equivalent expression would be:
ISNOTBLANK(SELECT( 02a Expense Reports Repository[ID Column],
AND([Traveler] = [_THISROW].[Traveler],
[Expense Status] = "Cancelled",
[Airfare Cost] > 1)))
I hope this finally resolves it!
This worked:
ISNOTBLANK(SELECT( 02a Expense Reports Repository[ID Column],
AND([Traveler] = [_THISROW].[Traveler],
[Expense Status] = "Cancelled",
[Airfare Cost] > 1)))
The “Lookup” would not work even though it gave a Green Check on the Expression.
Thank you very much for your assistance.
