cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hpkeong
Community Champion
Community Champion

Suggestion box prompted...

Hi PG:

 

I have encountered several times of Suggestion, even for a simple Filter as in the below photo.

Anyhow, I can still filter without problems.

I have changed numerous time:

- in

- exactin

- =

howver, the Suggetion still occurs.

May I know what exactly is this and the impact though I don;t see my formula is complex nor strange.

It always happened when comparing with DATE.

 

 

warning prompted but still workingwarning prompted but still working

hpkeong
1 ACCEPTED SOLUTION

Accepted Solutions
rgruian
Power Apps
Power Apps

 

PowerApps supports a concept called seamless query delegation, which is documented here:

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

 

In a nutshell, parts of your formula will evaluate locally on the device where your app runs, and parts will run in the cloud as native operations on the corresponding data sources in your app, regardless of how "deep" they are within your formulas or how they compose with the rest of the queries/operations in your formulas. For example, if you use a SQL table as a data source and have a formula that Filters the table in some way, the SQL Server back end supports filtering, sorting, etc -- and PowerApps will understand that fact and ask the back end to perform compatible queries on its behalf. This has many obvious advantages, including scalability and performance.

 

Not all connectors and types of data sources support this capability, however the list of such connectors is growing release to release, as is the support for delegating more and more types of queries that can otherwise be expressed today in the PowerApps language.

 

You will see that blue icon with the suggestion you pointed out when PowerApps detects that the data source supports delegation in general but something in your formula prevents it from being delegated; e.g. because it manipulates types or objects that are either incompatible with the back end or inexpressible in the underlying query language -- OData. When that happens, PowerApps falls back onto local evaluation instead, which operates on at most 500 rows. You noted that you can still filter just fine; however if you were to grow your data to more than 500 rows, you would start noticing the effects of this local evaluation. Rows 501, 502, etc -- would be "invisible" for all practical purposes and would not show up in the result of your filter.

 

The warning/suggestion that we provide can be improved, I agree. I am acklowledging this feedback and will see what we can do to give more meaningful/helpful messages in future releases. (Thank you)

 

If you want to take advantage of delegation (you may not have to), the suggestion in the mean time is to simplify the formula. Currently the action of formatting a field at each row (e.g. via the Text function, the way you are doing) is not something that PowerApps can delegate, because the back end does not support that concept. What you can do instead is to provide a column in your data source that surfaces the year as a numeric value, and compare that against the selection.

 

I hope this helps.

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

3 REPLIES 3
rgruian
Power Apps
Power Apps

 

PowerApps supports a concept called seamless query delegation, which is documented here:

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

 

In a nutshell, parts of your formula will evaluate locally on the device where your app runs, and parts will run in the cloud as native operations on the corresponding data sources in your app, regardless of how "deep" they are within your formulas or how they compose with the rest of the queries/operations in your formulas. For example, if you use a SQL table as a data source and have a formula that Filters the table in some way, the SQL Server back end supports filtering, sorting, etc -- and PowerApps will understand that fact and ask the back end to perform compatible queries on its behalf. This has many obvious advantages, including scalability and performance.

 

Not all connectors and types of data sources support this capability, however the list of such connectors is growing release to release, as is the support for delegating more and more types of queries that can otherwise be expressed today in the PowerApps language.

 

You will see that blue icon with the suggestion you pointed out when PowerApps detects that the data source supports delegation in general but something in your formula prevents it from being delegated; e.g. because it manipulates types or objects that are either incompatible with the back end or inexpressible in the underlying query language -- OData. When that happens, PowerApps falls back onto local evaluation instead, which operates on at most 500 rows. You noted that you can still filter just fine; however if you were to grow your data to more than 500 rows, you would start noticing the effects of this local evaluation. Rows 501, 502, etc -- would be "invisible" for all practical purposes and would not show up in the result of your filter.

 

The warning/suggestion that we provide can be improved, I agree. I am acklowledging this feedback and will see what we can do to give more meaningful/helpful messages in future releases. (Thank you)

 

If you want to take advantage of delegation (you may not have to), the suggestion in the mean time is to simplify the formula. Currently the action of formatting a field at each row (e.g. via the Text function, the way you are doing) is not something that PowerApps can delegate, because the back end does not support that concept. What you can do instead is to provide a column in your data source that surfaces the year as a numeric value, and compare that against the selection.

 

I hope this helps.

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

hpkeong
Community Champion
Community Champion

 Hi Rguian

 

Appreciate very much of your detail explanation of what PowerAppss is doing (either locally or cloud). In fact I found that they are many similarity between PowerApps and SQL and that helps me a lot (in Parallel, i try to learn programming language to help improve my skill in PowerApps; PowerApps simplifies HTML & CSS, CRUD, but needs more logical concept in writing).

 

I can understand your last paragraph well and I will be very careful in writing search and filter before they reach 500 data.

"...action of formatting a field at each row (e.g. via the Text function, the way you are doing) is not something that PowerApps can delegate, because the back end does not support that concept..."

 

Formatting a field at each row...

- This is a very important concept I can really understand and in fact, it will create burden during filtering and searching and hence the limitation of 500. I am clear of this.

- The best solution for me and future apps will be Field for Filtering shall be at its original property.

 

I shall modify the Entity so that those Fields used for Filter / Search stays at its original properties as much as possible.

 

Once again, appreciate and I will test it out and post my personal investigation and feedback.

 

Have a ncie day.

 

hpkeong
hpkeong
Community Champion
Community Champion

Hi

 

I have changed the way of using DataPicker. I would rather spend sometime using Dropdown for:

- Year

- Month

- Day 

(as sometimes it was used to be because DatePicker's issue of dd/mm/yyy, mm/dd/yyyy as well as filtering purposes to avoid formating at each row, eventhough DatePicker looks for more nicer but for me and user, usability is priority).

 

Yes, and you may see that no more blue exclamation mark to suggest and things filter much more faster for Year, Month.

 

Hope your detail explanation not only helps me but others when data starts to accumulate more than 500 rows and ...nightmare!

 

Screenshot (6).png

hpkeong

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,369)