Since October last year, our simple app which was created to let the end user know of a new job has failed to work. The app should be triggered upon the addition of a new job, specifically when the priority field has been completed (“ISNOTBLANK([Priority])”).
No changes to the architecture were made at that time or had been prior and the Twilio account is in credit.
Creating a case with AppSheet’s support has been a test of great patience and involved inaccurate information received from the engineers’ side along with the random closing of the enquiry because they emailed the wrong account with an update. No substantive update has been received for some weeks now, only the automated ‘your case is important to us, please be patient.’
No errors are reported by AppSheet at the time or on an audit log and Twilio is not detailing any issues on its side. Recently, however, I have established that whoever is working on the app behind-the-scenes is able to trigger the sending of the SMS but when I try the same, nothing happens.
Can you provide us with what you have implemented for the SMS automation? I assume by your comment “Twilio account is in credit” that you have updated the SMS Bot to use a personal/company account.
Additionally, have you reviewed the Monitor Logs to see if anything is logged there? If nothing logged there, then there is an issue with the Bot triggering and need to look at Bot Event criteria.
If there are logs, what do those log entries indicate - success or failure and what details.
If success, do you find the SMS message attempt logged on the Twilio side (I don’t use text messaging so I assume Twilio has logs)?
Yes, the Twilio account is for a business and the account token and SID are correct.
The bot is triggered with the app as the event source with information manually added to the spreadsheet input and the table is correctly referenced. Adds, updates and deletes are all enabled. The condition to prevent multiple messages being sent is as noted in the original post. There is a slice in operation which filters out all completed jobs and therefore shows only tasks which are not yet marked as complete, switching between that and the table does not change anything.
Twilio logs show that SMS have historically been delivered, not a single failure is reported on that side. For context, testing/debugging at this point is using my personal number which has been verified on the same platform. Both other users also have their numbers verified.
The app’s automation monitor indicates that the last successful initiation of the bot was Friday 13th. I cannot do a deep dive into older runs (I was hoping to identify activity around September-October last year).
One other place to look is in the Audit file. When Bots are not triggered, there will be an entry in the Audit Log file noted as “false” meaning the Bot criteria was not met for triggering. See image below. Then find the row entry for that user just immediately preceding and check its details to see if the Bot criteria should have been met.
If all the below all checks out then it seems ApSheet Support needs to get involved.
This, “ISNOTBLANK([Priority])”, if it is the Bot trigger condition, will not prevent multiple messages from being sent. While it will trigger ONLY when that column is filled in, the Bot will attempt to send again if any additional changes are made to that row - unless the Priority column is subsequently made blank. Is that your intention?
The above criteria would cause multiple messages for which there would be additional Twiliop message fees.
If the multiple messages is not your intention, a more appropriate expression might be: AND( ISBLANK([_THISROW_BEFORE].[Priority]), ISNOTBLANK([_THISROW_AFTER].[Priority]) )
The expression above will ensure the Bot is triggered ONLY when the Priority column has FIRST been set and not again unless it is blanked out and then set again.
Thank you. I have just received a couple of SMS moments before receiving an email confirming a task completion from the same app. It seems that the new job notification has been merged in the background, definitely not by us, with the job completion bot. It’s something that I noticed yesterday when looking at the audit logs and at the time thought it odd but let it pass as I thought that mentioning it might confuse matters.
Negative. It looks like I didn’t make it clear enough that it seems that the specific trigger for the SMS has somehow been merged with a completely independant bot/task.
It now looks like there are two bots involved. The first is the SMS notification which should be sent upon a new job being created, the second, i.e., the one that may have been merged into, is triggered when a job is marked as complete, the trigger for that being in a different column which is completely independent of any other, as they all are.
Due to the limitations of the account/AppSheet in general I am unable to go back six months or so (prior to the fault being identified) and confirm that they were once upon a time separate so this is just an idea; however, it does look to have weight given that I’ve just created a dummy job and received an SMS only when the job was marked as done, not when it was created, as it should be.
There is no merging of Bots. If you are referring to how the Audit log entries are listed, that’s just how each Bot is checked to be triggered when a data change is made
When a data row is entered or edited, that EXACT SAME data row result is compared against EACH Bot that operates on that type of row. In your example Audit Log entry above it is reflecting that two separate Bots were triggered and successfully completed. I can only guess that they both are designed to run against the same row type by their logged times. They don’t have to be Bots running on the same row depending the editing that has happened.
Another possibility is that you have the “Trigger other Bots” option turned on which means if the current Bot changes the data row - that changed row is again checked against the Bots to see if they should be triggered on the NEW row state.
******************************
Back to the SMS messaging not being received…The new job notification Bot indicates it was able to successfully execute the SMS message step in the Bot. The next thing to check is the Automation Monitor page - there should be an entry corresponding to that Audit Log entry row for “New Job notification” that will have more details about the running of that Bot. Do you see such an entry and what are the details?
Thanks for the further input. I decided to create a brand-new test app, input table, and bot to work through the problem myself. Having very quickly built a basic version, I tried the same trigger condition to see if it was a problem with the original sheet. It was not, no SMS was received upon updating the sheet, so I tried reconfiguring the columns and choosing a new triggering condition. Again, no joy.
I then started thinking about why the original app sends an SMS when a job is completed. While I remain confused about that, it occurred to me that way back when I first started trying to diagnose the problem there was an issue between myself and the engineer in describing certain elements of AppSheet’s UI, specifically what is now called the Event source. With nothing to lose, I changed the triggering column’s tickbox to editable - it was and always has been uneditable so users couldn’t ‘dope’ the app and make tasks more or less urgent according to their whim - clicked on a job in the desktop version of the app, made an edit in the triggering field and voila, the process works. Well, it works at the time of writing, I make no guarantees for next week…
Embarrassingly simple, yet AppSheet’s engineers have not picked up on it. It does require a bit more interaction in the office and the ideal solution would be for the sheet to be updated and that initiate the SMS but it does more or less work, albeit clunkily.
If the fire-upon-sheet-update could be solved and for the completion of the app to only send an email, that would be perfect; however, a win is a win I suppose.
A column that is explicitly not editable is read-only. Since a bot triggers on column changes, and that column was explicitly not changeable, there would never be a change to trigger the bot.
Note: I’ve not followed this thread in depth, so I’m probably missing details.
You are of course right, Steve. The thing is, it worked using that principle until last autumn, this proven by the Twilio logs which detail successful sending of SMS since inception until October 7th. I only learned about the problem when one of the mechanics asked why they weren’t receiving SMS and were having to check the app manually.
So, I now have a solution to update the mechanics when we load in a new job, but when they mark it as complete they will receive an SMS telling them to check the app because it has a new task for them which is fundamentally untrue. If there’s a way to stop this, please let me know.
I am not sure what you mean by “because it has a new task”.
However, if you don’t want the SMS Bot to trigger when the job status is marked “Complete” then you can update the Bot Trigger criteria to include something like:
AND( [Status] <> “Complete”, <<rest of your trigger criteria>> )
A side note….maybe a helpful hint, often times it is difficult to prevent unwanted triggerings of a Bot because multiple changes are being made back to back, say with a series of actions, and the row hasn’t yet reached a state that would PREVENT some of the extra triggers.
Once way to avoid that is to use a dedicated trigger flag column - for example “Send SMS?”. At the end of a series of row changes where it is known the SMS is to be sent, the flag is set to TRUE and then IMMEDIATELY set back to FALSE. The Bot is changed to trigger ONLY when [Send SMS?] = TRUE.
The setting of the flag and immediate resetting guarantees that no other edits will slide in and cause duplicate triggering of the Bot.
