Filter dropdown choices based on previous input in Sharepoint form

[susannarae]

Hi All,

I'm looking to filter the choices in a SharePoint dropdown PowerApp form based on the input of a room number: 

Name	Room Number
Sebastian Rood	B104
Viola Taylor	B104
Osiris Jinx	B111

If someone inputted 'B104' in the Room Number text box on the form, I'd like only Sebastian's and Viola's names to show up as choices in the dropdown for attendance reporting. This way teachers won't have to scroll through 100+ names to pick out the kids assigned to them to log their attendance. I'm attaching a screenshot of the basic framework I've got right now for a better visual; I have it set so the date and room number both autofill based on the top dropdown selections. Any and all help is greatly appreciated!

[LRVinNC]

What you are looking for is a dependent dropdown.  Your field names will vary from this example but this should give you an idea of how to set it up.

I have two columns in a SP List, TestList.   Title contains the student's name and RoomNumber is self explanatory.

I have two dropdowns, Dropdown3 with the room number and Dropdown4 with the students.

In DropDown3, I use the Distinct function in the Items Property so that a RoomNumber is only displayed once.  If I didn't do this, B104, for example, would appear once for each student that was in that room.  The distinct eliminates the redundancies, leaving only one B104.

Then I go to Dropdown4 on the Properties tab.  I want to display Title (containing the student's name).  Then I click on Depends on... then I fill out the 4 choices to link the two dropdowns.  In the first pair, you'll notice that it says Result, not RoomNumber.  This is because of the use of the Distinct function.  This says to use the Result of that function.  When you fill out these 4 fields, it will automatically generate the correct syntax for the filter shown in the Items property of Dropdown4 (the lower middle image).  

If I select B104, I see only Viola and Sebastian.  If I select B111, I see only Osiris.

[WarrenBelz]

Hi @susannarae ,

Firstly if your first dropdown Items are this

Sort(
   Distinct(
      YourListName,
      'Room Number'
   ),
   Result
)

Then the second drop down Items could be this

Sort(
   Filter(
      YourListName,
      'Room Number' = YourFirstDropDownName.Selected.Result
   ).Name,
   Name
)

which will sort both in alphabetical order.

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.

[susannarae]

Will this work in a Form on SharePoint as well?

[susannarae]

Will this work in a Form on SharePoint as well? And if so, will I manually need to type in all the names? I can do that (especially as it'll save me a lot of time in the long run), but just wanted to confirm.

[LRVinNC]

@susannarae The room numbers and names will come from your Sharepoint list.  If they aren't already loaded into a list, then you will need to type them in along with the room numbers.  Once those are loaded to the list, you'll be able to read that data to populate your two dropdowns.

[susannarae]

I tried typing in the formula you provided, and it seems to be working right up until the '.Selected.Result' part, at which point it auto-underlines the entire thing in red and gives me an error code that reads 'The function 'Filter' has some invalid arguments.' It accepts it if I put in Value instead of Result, but I'm not sure where to input the names of who's in which class--or even if it's the right formula. I'm still somewhat new at PowerApps, and I'm sure it's something I'm overlooking, but I'm not sure what that something is!

[LRVinNC]

Can you post a screen shot of your code and error?  What is the name of your sharepoint list and the room and name columns in your list?

[susannarae]

@LRVinNC Attached are screenshots of the error code with Result inputted, and one without an error code when 'Result' is replaced with 'Value.'

I'm also not sure how to connect the names to the specific room numbers; in my SP list they're all in one big dropdown (as I'd like them to all be exported to the same column). 

My list name is 'Pontiac Attendance', Room Number column is 'Classroom Number', and the children's names are choices under a column called 'Child's Name.'

[LRVinNC]

I see you are using a combo box rather than a dropdown, which is fine but will look slightly differently.  With a combo box, it will look like this.  The exact syntax is going to depend on what the field names are in your combo boxes.   What is the name of your room number dropdown or combo box?  Can you show me a screenshot of the properties for that control?

As for setting up your data, what is the structure of your sharepoint list?  
For example, in my test it looks like this: