SQL First;sortbycolumns suddenly delegation error

[HansHeintz]

Hi,

 

This code, using sql server suddenly comes back with a delegation error where it worked perfectly for months:

When I played as a kid it was considered unfair to change the rules while playing and I guess my users still feel the same way.

;;UpdateContext({FoundDier:First(SortByColumns(Filter('[dbo].[dier]';LastInputString in naam);"dier_id";Descending))})

[RobynPr]

Hi

just tried something similar and no delegation problems. See below. I see you have lots of ; where I have ,

[WarrenBelz]

Hi @HansHeintz ,

I believe the offending bit is the In filter (although it is documented as Delegable in SQL).

To test the theory, try (may not be the result you seek, but will establish the problem).

UpdateContext(
   {
      FoundDier:
      First(
         SortByColumns(
            Filter(
               '[dbo].[dier]';
               LastInputString = naam
            );
            "dier_id";Descending
         )
      )
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[HansHeintz]

No need to test, this line has the same problem (no deleagtion warning for months until now)

;;Set(MaxRecID; First(SortByColumns('[dbo].[dier]'; "dier_id"; Descending)).dier_id)

And there is no "in" in there

@ the other guy: we Europeans must be a minority because I still get comments about ; in stead of ,

If ; was wrong it would not have worked for months now would it?

[WarrenBelz]

Thanks @HansHeintz ,

I am not European (Australian), but there are a lot of posts with ; and I have never noticed any Delegation issues unique to it. As an experiment, try this

Set(
   MaxRecID; 
   First(
      Sort(
         '[dbo].[dier]'; 
         dier_id; 
         Descending
      )
   ).dier_id
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[HansHeintz]

Thanks, but same result alas

[HansHeintz · Accepted solution]

What does help is a

First(FirstN(   ….     ;1)) 

construction.

Dunno if that has negative sides to it (speed or whatever) but the blue lines and yellow triangles go.

ps if this is a viable solution I am tempted to say: "BUG!"

[WarrenBelz]

Hi @HansHeintz ,

Yes - First and FirstN(),1 are the same thing. I have no other thoughts if this works, stick with it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @HansHeintz ,

Based on the issue that you mentioned, I think it's a normal behavior.

Currently, the First function or FirstN function is not a Delegation function in Power Apps currently. Even though, the Sort() formula you provided is a Delegable formula, when you apply the First function to the Sort formula, the Delegation warning issue would show up.

Please check the following article for more details:

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

If you just want to get rid of the Delegation warning issue in your formula, I think the With function could achieve your needs. I have made a test on my side, please check the following workaround:

On your side, you should type the following formula:

With(
     {
         MaxIDRecord: FirstN(Sort('[dbo].[dier]'; dier_id; Descending); 1)
     },
     Set(MaxRecID; First(MaxIDRecord).dier_id)
)

I also agree with your solution, the following formula would also hidden the "Delegation warning" error:

Set(MaxRecID; First(FirstN(Sort('[dbo].[dier]'; dier_id; Descending); 1)).dier_id)

Although, the "Delegation warning" message disappear, but the formula still be subject to the Delegation limit -- you could not delegate the Data process to your SQL Table directly, instead, you could only process data locally. You could only process 2000 records at most locally.

Note: For your scenario, because you just want to get the first record of the Sort formula result, the Delegation warning limit would not affect the need you want to achieve.

Best regards, 

[Drrickryp]

Hi @HansHeintz 

Technically First() is not delegatable but Sort() is so regardless of whether there is a warning, the formula

First(Sort(datasource, ID, Descending)).ID

will delegation of First() will NEVER have any real effect regardless of the number of items in the datasource.  I have used this formula countless times with very large datasources.