cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HansHeintz
Post Patron
Post Patron

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

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
RobynPr
Advocate I
Advocate I

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

 

 

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.

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?

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.

Thanks, but same result alasThanks, but same result alas

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

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.

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.

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,484)