I have a master SharePoint list that has several lookups attached to it. What I need to do is this:
When entering a new item, once the category is selected, I need to show only those sub-categories that are available based on that selection. Then I also have a roomequipment item that needs to be limited to just what is available based on what has been selected in the Room field.
i.e., if the category Microbiology is selected, then there are say 7 different types of microbiology that could be found. Then based on the room that this microbiology is found in, only certain equipment is selectable as the source of the microbiology.
My list in SharePoint are:
Corrective Action - Master list (where the form is)
My Subcategory list has a field for categoryID so that it ties to the category list.
My RoomEquipment list has a field for the RoomID so that it ties the Room list.
What I need to know is how to best write the formula that so if I select category 1, it brings up only those subcategory items that are connected to 1 and if I select room 7 only the equipment in room 7 shows.
I've tried a few different filter, sort and choose options I've googled but nothing is working as expected.
Room - Filter(RoomEquipment, DataCardValue22.Selected.Value = RoomID.selected.value)
Subcategory - Choices([@CorrectiveAction].CategoryID)
The attached file shows the various lists and how I have them set up.
Any help to get this filtering done would be greatly appreciated.
I'm very new to forms and using formulas, programming, etc., so I thank you for your help getting this figured out.
Solved! Go to Solution.
Hi @ktownshend ,
Do you want to filter Category/Subcategory/Room/RoomEquipment based on the selection of other three fields?
Is the rule of filtering between these four fields "And"?
Could you tell me the data type of Category/Subcategory/Room/RoomEquipment field?
Do you want to multiple choice or single choice when make selection on one field?
I assume that they are all text type, you select single choice in one field.
Then, please insert four drop downs.
set drop down1's Items:
Distinct(Filter(Master,Subcategory=drop down2.Selected.Result,Room=drop down3.Selected.Result,RoomEquipment =down4.Selected.Result),Category)
set drop down2's Items:
Distinct(Filter(Master,Category=drop down1.Selected.Result,Room=drop down3.Selected.Result,RoomEquipment =down4.Selected.Result),Subcategory)
set drop down3's Items:
Distinct(Filter(Master,Category=drop down1.Selected.Result,Subcategory=drop down2.Selected.Result,RoomEquipment =down4.Selected.Result),Room)
set drop down4's Items:
Distinct(Filter(Master,Category=drop down1.Selected.Result,Subcategory=drop down2.Selected.Result,Room =down3.Selected.Result),RoomEquipment)
What I'm trying to do next is based on the selection in Dropdown_Cat, I want Dropdown_SubCat to filter to only those options that have the value from Dropdown_Cat.
Here's what I have but it's not working, I'm getting an invalid argument type at Dropdown_SubCat.Items.
Any ideas why the = isn't working?
This information for Cascading Dropdowns really helped. I've been able to get that to work out now.
The only problem, once I make my selections and save, it's not updating the CorrectiveActions list with those selections. They are showing up blank.
I'm probably missing something else but wanted to say thanks for the help to get me this far.
Looking to see if I can clarify what I need.
My master list is called Corrective Actions
I have other lists as well that I pull information from, those are
What I'm trying and failing to do is make it so that when a user selects a Category, the SubCategory field limits their additional choices.
Then when the user selects the Room, the RoomEquipment is limited to what's in the room.
I've attached to this reply how my tables are all setup, what code I've worked with and have sorta working. The piece that's current missing is that when I create a new record, it's not populating back to the Corrective Action list and when I go in to try to edit, the selections are reverting to blanks.
So I think at this point I've really messed up my powerapps form and will probably have to start over from scratch.
The fields are lookup fields but yes text in nature. Do that all have to be a standard single line of text for this to work with the dropdowns?
I've got them doing what I want, but they are not writing back to my CorrectiveAction List, which is the master catch list for the entries.
Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.
Did you miss the call?? Check out the Power Apps Community Call here!
ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.