Bot timing out? Get the just the first 100 records formula

[1minManager]

So I had a both that needs to send some SMS's to Twilio.  Issue is that its doing other stuff as well and takes approx 2secs per record.  So I need to limit it to just sending 100 each time it runs.  To make sure it can nvever go over the 300sec maximum bot run time.  So I setup a bot, running on a schedule with ForEachRowInTable turned on and with this as the Filter Condition:

[_RowNumber]<=INDEX(SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE),100)

Lets break it down bit by bit

Select(SMS To Send[_RowNumber],TRUE) 

SMS To Send is a slice where i define what rows need an SMS sending.   SMS To Send[_RowNumber] might have worked just as well.  So this just says "gets all the rownumbers from this slice" and make them into a list.  Note we are getting the rownumbers and not the key column.

SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE)

 Now we wrap that in SORT(...,FALSE).  This basically says "Put the list of row numbers in order, lowest number first".  E.g. 1 , 45 , 67 , 203 , 506...  Now normally the formula will pull them out in that order anyway.  But its best not to assume anything when creating formulas - always be explicit if can.

INDEX(SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE),100)

Next we use INDEX to say "From that ordered list of rownumbers, get me the 100th rownumber in that list".  

[_RowNumber]<=INDEX(SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE),100)

 This final bit says to the bot "Get all the records from that slice where the row number is less than or equal to the rownumber of the  100th record in the slice".  So my slice has 300ish records this will get records 1-100.

Then it got more complicated...

IFS(
   COUNT(SMS To Send[_Rownumber])=0,
   FALSE,
   COUNT(SMS To Send[_Rownumber])<=100,
   [_RowNumber]<=MAX(Select(SMS To Send[_Rownumber],TRUE)),
   COUNT(SMS To Send[_Rownumber])>100,
   [_RowNumber]<=INDEX(SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE),100)
)

A bit later I expanded on this to define what to do in senarios where there are either no records in the slice or you have more than 1 and less than 100.  So zero records returns false and the bot won't run.  >=1 and <100 says "Get the highest row number we have, then get me a list of that and everything with a lower row number".

Posting in case this might help someone 🙂

Simon, 1minManager

[dbaum]

---

@1minManager wrote:

running on a schedule

---

I have a similar use case where I need to break up processing of a large number of rows into batches to avoid automation time-out, and have been noodling on techniques. It sounds like you're using schedules to stagger initiation of each batch by maybe a few minutes apart to allow for one batch to complete before the next begins. Is that right?

Your nifty tip of using a slice to define the current batch suggests to me another technique. It ought to be possible to replicate the pagination concept used in many APIs. End the automation with a step that sets a value representing whether the current batch included the final row, and if that value represents that the final row hasn't yet been processed then that triggers the automation again.

[1minManager]

Hi @dbaum Not sure whether putting in some sort of loop counts as a seperate 300sec limit.  Never tested personally.

Probably using the API is quicker, but its not something I use in every app

[1minManager]

Another update to this...

I found that when your data set is quite large (>5000 rows) doing this in the Filter Condition of a Bot can be quite time consuming

INDEX(SORT(Select(SMS To Send[_RowNumber],TRUE),FALSE),100)

So instead I've changed the Bot to simply action all rows in this slice instead

AND(
   ISBLANK([SMS Sent]),
   [_RowNumber]<=INDEX(Select(SMS To Send[_RowNumber],ISBLANK([SMS Sent])),100)
)

How this works is the first row means the slice will return only rows which haven't been sent an SMS.  The second row then says only get the next 100 rows to send.  In my bot this dropped run time from 40ish secs to 6ish secs.  Reasons?  Two I think.  Firstly i think Appsheet gives Slices more computing performance than virtual columns or forumlas calcualted in Bots.  Also I've dropped the SORT() function since I assume that the slice is already in a suitable order, or I don't care what the order is.

Hope this helps 🙂

Simon, 1minManager.com