PowerApps potential delegation area -- and how to operate

I've been experiencing a sneaky error that I am having trouble nailing down.

It all starts with this command:

       // Begin by building the Collections needed to feed the email tool.
          ClearCollect(MailingListExploded, AddColumns(ScanDataCollection,
            "CustomerName", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name1),
            "CustomerEmail", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Email),
            "ManagerName", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name2),
            "ManagerEmail", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Email1),
            "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & Mid(Result, 12, 11) & " - " & LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name) & "</li></strong>")
          ); // ClearCollect(MailingListExploded

Usually it works, but lately, it sometimes doesn't. Meaning that it can't find Result. I have confirmed that Result is a proper value in ScanDataCollection, but when the ClearCollect function runs, that result sometimes works and sometimes doesn't.

So suddenly I think to look at my dataset that contains the table Spiderfood_RITMData and coincident with the time we started seeing errors, that table started growing in size. Right now, it's around 2900 records. The count will get higher over time. I have reviewed the database and I really can't make that dataset any smaller, as this is a list of "open orders" and those had been slim for the past year on account of COVID, but will be climbing up further moving forward.

I check the database (an Azure SQL database) and the records it should be finding are right there.

Question 1: Am I seeing a situation where PowerApps just can't look through the whole dataset?

Question 2: If so, in your opinion, what would be the best way to fix this?

I am sufficiently new to PowerApps that I would appreciate maybe a teensy bit of handholding.

Thank you kindly,

Edward

EDIT: If need be, I can put this data in a different source. I have control over this data and the app.

EDIT 2: I am almost entirely certain that this is a delegation error. I am attempting to use a different way of pulling data using the IN operator, but it still seems to indicate a potential delegation error and doesn't seem to be retrieving the data, either. This is the code I am trying, but without success:

       // Begin by building the Collections needed to feed the email tool.
          ClearCollect(MailingListExploded1, AddColumns(ScanDataCollection,
            "CustomerName", LookUp(Spiderfood_RITMData, Mid(Result, 12, 11) in Number, Name1),
            "CustomerEmail", LookUp(Spiderfood_RITMData, Mid(Result, 12, 11) in Number, Email),
            "ManagerName", LookUp(Spiderfood_RITMData, Mid(Result, 12, 11) in Number, Name2),
            "ManagerEmail", LookUp(Spiderfood_RITMData, Mid(Result, 12, 11) in Number, Email1),
            "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & Mid(Result, 12, 11) & " - " & LookUp(Spiderfood_RITMData, Mid(Result, 12, 11) in Number, Name) & "</li></strong>")
          ); // ClearCollect(MailingListExploded

I presume I am coding it wrong. How is the better way to use the IN function to create this Collection?

Solution

Okay, so this is what I've learned so far...

Because I am using a SQL Azure database, certain functions are delegable.

However, the mid-stream calculations of MID seemed to be causing the trouble. At least as far as I can tell so far.

So I extracted the MID up a level, adding a new column called RawRITM, which was the calculated MID string. Then, in the next lines, instead of using MID each time, I simply used RawRITM from the new collection.

Like this:

       // Begin by building the Collections needed to feed the email tool.
          ClearCollect(ScanDataCollectionPlus, AddColumns(ScanDataCollection, "RawRITM", Mid(Result, 12, 11)));
          ClearCollect(MailingListExploded, AddColumns(ScanDataCollectionPlus,
            "CustomerName", LookUp(Spiderfood_RITMData, Number = RawRITM, Name1),
            "CustomerEmail", LookUp(Spiderfood_RITMData, Number = RawRITM, Email),
            "ManagerName", LookUp(Spiderfood_RITMData, Number = RawRITM, Name2),
            "ManagerEmail", LookUp(Spiderfood_RITMData, Number = RawRITM, Email1),
            "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & RawRITM & " - " & LookUp(Spiderfood_RITMData, Number = RawRITM, Name) & "</li></strong>")
          ); // ClearCollect(MailingListExploded

       // Trim away the Result column
          ClearCollect(MailingListExplodedTrimmed, DropColumns(MailingListExploded, "Result", "RawRITM"));

That seemed to get rid of all the delegation warnings. We'll see how the fix fares in production.

Additionally, I used this same trick to fix another half-dozen delegation errors in my code. So, thankfully, at this point, my app has zero delegation warnings.

Thanks to Matthew Devaney, who gave me the first hint on the MID calculations.