The situation. I have a APDB which Stores Closed Requests, the DB receives records when they are in an “In Progress” Status. Once the Request is filled and Closed the “Closed” record is sent to the APDB. I have a check box that once checked deletes the “In Progress” Record. I have been trying to create a bot, to Delete the “In Progress” record once the “Closed” Record hits the DB. I have not been able to get it to work.
I have an automation bot that is supposed to delete a row when a new, duplicate row is added. The bot’s SELECT() formula correctly identifies the target row’s ID when tested in a virtual column, but the delete action never happens.
We have already confirmed the following:
Table permissions allow deletes.
The data in all compared columns is identical.
The bot and delete action have been deleted and rebuilt from scratch.
The SELECT() formula below correctly returns a Row ID when tested in a virtual column.
Formula Used:
SELECT(
02 Expenditure Data DB[ID Column],
AND(
TRIM([Status]) = "In Progress",
TRIM([_THISROW].[Submittor's Name]) = TRIM([Submittor's Name]),
TRIM([_THISROW].[Expenditure Source]) = TRIM([Expenditure Source]),
TRIM([_THISROW].[Type of Expenditure]) = TRIM([Type of Expenditure]),
DATE([_THISROW].[Date of Purchase]) = DATE([Date of Purchase]),
TRIM([_THISROW].[Purpose of Expenditure]) = TRIM([Purpose of Expenditure])
)
)
Neither.
The Record is copied to the Expenditure DB from another DB with the Status already assigned as “Closed”
A Travel Claim is Approved and Completed.
That Travel Claim is then Copied to various places for historical purposes and also copied to the Expenditure DB to track Funding.
That is how is ends up in Expenditure DB
You’re leaving out a lot of details that are needed to troubleshoot effectively, and I’m not inclined to put in the effort to get you to share them. Ultimately, your biggest problem is that you don’t seem to understand how bots work.
You tell me what details you want, I will provide them.
I’ve not left anything out. That I am aware of.
I have a Bot from another App (CTS), which copies completed Expense Report(ER) Data to CETT (Which tracking various Expenditure Data) for funding Tracking.
All that works perfectly.
We then setup a Bot to Copy that CTS data to CETT when it was a Request and give it an “In Progress” Status. For Upcoming Expenditure Tracking.
What I am trying to accomplish, I thought was simple.
When the ER is closed, it gets sent to CETT, when the Closed ER is added to CETT, Delete the “In Progress” Record which is no longer needed.
All the formulas I have identifies the Two Records. But no matter what “I” manipulate to attempt to Delete the “In Progress” record. It will not delete.
I was thinking that I would like the same effect in one of my apps, but I have come to the conclusion that if you have a data change in the database that is caused from outside the application, a bot inside your app isn’t going to be triggered. So if you have a Bot that is “AppSheet database” event triggered, it will only fire when you make a change in the “Appsheet database tool” directly (you know, when you click the “View data source” button and it pops a new tab in your browser with https//www.appsheet.com/dbs/database/blabblahblah… and you see the tables, rows, columns, data). If another Bot from another app changes data in the same database (using the same table across multiple apps), it won’t trigger bots in different apps.
Essentially, cross-app data changes don’t trigger bots in other apps. The only way I found to make that work is to use a “Scheduled” bot - that uses “For Each Row in Table” for you Table and uses the Filter Condition as you need.
In you postings, you have a bunch of [_THISROW] so be aware the table you are working vs the table you are targeting.
Yep, I figured it out this morning.
I got it to work using a Schedule.
I appreciate your input.
Now the bot looks for the Status = In Progress and matches it with the Status = Closed with the Same “ID Column” Hourly and deletes the “In Progress” Row.
