Answered

Delegation warnings in Power Apps - Canvas App

(0) Share

Report

Posted on by Inkey Solutions

112

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

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

rameshmukka 1,115 on at

Like (2)

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

Was this reply helpful? Yes No
eka24 20,923 on at

Like (2)

Report

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.

Was this reply helpful? Yes No
LRVinNC 2,297 on at

Like (0)

Report

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/18379

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (2)

Report
@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.

Was this reply helpful? Yes No
LRVinNC 2,297 on at

Like (0)

Report

@RandyHayes Is this out of date then?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
LRVinNC 2,297 on at

Like (1)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@LRVinNC

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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Inkey Solutions 112 on at

Like (0)

Report

Thanks everyone for the help!! My issue got resolved using the LookUp() function.

Was this reply helpful? Yes No