Formula worked with Excel data source, but not Sharepoint.

[JamesonRH]

Hello! I appreciate any help I can get on this. I am a teacher building an app for my school to award points to students for good behavior (think house points in Harry Potter). I originally used Excel as my datasource, but because I don't like the idea of crafty students finding the excel file in their OneDrive and editing it, I decided to move my datasource to sharepoint. Everything transitioned well, except one particular formula:

Filter(StudentData, If(Checkbox9.Value =true, Grade ="9") || If(Checkbox10.Value =true, Grade = "10") || If(Checkbox11.Value =true, Grade = "11") || If(Checkbox12.Value =true, Grade = "12") || If(Checkbox6a.Value =true, Teacher = "Gedvilas") || If(Checkbox6b.Value =true, Teacher = "Braden") || If(Checkbox6c.Value =true, Teacher = "Wills") || If(Checkbox7a.Value =true, Teacher = "Ryan") || If(Checkbox7b.Value =true, Teacher = "Sullivan") || If(Checkbox8a.Value =true, Teacher = "a") || If(Checkbox8b.Value =true, Teacher = "b"))

Now, I'm still very new at this. This is the only formula I could come up with that would filter a dropdown list of student names based on a combination of checkboxes (some of the teachers, myself included, teach classes with multiple grade levels, so I'd like to filter the student names in the list by grade level, with the option of listing a combination of different grades in the dropdown). Our school is very small; each grade level has one or two cohorts of students, with 6th grade having three. For those grades, instead of filtering by grade level, I chose to filter by Homeroom teacher. This formula worked perfectly in Excel, but in Sharepoint, I get a blue indicator that the formula can't be processed remotely, and the dropdown list of student names is blank. We have a total of 239 students in the list, so it's not like I'm hitting the 500 limit even if I had all of the grades selected. Any advice on how to fix this, or perhaps a revised formula?

[v-yamao-msft]

Hi JamesonRH,

What’s the column type of Teacher column in your SharePoint list?

I try your function on my side with the similar configuration. The function can return values without issues. Though I am also getting the blue dot suggestions.

I test with both Person or Group column type and a single line of text on my side.

On the app, I add four Check box controls, and a Dropdown control.

Say the Teacher column is a Person or Group column, I use the function likes below:

Filter(List2,If(Checkbox1.Value=true,Title="grade9")||If(Checkbox2.Value=true,Title="grade10")||If(Checkbox3.Value=true,Manager.DisplayName="mao mabel")||If(Checkbox4.Value=true,Manager.DisplayName="yanan mao"))

Say the Teacher column is a Single line of text column I use the function likes below:

Filter(List2,If(Checkbox1.Value=true,Title="grade9")||If(Checkbox2.Value=true,Title="grade10")||If(Checkbox3.Value=true,teacher="mabel")||If(Checkbox4.Value=true,teacher="yanan"))

They are both working fine.

Besides, I think it isn’t a delegation issue. A blue dot suggestion makes it easier to know what is and is not being delegated, the authoring experience provides blue dot suggestions when a formula contains something that cannot be delegated. Please check this doc for more details:
https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

Best regards,
Mabel Mao