Will I face the delegation issues with Sharepoint?

(0) Share

Report

Posted on by ultrAslan

100

Hello Community,

We have been using (actually) a finished canvas apps for a few weeks now. In the settings, I have increased the limits of the items that can be loaded directly into the app to 2000.

In the Power Platform Documentation it's explained,
which functions and operators are supported when loading items into a gallery from a sharepoint list and which are not (so they have to be delegated).

Power Apps delegable functions and operations for SharePoint

It is a bit confusing for me though.
For example, it says that IsBlank() is not delegable.
I also use IsBlank() within the Items property, but not in touch with the data source (Sharepoint List) directly, but for my search algorithm to include multiple dropdowns and text fields (with several words that do not have to match each other) in my search.

Here is the code I use inside the Items property for the Gallery:

SortByColumns(
 Filter(
 'SharepointList';
 IsBlank(
 Find(
 "0";
 Concat(
 Filter(
 Split(
 
 Concatenate(TextSearchBox1; " " ; Dropdown1.SelectedText.Value) ;
 " "
 );
 !IsBlank(Result)
 );
 If(
 TrimEnds(Result) in Column1.Value || TrimEnds(Result) in Column2 || TrimEnds(Result) in Column3;
 "1";
 "0"
 )
 )
 )
 )
 );
 "CreatedColumns";
 If(
 SortDescending1;
 SortOrder.Descending;
 SortOrder.Ascending
 )
)

What do you guys think, will the app run clean even with more than 2000 rows in the sharepoint list?
Thank you for any help and any advice!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Sort by

WarrenBelz 155,315 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @ultrAslan ,

Firstly, you might have a read of my blog on SharePoint Delegation and it should answer a few of your questions. SharePoint can accommodation 33 million rows - the 2,000 "limit" is for non-Delegable queries which are generally able to be managed with a bit of thought and planning around your data structure (I have a blog on this also). You also need index any columns (you are allowed 20 per list) that are addressed in queries once your list gets over 5,000 items.

You do however have a number of non-Delegable (or output limited by Delegation) elements in your posted code including Find, IsBlank, Concat, Split and TrimEnds (pretty much the entire query).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
ultrAslan 100 on at

Like (0)

Report
Copy link

Link copied!

Unfortunately, I still can't quite understand it.
All the functions you listed I use only to transform the inputs that are still made in the app. Find, IsBlank, Concat, Split and TrimEnds do not refer to the data within the sharepoint list, but to my search field or dropdowns. I use these mentioned functions to search for multiple dropdown selections and keywords. This is not the query, in my opinion.

For example TrimEnds removes all spaces from the entire searchtext typed in the App before the query to Sharepoint. Same with Find, IsBlank, Concat, Split because none of them refers to the data/column contents from the datasource. So, the only issue I see is with "in" operator/function.

I dont understand ........ 😕 i dont know 😕

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,315 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @ultrAslan ,

All those functions are inside the Filter

Filter( 'SharepointList'; . . . . . . . . . . )

therefore are part of the query you are sending to SharePoint. There is a workaround for some of it if you can get the bit not referring to the SharePoint field outside the filter (using a With() Statement), but I must admit I am a bit perplexed by the code gymnastics when you seem to be just looking for the content of TextSearchBox1 or the Dropdown1 selection in your three columns. I forgot to mention that an If() statement inside a filter is also not Delegable.

Was this reply helpful? Yes No
ultrAslan 100 on at

Like (0)

Report
Copy link

Link copied!
I took everything you said into consideration. Thanks for that. The whole problem was not clear to me before.

My code now looks like this:

SortByColumns( If( IsBlank(TextSearchBox1.Text) && IsBlank(Dropdown1.Selected.Value); [@'SharepointList']; Filter( [@'SharepointList']; StartsWith( Column1; TextSearchBox1.Text ); StartsWith( Column2; TextSearchBox1.Text ); StartsWith( Column3; Dropdown3.Selected.Value ) ) ); "Created"; If( SortDescending1; SortOrder.Descending; SortOrder.Ascending ) )

I no longer have a delegation warning. That's the good thing for now.

I am only missing the functionality that the keywords, which are typed in in "TextSearchBox1", dont need to be at the beginning of the Column I am looking into.

Unfortunately, I can't think of a way to realize this with delegatable functions and operators.
Before it didn't matter if the search word "House" in the column was at the beginning ("House is big"), in the middle ("He has the biggest house in town") or at the end ("It's a huge house.").
I always got a result in the Gallery with my original, non-delegable algorithm, whether the keyword typed into the search box was at the beginning or somewhere else.

Unfortunately, I'm missing that and I'm very overwhelmed. Can you help me here too please?
Thanks a lot!

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,315 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @ultrAslan ,

Firstly, the below may not fix your Delegation issue limitation (you will get not warning), however providing the output of the top filter (the list can be any size) is under your Delegation limit, the bottom filter will return the correct result

With( { wData: Filter( [@'SharepointList']; Len(Dropdown3.Selected.Value) = 0 || Column3 = Dropdown3.Selected.Value ) }; Search( wData; TextSearchBox1.Text; "Column1"; "Column2" ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
ultrAslan 100 on at

Like (0)

Report
Copy link

Link copied!

I think now I have finally understood the whole matter. Thanks for your patience 🙂 and effort!

Your last answer with the example code was very enlightening.
I shall select the columns/dropdowns to be filtered so that within With()-Function the gallery list is below 2000 items - in the best case.

This is how I have understood it now.

Anyway, thanks for the explanations and also your helpful tips in your blog, which I can also recommend to the other readers who happen to be here!

I have one more thought I want to get off my chest:
In my business case, if I can assume that by using a dropdown in the With() function, the list to be loaded onto the app will definitely be under 2000 items,
is there anything against searching this result (let's still call it "wData") with non-delegable functions like "in"?

Was this reply helpful? Yes No
WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @ultrAslan ,

You can use whatever filter you want in the second part - the With() statement is effectively a (very) temporary Table Variable (call it a collection if you like) and the second filter executes "locally", so Delegation does not apply.

Was this reply helpful? Yes No
KarimWalid 2 on at

Like (0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No