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
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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

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

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

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

@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.
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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,974)