Delegation warning doesn't make sense

[sopatte]

    Set(Prefixes,
        Coalesce(
            LookUp(Metadata,
                Title = "Prefixes|" & MyFilter.Value,
                Data
            ),
            MyFilter.Value
        )
    );
    Clear(Cache);
    ForAll(Split(Prefixes,","),
        Collect(Cache,
            Filter(MyDataSource,
                StartsWith(Title, Result) // Delegation Warning
            )
        )
    )

This delegation warning doesn't make sense; StartsWith is delegable, and "Result" evaluates to a static string at runtime for each call to Filter. MyDataSource doesn't have a column named Result so there is no name collision. This seems like a bug.

[RandyHayes]

@sopatte 

You are looking at the ForAll as a ForLoop.  It is not, it is a function that returns a table of records.  In your case, it is going to return a table with multiple rows that have records with a Value column that will be complete copies of your Cache table.

What you are trying to do is not going to be delegable!

Is there any pre-filtering of the datasource that can be applied prior to filtering in your formula?

I assume that delegation and record limit are important in your app as you have surfaced this question.

[sopatte]

@RandyHayes,
Respectfully, in my mind, if the data passed to Filter is contained completely in the app prior to delegation, i.e. there's nothing to guess about at runtime, to pass up to the server and say, '[Hey server, you calculate this thing because Idk what it is at this time]", for example it includes a part of the record itself, then to say we can't delegate this comparison to a known string even though we can delegate to this other known string, that's a bug.

I came up with another case that illustrates my point even more clearly. 

Set(Prefix, SomeOtherVariable & "/");
ClearCollect(Cache,
    Filter(MyDataSource,
        StartsWith(Title, Prefix) // No problem
    )
);
ClearCollect(Cache2,
    Filter(MyDataSource,
        StartsWith(Title, SomeOtherVariable & "/") // Delegation warning
    )
);
ClearCollect(Cache2,
    Filter(MyDataSource,
        StartsWith(Title, "AB") // No problem
    )
);
ClearCollect(Cache2,
    Filter(MyDataSource,
        StartsWith(Title, "A" & "B") // Delegation warning
    )
);

No ForAll to confuse the issue here. These last Filter calls should be semantically identical. Quoting the delegation documentation: "You can also use portions of your formula that evaluate to a constant value for all records. For example, Left( Language(), 2 ), Date( 2019, 3, 31 ), and Today() don't depend on any columns of the record and, therefore, return the same value for all records. These values can be sent to the data source as a constant and won't block delegation." "A" & "B" is such a case. This is a bug. This one can be worked around, but it's still a bug.

Also, nothwithstanding it being a function that sometimes returns a table ForAll is like a for loop. In my original example, Filter gets called once for every comma-separated value in Prefixes (I checked in Monitor), and delegation doesn't happen in each call, even though StartsWith is delegable and its second argument doesn't reference any fields of MyDataSource and can be resolved to a static string to be passed to the data source at runtime. This is not due to some limitation of the data source; that's a bug in Power Apps.

[chathway2]

I don't get this either! I'm trying to get a collection of all SharePoint items in list, so to avoid 500 limit using a ForAll to collect each item that starts with A through Z. The below returns 500 limit.

ForAll(Sequence(26,65) As letter,
    With({currentLetter:Char(letter.Value)},
         Collect(myUsers,Filter(permStaff,StartsWith(Title,currentLetter))) //delegation warning
  )
)

If I replace currentLetter with a string value e.g. "A" then it loops 26 times creating 26 collection items for each item that starts with A!  no delegation warning.

I thought only the Filter function would delegate to SharePoint? on each loop iteration delegating the Filter query to SharePoint with the current item (currentLetter) 🤷‍

What does the below mean? The field name (Title) does not appear in the second argument (A-Z)

[chathway2]

This works fine! 😩

ClearCollect(Myusers,Filter(permStaff,StartsWith(Title,"A")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"B")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"C")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"D")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"E")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"F")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"G")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"H")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"I")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"J")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"K")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"L")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"M")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"N")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"O")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"P")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"Q")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"R")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"S")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"T")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"U")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"V")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"W")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"X")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"Y")));
Collect(Myusers,Filter(permStaff,StartsWith(Title,"Z")))

[RandyHayes]

@chathway2 

If you are experiencing a particular issue with your formulas, you might want to post a new message on it as this one is a little old at this point.

However, in reviewing your posts, yes, this is not something you can delegate.  You are trying to delegate multiple criteria (i.e. multiple filters).  You cannot do that.  You can delegate complex criteria in certain ways, but it all boils it down to a single filter/selection.  In your case, you are trying to force the formula editor to make a filter/select to the datasource that would always require at least 26 different filter/selects...that you cannot do.

In other words - Whenever the formula editor encounters a Filter statement, it tries to "package" it in a way that will produce one query based on what you want.  In your case, it would have to produce 26 of them, and that is not supported.

I hope this is helpful for you.

[chathway2]

Thanks for the explanation @RandyHayes .    It's a bit counterintuitive as would expect the only the Filter statement to be delegated to SharePoint however, I understand your statement regarding it trying to 'package' the whole ForAll command into a single command which it cannot do.

I'll work around it the long way around for now.