cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

Not getting all records from a Filter() that I should

Hello,

 

I'm suspecting this has something to do with delegation or something that I don't fully understand about powerApps.  I have the below formula in Powerapps that is only returning the first 3 records when it should return 14.  

Filter('[dbo].[CustomerKeyAccountParentMaster]', CustomerKeyAccountParentId in (Filter('[dbo].[SalespersonCustomerSupplierItemBridge]', SalespersonId = varCurrentUser.SalespersonId).CustomerKeyAccountParentId))

In SSMS I use the below query and get everthing that is expected.  

 

select CustomerKeyAccountParentName from CustomerKeyAccountParentMaster join SalespersonCustomerSupplierItemBridge ON CustomerKeyAccountParentMaster.CustomerKeyAccountParentId
= SalespersonCustomerSupplierItemBridge.CustomerKeyAccountParentId where SalespersonId = 1002342;

 

Can someone please provide some insight into why this is?  It is a SQL Azure connection.  These tables are pretty large tables,  hundred of thousands of records, but my understanding is that these Filter() functions and "in" comparisons should be happening on the SQL server side.  So large data is not a problem as I am expecting only a subset of ~14 to returned.  

7 REPLIES 7
Highlighted
Dual Super User
Dual Super User

Re: Not getting all records from a Filter() that I should

Hi @samuelJ 

 

If you are getting a delegation warning then it will only perform the query on the first x (delegation row limit - 500 default can be extended to 2k) records

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Highlighted
Responsive Resident
Responsive Resident

Re: Not getting all records from a Filter() that I should

@RezaDorraniThanks for the reply,

 

So based off of what you are saying is that PowerApps will only look at 500 records for each table?...  I thought this delegation warning meant that it would not bring back more than 500 records as a result?  

 

If this is the case, how do you get powerapps to work with modern data?

Highlighted
Dual Super User
Dual Super User

Re: Not getting all records from a Filter() that I should

Hi @samuelJ 

 

You would have to use formulas which are delegable (check below for list of delegable functions related to SQL)

https://docs.microsoft.com/en-us/connectors/sql/

 

If you want to know more about delegation in general - check my video on delegation

https://www.youtube.com/watch?v=gwiErbYtRdA

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Highlighted
Responsive Resident
Responsive Resident

Re: Not getting all records from a Filter() that I should

I checked out that link and both "Filter()" and "in" are listed as delgable so guess im missing something....  Ill watch the video.  

Highlighted
Dual Super User
Dual Super User

Re: Not getting all records from a Filter() that I should

Hi @samuelJ 

 

check closely 

in only works with text fields

read the details 

 

you seem to be using exactin

 

delegation is a double edged sword 🙂

Highlighted
Super User
Super User

Re: Not getting all records from a Filter() that I should

Hi @samuelJ 

When using Sql, you can use Search() instead of Filter and "in".  

Highlighted
Responsive Resident
Responsive Resident

Re: Not getting all records from a Filter() that I should

Hi @Drrickryp thanks for the reply,

 

My understanding of Search it is returns the first instance of the row/record it finds.  Here I want a subset of data.

 

Ive corrected my original post, I was using "In" and not "exactin".  Reza makes a good point that "in" is only delegation for strings, and not numbers, which is what my id column is.  

 

I almost need to create a loop and fetch each record individually?...

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

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

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,566)