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!

[susannarae]

@LRVinNC 

Each time I think I'm finally making progress, I feel like I mess something up and ruin it all! My Classroom Number is a dropdown list, but the Child's Name is a Combo Box, and of course I'm now getting errors on both. I'm attaching screenshots for all. I really appreciate your help with all of this; I would've given up otherwise!

Also, my combo box with the Child's Name is called 'DataCardValue5_2' and the Dropdown with the Room Numbers is 'Dropdown1'. The Combo Box is part of a Form, and the Dropdown is not (in case that matters for anything). I will reply again with a screenshot of the columns on the SP list.

[susannarae]

Screenshot of my SP list with Room Number and Name columns.

[LRVinNC]

Thank for you the additional details.  

The warning is because there is no "Value" selected.  It should be "Result" because you are using Distinct to eliminate the duplicates in your list of classrooms.  I believe that is also what is causing your error in the Depends on.  Let's see if we can get that resolved first. If it continues to give you problems, you might consider deleting the control and readding it.

Also you mentioned that the room number dropdown is not on a form and the name is.  Can you give me a screen shot of your UI so I can see how you have it set up?

[susannarae]

@LRVinNC This is my current UI. Would it be easier to filter the names by the Room Number in the Form itself instead of by the independent dropdown?

The Room Number in the Form is set to auto-populate based on the selection in Dropdown1. My reason for this is that eventually I'm going to have multiple mini-Forms on each page for teachers to input their students' attendance in (so that each student has an individual row), and selecting the room number once at the top and then having that section auto-populate on each Form below it would be faster for them.  Hopefully that makes sense.

[LRVinNC · Accepted solution]

If I understand your plan, I think that design is going to give you fits.  I'm playing with a possible format that I think might be easier for you and for your user.  Here you select the classroom and specify the date.  A list of students pops up below.  The user checks the box if they were present and the attendance date is applied to their records automatically.  If they make a mistake and need to uncheck, the attendance record for that date is removed.

Additional fields could be added but I just kept this simple.  You will need two lists no matter how you set up your app.  One still will only have the student and room number.  This gives your complete list of students in a classroom.  It will be the list used to create your dropdowns/combo boxes.  The second list will be similar to the one you have (although I will recommend a simplification), and will contain the detailed attendance records with one record per student/date attended combination.  If a student didn't not attend on a specific date, they will not have a record in this second file.  They will ALWAYS maintain the record in the first file as long as they remain in the class.  Does that make sense?

I have to go out for a few hours, but if you think this will be useful for you, I will cleanup what I put together a little bit when I get back and send you the app to play with.  

[susannarae]

@LRVinNC that sounds and looks AMAZING! There are a few tweaks I'd need to add in, such as virtual v. in-person attendance and the different options for why someone is absent (sick, COVID-related, etc.), but I think that that app format would work MUCH better--thank you so much!

[LRVinNC · Accepted solution]

OK.  The sample I worked up wound up slightly different to support two things:

1.  Your ability to easily add more fields

2.  The ability to see and correct the history for a student.  

I created two lists:  ClassRoster and Pontiac Attendance.  You will need to create/modify these two files.  You'll either need to change fields names in yours files to match the sample app or you'll have to change the sample app to match your field names.  I suggest the former at least to start.  You'll need to add all the classrooms and students eventually in ClassRoster.  For now I just included the 3 you listed above.  

There are two screens - HomePage and ChildDetail. Here's how it looks.  On HomePage you select a classroom.  The list of students in that classroom is displayed.  Click on > to open the ChildDetail page.

The detail page displays the child's name and classroom at the top.  On the left is an attendance history.  When you add additional fields, you can add them to this gallery and display them as well as the date.  I just made it simple to start.  To report a new attendance date for a child, click the Report Attendance button and a form will open up on the right. 

Right now it only includes two fields:  ChildID (which has visible set to false because the user doesn't need to see it)  and AttendDate.  You can add other fields to this form.  User clicks the save button and a new record is submitted for the date selected.  This isn't a sophisticated app.  It does check to see if there is already a record on file for the currently selected date.  If so, it shows an error message.  If not already on file, it adds the record.  There is a delete icon for each row that you can use to eliminate any erroneous reportings.  

Use the return button to go back to the HomePage to either select another student in the same classroom or select another classroom. 

Download the attached app and import it into your environment.  Here is info on importing the app:  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/export-import-app 

I hope this helps you get past your problem and continue development on the app.  If this provided you the help you needed, I'd appreciate it if you would mark it as a solution.  Thanks and good luck!

[susannarae]

Thank you so much! I think this will work really well, I really appreciate it!