cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

SQL First;sortbycolumns suddenly delegation error

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))})

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: SQL First;sortbycolumns suddenly delegation error

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!"

View solution in original post

12 REPLIES 12
Highlighted
Advocate I
Advocate I

Re: SQL First;sortbycolumns suddenly delegation error

Hi

 

just tried something similar and no delegation problems. See below. I see you have lots of ; where I have , Annotation 2020-06-05 171450.png

 

 
Highlighted
Community Champion
Community Champion

Re: SQL First;sortbycolumns suddenly delegation error

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.

Highlighted
Resolver II
Resolver II

Re: SQL First;sortbycolumns suddenly delegation error

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?

Highlighted
Community Champion
Community Champion

Re: SQL First;sortbycolumns suddenly delegation error

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.

Highlighted
Resolver II
Resolver II

Re: SQL First;sortbycolumns suddenly delegation error

Thanks, but same result alasThanks, but same result alas

Highlighted
Resolver II
Resolver II

Re: SQL First;sortbycolumns suddenly delegation error

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!"

View solution in original post

Highlighted
Community Champion
Community Champion

Re: SQL First;sortbycolumns suddenly delegation error

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.

Highlighted
Community Support
Community Support

Re: SQL First;sortbycolumns suddenly delegation error

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:

1.JPG

 

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, 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

Re: SQL First;sortbycolumns suddenly delegation error

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.

wrong.gif

 

Helpful resources

Announcements
secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,217)