Delegation not working as documented / with SQL Server.

[ShowMeTheAnswer]

Hi,

I've read this document:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

And done some testing / had some issues.

I have a dataset of 530 rows. Its an SQL Table, which is "delegated" according to documentation.
I'm using a Lookup formula, with equals, which are both also delegated.

Here is my formula:

LookUp('[dbo].[MMMPRODUCT]',Parent.Default=Text(PROD_ID),PRODUCTSHORT)

=Text(PROD_ID) has a blue squiggle under saying:
"Delegation warning. The lookup part of the formula may not work on large datasets".

Documentation states:

Filter functions

Filter, Search, and LookUp can be delegated.

Within the Filter and LookUp functions, you can use these with columns of the table to select the appropriate records:

• And (including &&), Or (including ||), Not (including !)
• In
• =, <>, >=, <=, >, <

--

Results:

• When limit is set to 500 (the default), nothing is returned by the LookUp.
• If I change the limit to 600, then the formula works correctly.
• This means that results are being processed locally so the app is slow.

Why doesn't it work as documented?

[timl]

Hi @ShowMeTheAnswer 

The reason this query is non-delegable is because you're applying a function (ie the Text function) against a source SQL field.

I imagine PROD_ID is an int field, so if you match this against a numeric value, the delegation problem will hopefully go away.

LookUp('[dbo].[MMMPRODUCT]', 
        Value(Parent.Default)=PROD_ID,
        PRODUCTSHORT
)

[RusselThomas · Accepted solution]

Hi @ShowMeTheAnswer ,

The problem isn't your operator, it's your function inside the filter statement. 
Text() is not a supported (by delegation) function and this is what's breaking your delegation.

In my experience, both sides of your condition need to be uncomplicated comparisons - the column value on one, and a simple value on another.  If you try and wrap either side in a PowerApps function (like Text() as an example) this generally breaks delegation (unless that function happens to support delegation - like SPO and StartsWith()), because the function is passed as part of the filter command and the source won't understand it.

You can however apply functions or formula to the the conditional provided you do it outside the Filter() function and pass it in as a variable.

eg: let's assume columnA in my source is a NUMBER

Filter(source, columnA=12)

works fine with delegation - but let's say our comparitor on the right is actually a Text type.

Filter(source, columnA="12")

won't work (very well) because I'm comparing a Number to a Text value and it will complain.  PA is clever enough to auto-type where possible, but this breaks delegation anyway.  For best results, I need to match types on both sides of the equation.

Filter(source, Text(columnA)="12")

Will work but will break delegation because the source does not understand the Text() function.

Filter(source, columnA=Value("12"))

Will also work, but will also break delegation because the Value() function is passed as part of the filter query and the source does not understand it.

UpdateContext({varComparitor: Value("12")});
Filter(source, columnA=varComparitor)

Will work with delegation because it passes the result of the function through as a simple value of type Number, which the source understands and can be delegated.

Hope this helps,

Kind regards,

RT