Multiple Filter in sharepoint

[russelln1]

Hi

I have only used Excel a s a data source and now working with Sharepoint.

With Excel Searching was straight forward and easy but with sharepoint it's not so much. I understand you need to use Filter.

I have 6 columns in a Gallery that I want the user to be able to search / Filter, ReportID, FName, LName, Status, Date and Department. How do I go about doing this whilst satisfying Delegation? The user will determine the search/filter words in the SearchBox

Many thanks

[BCBuizer]

Hi @russelln1 ,

The Search() function is not delegable, so try the StartsWith() function:

Filter(
	YourSharePointList,
	If(
		!IsBlank(TextInput1.Text),
		StartsWith(Column1, TextInput1.Text),
		true
	),
	If(
		!IsBlank(TextInput2.Text),
		StartsWith(Column2, TextInput2.Text),
		true
	),
	If(
		!IsBlank(TextInput3.Text),
		StartsWith(Column3, TextInput3.Text),
		true
	),
	If(
		!IsBlank(TextInput4.Text),
		StartsWith(Column4, TextInput4.Text),
		true
	),
	If(
		!IsBlank(TextInput5.Text),
		StartsWith(Column5, TextInput5.Text),
		true
	),
	If(
		!IsBlank(TextInput6.Text),
		StartsWith(Column6, TextInput6.Text),
		true
	)
)

[russelln1]

Hey @BCBuizer      

thank you for the response -

So i have put in my names (as below) It's not working (I have put this code into Gallery > Items Do i need to have 6 different SearchBox (TextInputs as in) or am I missing soemthing - I have the Yellow triangle for the delegable side of things still showing?

Filter(
RTWCapture_SP,
If(
!IsBlank(SearchBox.Text),
StartsWith(ReportID, SearchBox.Text),
true
),
If(
!IsBlank(SearchBox.Text),
StartsWith(EmployeeFName, SearchBox.Text),
true
),
If(
!IsBlank(SearchBox.Text),
StartsWith(EmployeeLName, SearchBox.Text),
true
),
If(
!IsBlank(SearchBox.Text),
StartsWith(InterviewDate, TSearchBox.Text),
true
),
If(
!IsBlank(SearchBox.Text),
StartsWith(Department, SearchBox.Text),
true
),
If(
!IsBlank(SearchBox.Text),
StartsWith(Status, SearchBox.Text),
true
)
)

Many thanks

[BCBuizer]

Hi @russelln1,

For this to work indeed you'll need some kind of mechanism to select the column you are applying the search to. In the previous example this is done by having 6 different text inputs, because this allows for multiple columns to be searched. However, you could also use a dropdown in combination with a single text input, using the dropdown to select the relevant column.

[russelln1]

@BCBuizer     IsBlank appears to be not delegable?

[BCBuizer]

Hi @russelln1 ,

The issue was more in the If statement than in IsBlank.

Try this please:

Filter(RTWCapture_SP,
    !IsBlank(TextInput1.Text)||StartsWith(ReportID,TextInput1.Text)),
    !IsBlank(TextInput2.Text)||StartsWith(EmployeeFName,TextInput2.Text)),
    !IsBlank(TextInput3.Text)||StartsWith(EmployeeLName,TextInput3.Text)),
    !IsBlank(TextInput4.Text)||StartsWith(InterviewDate,TextInput4.Text)),
    !IsBlank(TextInput5.Text)||StartsWith(Department,TextInput5.Text)),
    !IsBlank(TextInput6.Text)||StartsWith(Status,TextInput6.Text))
)

[russelln1]

@BCBuizer nope this doesn't work either the double brackets at the end of each line bring an error straight off the bat. removed those to remove the error but the dalagable side of things still not working

[russelln1 · Accepted solution]

@BCBuizer 

This works exactly as needed and is delegable:

Filter(RTWCapture_SP,
StartsWith(EmployeeFName, TextInput1.Text) || StartsWith(EmployeeLName, TextInput1.Text)|| StartsWith(Department, TextInput1.Text) || StartsWith(Status, TextInput1.Text)) 

[BCBuizer]

Hi @russelln1 ,

Can you please confirm the below data types of the columns you are trying to search?

ReportID                  Number

EmployeeFName     Text

EmployeeLName     Text

InterviewDate          Date

Department            Text

Status                      Text

As per the documentation StartsWith() is only delegable for Text and Complex data types: https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope...

Since I assume ReportID and InterviewDate are not delegable with StartsWith(), the = operator can be used:

Filter(RTWCapture_SP,
    ReportID = TextInput1.Text ||
    StartsWith(EmployeeFName, TextInput2.Text) ||
    StartsWith(EmployeeLName, TextInput3.Text) ||
    InterviewDate = TextInput4.Text ||
    StartsWith(Department, TextInput5.Text) ||
    StartsWith(Status, TextInput6.Text)
)

Next time please share which parts of the formula give delegation warnings; a screenshot always helps.

Edit: Changed data types for Department and Status to Text