0

EDIT: I missed a major constraint in this question (I can't use Power Apps), so the question is no longer valid for me. I'm leaving it in case it's useful to anyone else, but I've posted a new, clearer question here: Filter or populate choice/lookup column based on another list without Power Apps? Or format column based on another list?.

I'm working on a project where I have a "Course" column (pre-populated) and a "School" column that I want to filter to only display schools offering the specified course. Most of the examples I see are the city/country model, where each city is only associated with a single country. In my case, I have about 65 courses and 20 schools, and any one of those schools can offer any number of those courses. So I can't just do a 1:1 lookup.

I'll also be getting the data relative to each school, rather than each course (so, an Excel sheet for each school with the names of the courses that they offer), and I'll need to do some additional filtering later in the process to assess if they have a limited number of spots in each course.

Is my best bet to just use a single list to hold my reference data, with three columns? I'd need ReferenceSchool (to hold the school name, which will be identical to the one of the choices in my School column), ReferenceCourse (to hold the course name), and Cap (for use later on when I'm allocating students to the schools). Then, for each school, I just create as many list items as they have courses.

In PowerApps I'd then need to filter to show the ReferenceSchool where Course = ReferenceCourse. Is that the right approach? I'd rather find out how to get the data in place before I get into the PowerApps part.

Does that make sense? And anything else that I should keep in mind? Is it like SharePoint Designer used to be, where once I modify the edit form in PowerApps it won't automatically update to show new columns or changes to columns unless I manually update it every time in PowerApps, or is there a way to get PowerApps to just impact this one drop-down field?

UPDATE

After a bit more research, I'm wondering if I could skip power apps and, instead, use Flow to define or restrict the options available in my choice field or lookup field. Any ideas on that approach?

Improve this question

1 Answer 1

Reset to default
0

I would without a doubt recommend that you put your data in SharePoint lists, and then use Power Apps to make your conditional/cascading dropdowns. It will by far be the best user expirience. And along the way you can build additional rules or logic when needed.

There are plenty of guides online on how to build the cascading columns in Power App. And lots of folks on here will be happy to help on questions once you are starting to set it up and you encounter issues.

I would start by creating the lists like Schools, Courses, Bookings (something like that). Then create the Power Apps and connect to those data sources (the lists). From there you can build your form :)

Best of luck!

Improve this answer
3
  • Thank you, and I apologize - I discovered something after posting this but didn't get back to update it before you responded: I don't have access to Power Apps (organizational constraint), so need to find a way to make it work just with SharePoint and Power Automate. Do you know if there is still a way? Or, if I can't restrict what appears in my School dropdown field, can I use conditional formatting in one list to check another list? That way I could at least indicate if a selection was invalid after someone entered it.
    – Darryl
    Commented Jan 21, 2022 at 16:58
  • 1
    @Darryl unfortunately Power Automate/Flow will not really help you in this situation. Power Automate/Flow is useful to set up automatic processes that respond to triggers, such as when a new item is created in a SharePoint list, or when an item is updated, and you want something else to happen automatically. You can't really use it to customize forms.
    – Dylan Cristy
    Commented Jan 21, 2022 at 18:44
  • Thanks, Dylan. It looks like the only workaround I can find is to use Flow to retrieve the list of schools that offer the specified course and then put that into a multi-line text field (the values will be too long in some cases for a single-line text). I can then show that in the list; it won't dynamically filter the options for users, but it will at least give them an easy reference to know which ones are valid choices.
    – Darryl
    Commented Jan 24, 2022 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.