Automation Filter Condition hits limitations

Patrick_Hodgkis · 12-02-2021 08:29 AM

I have a bot that runs daily to check if there are pending schedule changes to employee tasks.

I have a table of tasks and a child table of pending schedule changes to the tasks.

Today, my team scheduled 151 schedule changes and the bot never triggered. I can’t find any failure reason in the monitoring. From my research, I believe this is due to the internal limitation of the AppSheet automation and it being over the 5 minute run time.

If I manually limit the row number in the filter condition from the table, it will run.

If I try to use Top() formula in the filter condition it does not run.

IN([Key],
TOP(
LIST(Todays Schedule Changes[Key])
,10)
)

I can’t find any diagnostic information for why this isn’t running.

My thought was to take the Top 10 rows from a slice and schedule the bot to run every 20 minutes for a few hours.

I am approaching this problem in the best way?

Thanks!

WillowMobileSys

Are you looking in the Monitor page for log entries? It should show if the Bot triggered and failed there.

If it is a run time problem, I would propose two options:

151 rows is not that many. Maybe there is a way to reduce the processing needs so it runs faster. First thing to look for is any processing you can do at the time that updates are made during the day. This will spread the load out over time.
If feasible, run the process several times during the day. While “Daily” is the smallest frequency. You can create several Daily processes that run at different times but all access the same Process.

Patrick_Hodgkis

Thanks for helping John!

When I look at the monitor log it only shows the green complete for the bots that I ran with the _rownumber<XXXX limit. There isn’t a single error that is listed.

151 rows is not that many. Maybe there is a way to reduce the processing needs so it runs faster. First thing to look for is any processing you can do at the time that updates are made during the day. This will spread the load out over time.

I’m currently calling a process that looks up a key from the child table and pushes update.

If feasible, run the process several times during the day. While “Daily” is the smallest frequency. You can create several Daily processes that run at different times but all access the same Process.

This was my next step, but I can’t get the formula using the TOP() 10 to work in the filter condition.

WillowMobileSys

Is your app deployed?

Patrick_Hodgkis

It is.

Patrick_Hodgkis

I can get the Top() formula to work in a slice view without any issues.

If I use the slice that is incorporating the Top() 10 limit it doesn’t run.

When I use the 10 Limit slice, I set the filter condition to TRUE since the slice removes any unwanted rows.

WillowMobileSys

Have you checked the Audit Log? It will have a “Scheduled report rule - …” entry similar to this:

And then show Pass/Fail on the right-hand side like this:

Obviously, You want to search for this entry around the time the Bot should have ran automatically. NOTE: the times are not based on your time-zone. You’ll have to mentally apply the time offset for your zone.

Patrick_Hodgkis

I’ve got nothing on the original bot scheduled time. 12:05am my time and 6:05am UTC time.

Other scheduled bots triggered at their appropriate times though:

My scheduled bot doesn’t run until I manually trigger it with the filter condition [_rownumber]<XXXX

I can’t find a log where it triggers with the TOP() formula.

WillowMobileSys

Based on this comment I assumed it was a Bot that WAS working but stopped. So I guess I should ask…Did this Bot successfully automatically run in the past?

Patrick_Hodgkis

It was working, but it stops when there are more than 14 tasks queued. I played around with the row filter limit until it consistently ran. This leads me to believe that it is due to the run times.

WillowMobileSys

In these two statements, are Tasks = schedule changes?

I don’t know your data structure and what kind of updates are being performed by the Bot but being able to only process 14 or less “tasks queued” seems to be wildly inefficient - if it is indeed a run time problem. I think you may want to take a close look at the process flow to see where the bottlenecks might be.

What bothers me most is that I believe you should be seeing some kind of log entry of the “time out”. At the very least, there should some indication that the Bot attempted to run. The fact that you do not see any indication of that anywhere is a cause for concern. It make me think the Bot isn’t even attempting to run at all…meaning it never has the chance to “time out” in the first place.

The only time I have heard of this happening is when the datasource does not match between the Event and the Process. To see the table the Process is set for, you would need to open the Process in the Processes tab. Note the table used and make sure it matches with what is set in the Event.

Patrick_Hodgkis

Correct Tasks = schedule changes

I removed the text message notification action I was able to process 154 rows with sample data in the production application.

It looks like the “Call a process” is what is taking so long.

Is there a better way to accomplish this?

I agree, not being able to see an error code makes it difficult to troubleshoot.

I did verify that that the Event and Processes matched.

Resolution:
I’m going to assume that removing the text message step brought the action under run time limits. It did have some costly lookup functions in it as well.

Thank you so much for your help!

WillowMobileSys

One suggestion I can make is to unburden the automation by pre-setting as much of the information as you can in row updates during user activity in the app during the course of the day. Sometimes this might require actions attached to Form saves to update other tables. It may also require some type of summary table that kept updated as add and edits are made. Then your automation only needs to read results rather than stacking a bunch data collection and computation Tasks during the automation.