cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Delegation Warning

Hi there,

Currently I have 2 warnings for this:

 

In my first gallery I have a warning for my Column "Title"

SortByColumns(
Filter(
LIPCustomers,
TextSearchBox1.Text in Title || TextSearchBox1.Text in Quote
),
"ID",
If(
SortDescending1,
Descending,
Descending
)
)

 

And In a combobox it says the "search" part of this formula might not work on large data sets. This data source I am pretty sure that the list will never exceed 2000 records. Should I worried about this? if so how can I fix both warnings?

 

Thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

@sajarac 

That depends on how large your first table is (or can be expected to grow to).  However, if you can use StartsWith in your filter you will not have a problem with delegation no matter how large your Customers list becomes. 

 

Filter(
    Customers,
    StartsWith(
        Title,
        SearchInput.Text
    ) || StartsWith(
        Quote,
        SearchInput.Text
    )
)

 

StartsWith() is a delegatable function in SharePoint so the filter is done at the server level. It will also be quite fast.  If you absolutely require a search within the field and your list will become greater than 5k, consider using a different connector such as SQL or CDS.  SharePoint is free but gimping the functions available to PowerApps is the price you pay. 

View solution in original post

Highlighted

@sajarac 

To test whether the Filter is working, copy it into the Items property of the gallery.  Also, check the Text property of the TextInput box and make sure it is "".   I like to build a formula for a gallery piece by piece so that I can check if it is working.  For example, 

I would to start by clearing the Items property of the gallery and put in just Customers and see if it fills up.  Then build the formula by wrapping Customers with Filter( Customers, StartsWith(Title, SearchInput.Text )).  If that works, then add the OR part to get 

Filter( Customers, StartsWith( Title, SearchInput.Text ) || StartsWith( Quote, SearchInput.Text ) ) .  Finally when that works, wrap the Filter with the SortbyColumns().

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

Hi @sajarac ,

 

In PowerApps In is not a delegable operator for PowerApps. I noticed you are using In in your filter.

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

If you want to avoid this move all your data to a collection and perform filter on the collection with IN.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

@KrishnaV is absolutely right, but I will elaborate a bit on his point just to explain some of the nuance here.

 

  1. Delegation of query is a topic actively worked on by the product group, so over time more and more support is being added, so do not despair: some day soon this might not be an issue any more.
  2. You mentioned that you are sure the record counts will never exceed 2k for this table. If you are CERTAIN about that, then it is possible to override the return row limit for non-delegable queries (default 500) to bump them up to 2k. This would get you around the problem, but be CAREFUL: the limit is defaulted to a low number because it could possibly cause a performance issue and could be a big problem down the line if you go higher than the limit you set, so TEST TEST TEST!
    1. To override this, open your app to edit and navigate to Settings --> Advanced Settings --> Data row limit for non-delegable queries. Set this value to 2000 and it will still give you the warning, but the query will be safe for up to 2k rows.

cchannon_0-1594058862759.png

 

 

Highlighted

Thank you both @cchannon , @KrishnaV ,

 

I am sure for the second gallery that the rows never will exceed.

 

But for the first gallery, any workaround to fix the issue in the meantime, because that gallery will pass sooner 2000 records.

 

thank you kindly

Highlighted

@sajarac 

That depends on how large your first table is (or can be expected to grow to).  However, if you can use StartsWith in your filter you will not have a problem with delegation no matter how large your Customers list becomes. 

 

Filter(
    Customers,
    StartsWith(
        Title,
        SearchInput.Text
    ) || StartsWith(
        Quote,
        SearchInput.Text
    )
)

 

StartsWith() is a delegatable function in SharePoint so the filter is done at the server level. It will also be quite fast.  If you absolutely require a search within the field and your list will become greater than 5k, consider using a different connector such as SQL or CDS.  SharePoint is free but gimping the functions available to PowerApps is the price you pay. 

View solution in original post

Highlighted

Thank you.

 

My current formula is:

SortByColumns(Filter(LIPCustomers,TextSearchBox1.Text in Title || TextSearchBox1.Text in Quote),"ID", if(SortDescending1,Descending,Descending))

 

And yours is:

 

Filter( Customers, StartsWith( Title, SearchInput.Text ) || StartsWith( Quote, SearchInput.Text ) )

 

 

but I am getting an empty gallery.

 

Any idea?

 

thank you kindly

Highlighted

@sajarac 

To test whether the Filter is working, copy it into the Items property of the gallery.  Also, check the Text property of the TextInput box and make sure it is "".   I like to build a formula for a gallery piece by piece so that I can check if it is working.  For example, 

I would to start by clearing the Items property of the gallery and put in just Customers and see if it fills up.  Then build the formula by wrapping Customers with Filter( Customers, StartsWith(Title, SearchInput.Text )).  If that works, then add the OR part to get 

Filter( Customers, StartsWith( Title, SearchInput.Text ) || StartsWith( Quote, SearchInput.Text ) ) .  Finally when that works, wrap the Filter with the SortbyColumns().

View solution in original post

Highlighted

Thank you, thank you, thank you.

 

Delegation is "GONE WITH THE WIND"

 

and sort by columns works also.

 

thanks a million!!

 

🙂

 

Highlighted

smiling.gif

 

Highlighted

Hold on, before we do the happy dancing with your creature, I would like to ask you this:

 

SortByColumns(Filter(LIPCustomers, StartsWith(Title, TextSearchBox1.Text) || StartsWith(Quote, TextSearchBox1.Text)),"ID",If(
SortDescending1,
Descending,
Descending
)
)

 

I am getting invalid argument type (number) expecting a text value instead.

 

I have tried (value(quote.text). 

 

But is complaining

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,969)