Galery filter, Start with or with, delegation warning

[AnthonyRegnier]

Hello guys,

I have a SharePoint List that I filter with the code that I pasted below. It's a little complex please feel free to read it.... It filters according to a search, according to a dropdown, and does a custom sort according to the result of a division of some values.

1 -For the search box: I want to add as may OR conditions as I want to filter through the columns I need (just one more actually). It works, but it breaks the rest of the code (not that it breaks the syntax the rest of the code does not apply anymore.

2 -Also for the search box: I also want to change StartWith with an "IN" but it does not work and I have not been able to so far to make the search works.

3 - In the code I have some delegation warning that I cannot get rid of, on the below piece of code: 

&&
         (
            Status = "High" || 
            Status = "Low" ||
            Status = "Average" ||
            Status = "Out"
         )

Thank you so much for your help!

Here is the code of the item property of the gallery:

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status = "High" || 
            Status = "Low" ||
            Status = "Average" ||
            Status = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

[KrishnaV]

Hi @AnthonyRegnier ,

try this:

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status.Value = "High" || 
            Status.Value = "Low" ||
            Status.Value = "Average" ||
            Status.Value = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

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.

[AnthonyRegnier]

Hello Krishna, What is the difference in the code you have sent?

It does the same thing (I removed the ".value" after the status as it was breaking the code.

What I need is to be able to search through multiple columns, using a "contains" operator instead of startwith.

Thanks for your help!

Anthony

[KrishnaV]

Hi @AnthonyRegnier ,

contains is not available in PowerApps. what you could do is use the search function but remember Search is not a delegable function with the DataSource Sharepoint.
Before I provide you the solution let me explain to you how StartsWith work it look for the text as start with value in the provided column and if you want you can add multiple columns with the And condition.
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-startswith#examples
To achieve your multicolumn search all we can do is as below:

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         StartsWith(
            Column2,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status = "High" || 
            Status = "Low" ||
            Status = "Average" ||
            Status = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

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.

[AnthonyRegnier]

Thanks for your reply Krishna.

You have combined both "StartWith" with an AND (&&) operator so it doesn't return anything. I have replaced it with an OR (||) which works but breaks the rest of the code, the sorting isn't working anymore (with the status = high, etc).

That is something i have tried already in the past, it's the reason I'm posting this actually :p.

[Drrickryp]

Hi @AnthonyRegnier 

Sort(
    AddColumns(
                         Filter(
                                   'Stock Management',
                                     AND(
                                               StartsWith(Model, TxtIn_AllProductsSearchBox.Text),
                                      OR(Description=DpDwn_AllProducts.Selected.Result,DpDwn_AllProducts.Selected.Result="All"),
                                      OR(Status = "High",Status = "Low", Status = "Average", Status = "Out")
                                     )
                          ),
                          "SortRank",(Stock/Threshold)
         ), SortRank,
            If(
                 SortDescending1,Descending,Ascending
             )
)

See if this works.  I mostly changed the Filter part of the formula. 

[v-yutliu-msft · Accepted solution]

Hi @AnthonyRegnier ,

Could you tell me the data type of Status field?

If it is text type, then the formula that you used should have no problem.

If it is choice, type, then you need to use formula like this:

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status.Value = "High" || 
            Status.Value = "Low" ||
            Status.Value = "Average" ||
            Status.Value = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

Back to the problem of delegation.

The filter and sort part should have problem. The key problem is that AddColumns function is not delegate for sharepoint list.

Could you tell me whether your data source has more then 2000 records?

If no, you just need to change the delegation limit to 2000. Then delegation limit will not make your app perform bad.

If yes, since there's no delegation function that has the similar feather of AddColumns function, so you have two choices:
1)not use AddColumns function, just use Filter and Sort part.

2)use collection to get the result. There's no delegation limit in collection.

You need to firstly create collection then filter on that collection.

For example:

set the app's OnStart:

ClearCollect(collection1, 'Stock Management')

set the gallery's Items:

Sort(
   AddColumns(
      Filter(
         collection1,
         StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status = "High" || 
            Status = "Low" ||
            Status = "Average" ||
            Status = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

Best regards,

[AnthonyRegnier]

Hello guys!

Thank you so much for your answers!

@Drrickryp , 

You almost got it right for the multiple conditions StartWith within the filter, here is the code I got it working with:

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         Or(StartsWith(
            Model,
            TxtIn_AllProductsSearchBox.Text
         ), StartsWith(Title, TxtIn_AllProductsSearchBox.Text)) &&
         (
            Description = DpDwn_AllProducts.Selected.Result || 
            DpDwn_AllProducts.Selected.Result = "All"
         ) &&
         (
            Status = "High" || 
            Status = "Low" ||
            Status = "Average" ||
            Status = "Out"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

@v-yutliu-msft 

I have set the limit to 2000 thanks for the tip! I still get delegation warning - the column "Status" is actually a calculated column. Based on the number of item I have in stock versus the minimum required, it will display Low, Average, High etc...

I will have less than 2000 items in the SP list, does that mean I can keep the delegation warning safely? Is it wiser to create a collection maybe?

Thanks

Anthony

[v-yutliu-msft]

Hi @AnthonyRegnier ,

If you set the delegation limit to 2000 and your data source has less than 2000 record, the delegation warning will not affect your app's performance.

The delegation warning only affect your app when your data source has more then 2000 records(delegation limit number).

Best regards,