cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ancy
Kudo Kingpin
Kudo Kingpin

Delegation warnings in Power Apps - Canvas App

Hi All,
We are facing issues while fetching data from the data sources in the Canvas App. We are using the SQL Server Connector and wanted to fetch the large set of records in Power Apps.
We have used the below code but it's showing the delegation warning at "&&" and "||" operators (example is given only for "&&" operator).

 

 

Set(
    loggedInEmpId,
    First(
        Filter(
            '[dbo].[employees]',
            emp_email = "test@test.com" && emp_name = "Test"
        )
    ).id

 

 

Also we face the similar problem with the "Filter" method being used under the "First" method.  

Is there any alternative to fix the aforesaid warnings?Any help would be appreciated.
Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
RameshMukka
Post Prodigy
Post Prodigy

Does your filter always fetch single record? Can you try below formula?

Set(
    loggedInEmpId,
        Lookup(
            '[dbo].[employees]',
            emp_email = "test@test.com" && emp_name = "Test"
        ).id
    )

View solution in original post

10 REPLIES 10
RameshMukka
Post Prodigy
Post Prodigy

Does your filter always fetch single record? Can you try below formula?

Set(
    loggedInEmpId,
        Lookup(
            '[dbo].[employees]',
            emp_email = "test@test.com" && emp_name = "Test"
        ).id
    )
eka24
Super User
Super User

Try using lookUp:

Set(

    loggedInEmpId,

            LookUp('[dbo].[employees]',emp_email = "test@test.com" && emp_name = "Test" ,id)
 )

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

LRVinNC
Resident Rockstar
Resident Rockstar

Unfortunately, expressions joined with AND, OR and NOT are not delegable with SQL Server.  (Here's the info on what is / is not delegable in SQL Server:  https://docs.microsoft.com/en-us/connectors/sql/

 

The best alternative I can think off is to load the data to a collection then do additional manipulations on the collection where you won't have the delegation issues.  You may be able to pre-filter using a single expression on your load to reduce the amount of data loaded.  If that is still too many records to load, find a way to iterate through the file to load in chunks.  That might be using field values that will return less than the delegation limit or by iterating on the ID value.  In one app, I using a region field to break the load into 5 segments, all within delegation limits.  In another, I use the method put forth by Brian Dang in this article: https://powerusers.microsoft.com/t5/Building-Power-Apps/Overcoming-the-500-record-limit-locally/m-p/...

 

 

LRVinNC

------------------
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.
RandyHayes
Super User
Super User

@Ancy 

Please consider changing your Formula to the following as has been previously provided:

Set(
    loggedInEmpId,
    LookUp('[dbo].[employees]',
        (emp_email = "test@test.com") && (emp_name = "Test"), 
         id
    )
)

 I only chime in for a few reasons - first, the above is the correct syntax.  Second, note the use of the parens around the conditions.  In this particular case, they are not needed, but I express it as a general practice to get used to because sometime the parse of the condition is done out of the order that you expect; putting the parens around it ensures that you are getting the order you want - and it's also a little easier to read.  

And finally to mention, @LRVinNC , yes, you can AND and OR criteria to SQL, we do this all the time with no delegation issues.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
LRVinNC
Resident Rockstar
Resident Rockstar

@RandyHayes Is this out of date then?
SS.jpg

LRVinNC

------------------
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.
RandyHayes
Super User
Super User

@LRVinNC 

No, it is not out of date.  However, I believe you are reading it wrong.  Read it again.  The "Not" in the sentence is one of that placements in the sentence that fools the mind.

   Expressions joined with And, Or, and Not ARE delegable to SQL Server

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
LRVinNC
Resident Rockstar
Resident Rockstar

@RandyHayes I'll just be crawling back under my rock now....

LRVinNC

------------------
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.
RandyHayes
Super User
Super User

@LRVinNC 

LOL...no need!  These things are easily confused.  I really think they should have written that statement differently.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Drrickryp
Super User
Super User

Hi Everyone,

Although I use DataVerse not SQL, it is my understanding that you can use Views in SQL to prefilter, sort and join tables before bringing the data into PowerApps.  If you could do the query in Sql, perhaps this would be the way to avoid the issue in the first place.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (4,651)