cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjcitpro
Helper III
Helper III

Filter not returning all results from SQL Database

I have this OnSelect for a Button:

ClearCollect(preInvoice, Filter('[dbo].[PurchaseOrderInvoice]', DateDiff(CreatedOn, Now(), Days) <= 10))

Gallery: SortByColumns(preInvoice,"CreatedOn",Descending)

It returns 7 results from a large table.

It should return 49 results but I only get last 7.

No matter what value I substitute for number of days the results always start with the same record, 43 into what should be returned.

I created this OnSelect for a Button:

ClearCollect(preInvoice, Filter('[dbo].[PurchaseOrderInvoice]',InvoiceId > 2000))

It will return those records that are not showing in first command.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @sjcitpro

DateDiff is a non delegable filter expression, and this explains why your query returns 7 records instead of 49. Because your filter expression is non delegable, PowerApps retrieves the first 500 records from the database and shows the matching records from those 500 records only.
A related issue is that due to a bug in PowerApps, it isn't possible to filter SQL server date fields by inequality operators.
https://powerusers.microsoft.com/t5/General-Discussion/Filtering-on-prem-SQL-data-source-by-date/td-...

Therefore, the best workaround for your situation is to display your data through a SQL Server view. You can either create a view that returns only the records that were created in the last 10 days, or you can create a view that includes a numeric representation of your create date in yyyymmdd format. This would enable you to filter the numeric date column within PowerApps in a delegable way, thereby returning all 49 records that you expect.

View solution in original post

4 REPLIES 4
samuelJ
Responsive Resident
Responsive Resident

What datatype is "CreatedOn", is it just a Date, or a DateTime?  If its a DateTime (It looks like you're using a SQL DB), SQL Server stores time in UTC.  Could there have been data conversion loss between storing the date and retreiving the date?  How do the dates get put into the DB?

SQL Server. CreatedOn is a DateTime.  First Pic is first command, second Pic is second command.

Hi @sjcitpro

DateDiff is a non delegable filter expression, and this explains why your query returns 7 records instead of 49. Because your filter expression is non delegable, PowerApps retrieves the first 500 records from the database and shows the matching records from those 500 records only.
A related issue is that due to a bug in PowerApps, it isn't possible to filter SQL server date fields by inequality operators.
https://powerusers.microsoft.com/t5/General-Discussion/Filtering-on-prem-SQL-data-source-by-date/td-...

Therefore, the best workaround for your situation is to display your data through a SQL Server view. You can either create a view that returns only the records that were created in the last 10 days, or you can create a view that includes a numeric representation of your create date in yyyymmdd format. This would enable you to filter the numeric date column within PowerApps in a delegable way, thereby returning all 49 records that you expect.

View solution in original post

Thanks. I read up on delegation. Interesting.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,507)