cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Sharepoint List Search Issue

Hi guys,

 

I've created a PowerApps automatically from a Sharepoint Contact List.

The Browser Gallery Search box only allows me to filter content based on SURNAME Column.

 

The filtering formula is this one :

SortByColumns(Filter([@Contacts]; StartsWith(Surname; TextSearchBox1.Text)); "Title"; If(SortDescending1; Descending; Ascending))

The Browser Gallery show Surname / Department and Contact fields from my list)

How could i filter content based on MORE / All columns from my list ?

 

Is it only able to search within the values displayed in Browser Gallery ?

 

Thanks,

Regards,

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Sharepoint List Search Issue

Hi @LaurentH ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery items based on multiple columns rather than just the Surname column?

 

Based on the needs that you mentioned, I think the Filter function could achieve your needs. Please consider modify your formula as below:

 

SortByColumns(
Filter(
[@Contacts];
StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Departemnt; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || ...
);
"Title";
If(SortDescending1; Descending; Ascending)
)

 

 

In addition, if all columns which you want to use to filter your Gallery items are Single text type column, you could also consider modify your formula as below:

 

SortByColumns(
              Search([@Contacts]; TextSearchBox1.text; "Surname"; "Department"; "Title"; ... ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

More details about the Search function, please check the following article:

Search function

 

If these columns you want to use to filter your Gallery contain Complex type column (e.g. Choice, LookUp, Person, ... etc), please consider modify above formula as below:

SortByColumns(
              Filter(
                       [@Contacts]; 
                       StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Departemnt; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || TextSearchBox1.Text in ChoiceCoumn.Value || TextSearchBox1.Text in PersonColumn.DisplayName || TextSearchBox1.Text in LookUpColumn.Value ...      
              ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support
Community Support

Re: Sharepoint List Search Issue

Hi @LaurentH ,

Based on the issue that you mentioned, I think you have faced a Delegation warning issue with your formula. If you have faced a Delegation issue with your formula, it means that you could not delegate the data process to your data source, instead, you could only process your data locally in your app.

In default, you could only process 500 records at most locally. You could consider change this limit to maximum value -- 2000, then you could process 2000 records locally in your app.

More details about the Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

If the amount of your SP List records is not more than 2000, you could ignore this warning issue.

 

If you want to get rid of this warning issue in your app, please modify your formula as below:

SortByColumns(
              Filter(
                       AddColumns([@Contacts]; "DepartmentValue"; Department.Value; "MainContact"; 'Main Contact'.DisplayName);
                       StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Name; TextSearchBox1.Text) || StartsWith(Function; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || StartsWith(DepartmentValue; TextSearchBox1.Text) || StartsWith(MainContact; TextSearchBox1.Text)       
              ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Sharepoint List Search Issue

Hi @LaurentH ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery items based on multiple columns rather than just the Surname column?

 

Based on the needs that you mentioned, I think the Filter function could achieve your needs. Please consider modify your formula as below:

 

SortByColumns(
Filter(
[@Contacts];
StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Departemnt; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || ...
);
"Title";
If(SortDescending1; Descending; Ascending)
)

 

 

In addition, if all columns which you want to use to filter your Gallery items are Single text type column, you could also consider modify your formula as below:

 

SortByColumns(
              Search([@Contacts]; TextSearchBox1.text; "Surname"; "Department"; "Title"; ... ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

More details about the Search function, please check the following article:

Search function

 

If these columns you want to use to filter your Gallery contain Complex type column (e.g. Choice, LookUp, Person, ... etc), please consider modify above formula as below:

SortByColumns(
              Filter(
                       [@Contacts]; 
                       StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Departemnt; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || TextSearchBox1.Text in ChoiceCoumn.Value || TextSearchBox1.Text in PersonColumn.DisplayName || TextSearchBox1.Text in LookUpColumn.Value ...      
              ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Regular Visitor

Re: Sharepoint List Search Issue

Hi @v-xida-msft ,

 

Thanks for you answer.

 


@v-xida-msft wrote:

Hi @LaurentH ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery items based on multiple columns rather than just the Surname column?

 


Yes indeed

I managed to achieve this thanks to you with this code :

 

SortByColumns(
              Filter(
                       [@Contacts]; 
                       StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Name; TextSearchBox1.Text) || StartsWith(Function; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || TextSearchBox1.Text in Department.Value || TextSearchBox1.Text in 'Main Contact'.DisplayName       
              ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

I just have a warning telling me "Delegation warning the filter part of this formula might not work correctly on large data sets"

 

Not sure about what it means ?

 

Regards

Highlighted
Community Support
Community Support

Re: Sharepoint List Search Issue

Hi @LaurentH ,

Based on the issue that you mentioned, I think you have faced a Delegation warning issue with your formula. If you have faced a Delegation issue with your formula, it means that you could not delegate the data process to your data source, instead, you could only process your data locally in your app.

In default, you could only process 500 records at most locally. You could consider change this limit to maximum value -- 2000, then you could process 2000 records locally in your app.

More details about the Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

If the amount of your SP List records is not more than 2000, you could ignore this warning issue.

 

If you want to get rid of this warning issue in your app, please modify your formula as below:

SortByColumns(
              Filter(
                       AddColumns([@Contacts]; "DepartmentValue"; Department.Value; "MainContact"; 'Main Contact'.DisplayName);
                       StartsWith(Surname; TextSearchBox1.Text) || StartsWith(Name; TextSearchBox1.Text) || StartsWith(Function; TextSearchBox1.Text) || StartsWith(Title; TextSearchBox1.Text) || StartsWith(DepartmentValue; TextSearchBox1.Text) || StartsWith(MainContact; TextSearchBox1.Text)       
              ); 
              "Title"; 
              If(SortDescending1; Descending; Ascending)
)

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,038)