cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samuelJ
Level 8

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

samuelJ
Level 8

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?

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

 

 

samuelJ
Level 8

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.  

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 🙂

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

samuelJ
Level 8

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,736)