cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Getting following error when trying to fectch data into a varaible using Filter the error as following "Filter Part of this Formula may not work correctly on large Dataset"s .

Thanks, in Advance  Guys for your time and Support...

 

I have created a variable to get some data for validation using the following code 

 

Set(strdt,Filter('[dbo].[SQlTable]',( Title4_1.Text in Details && StartDate = Text(StartDate) && EndDate = Text(EndDate));

 

What I have tried is changing the App Setting 500 to 2000 But still facing the same issue.

is any better way to get the data from SQL table for validation please Help ...

2 REPLIES 2
Highlighted
Super User
Super User

Re: Getting following error when trying to fectch data into a varaible using Filter the error as following "Filter Part of this Formula may not work correctly on large Dataset"s .

What is the structure of the date column?

In is Delegated by Date Column should be structured in numbers to avoid delegation issues.

 

Meanwhile, try a collection OnStart:
ClearCollect(MyTable,'[dbo].[SQlTable]')

 

Then

Set(strdt,Filter(MyTable,( Title4_1.Text in Details && StartDate = Text(StartDate) && EndDate = Text(EndDate))

 

------------

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.

Highlighted
Super User
Super User

Re: Getting following error when trying to fectch data into a varaible using Filter the error as following "Filter Part of this Formula may not work correctly on large Dataset"s .

A couple of quick questions/suggestions:

  • Is there a reason you're putting this table of records in a variable? Can it be a Collection instead?
  • Here is a quick overview of Power Apps delegation
  • The Filter function supports comma-delimited functions, and will evaluate all of them - it's easier to read the list, instead of relying on combining them with multiple &&'s

In your example, SQL is a delegable source. Therefore, it will support delegation of queries with common, supported methods.

The part which is likely tripping it up is explicitly calling the wrapper Text function, as part of your query. In theory, this doesn't resolve once (as you may expect), but instead if now being called as part of query and all data sources will not support this.

 

To fix it, I would recommend two things:

  • Make your Date values variables, before you get the data
    • Set ( gblStartDate, Text(StartDate) )
    • Set ( gblEndDate, Text(EndDate) )
  • Consider putting this data into a Collection
    • This is inherently a table structure, and is simpler to troubleshoot
    • Both a table variable and Collection will have the same columns, but the Collection will be much easier to work with syntactically
  • (You may want to do it for the Text value, as well, but this should be supported as-is)
Collect(colStrdt,Filter('[dbo].[SQlTable]',( Title4_1.Text in Details, StartDate = gblStartDate, EndDate = gblEndDate )));

 

Helpful resources

Announcements
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
Users online (4,966)