Power Apps Collection Vs Datasource Query && Search Vs Filter function

[manjotsingh057]

I have a SQL table with 6000+ rows bound to increase gradually. 

I have to create PowerApp containing Datatable that connects to this data along with filter.

Query 2  : What would be the best way to have the data ?? Would it be storing in collection or direct sql query ?

Also there would be 2 queries. 

1. For filtering out data based on one flag Yes/No 

   AND

2. Filtering based on search term.

Query 1  : What would be better for this type of scenerios ??

Search Vs Filter function ?

[Drrickryp · Accepted solution]

Hi @manjotsingh057,

Sql server has the most delegatable functions of all the data sources. See: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list so you are already using the best platform for Powerapps.

All Powerapps users should understand delegation and why it is necessary.  To quote "Delegation in PowerApps minimizes the data transport from the data source to the mobile device through remote data processing. With delegation the data processing is performed in the data source level rather than moving the entire data to the mobile device and process them locally".  This means that the speed of processing is limited only by the bandwidth. In the case of Search and Filter, both functions are delegatable and whether you use one or the other depends mostly on your personal preference since the speed of the operation should be the same for both.  I have data sources of more than 70,000 items and both functions work great.  

To answer your question regarding collections, the Collect functions are not delegatable and thus are limited to a maximum of 2000.  In your case, since you use Sql server, you are better off using the data source table directly rather than a collection.  Hope this helps and sorry to be so preachy.

[manjotsingh057]

Thanks @Drrickryp for clarification.

Just one doubt. 

When i directly connect data table to data souce and use "in" operator for search. Then it does not work. It gets stuck after loading 200-400 items. It does not load after this.

when i remove this and try to load without any filter function it works fine. Anay idea here  ??

Also while designing app once i set the data table item source then it starts loading items which slows down the development. Any way or setting where we can set this off.. Like it should not load any data until we play it ?

[Drrickryp]

Search() function doesn't require "in" as it is implicit. Please post the items property of the datatable so I can tell you how to load the datatable on command.

[manjotsingh057]

Thanks @Drrickryp, by using "in" operator in search i mean using "in" inside filter operation for search. I could have more clarity in words.. Anyways big thanks for your time.

It's working fine now.

By the way.. Is it possible to have multiple conditions using "search" operator  ? like using AND or OR operators in search just as we use in filter operator ??

[Drrickryp]

Hi @manjotsingh057,

If I have answered your question could you please mark it solved?

I haven't actually used multiple conditions for a Search.  I have used Filter with multiple conditions using And & Or so that is the way I would approach it.