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

[samuelJ]

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.  

[RezaDorrani]

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]

@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?

[RezaDorrani]

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]

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

[RezaDorrani]

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 🙂

[Drrickryp]

Hi @samuelJ 

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

[samuelJ]

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