cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to form to only show items available based on entry in another field

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)

Category

Subcategory

Room

RoomEquipment

 

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How to form to only show items available based on entry in another field

@ktownshend
If you are new to PowerApps and forms it might be best to watch a video on this instead of me trying to explain on the forums.

This video for cascading dropdowns has over 100k views and is Shane Young’s most popular PowerApps video. Suggest you watch it to get the general idea. https://youtu.be/pkZG2boN7jQ

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: How to form to only show items available based on entry in another field

@ktownshend
If you are new to PowerApps and forms it might be best to watch a video on this instead of me trying to explain on the forums.

This video for cascading dropdowns has over 100k views and is Shane Young’s most popular PowerApps video. Suggest you watch it to get the general idea. https://youtu.be/pkZG2boN7jQ

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

View solution in original post

Highlighted
Community Support
Community Support

Re: How to form to only show items available based on entry in another field

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)

 

 

Best regards,

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: How to form to only show items available based on entry in another field

Thank you @v-yutliu-msft  and @mdevaney  for your replies.  I'm working with the Cascading Dropdowns and running into issues.  I can get the first one to pull the information I'm looking for.

 

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.

 

Distinct(Filter([@SubCategory].Description,[@Category].Deviation=Dropdown_Cat.Selected.Value),Subcategory)

 

Any ideas why the = isn't working?

 

Thank you 

Highlighted
Helper I
Helper I

Re: How to form to only show items available based on entry in another field

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.

Highlighted
Helper I
Helper I

Re: How to form to only show items available based on entry in another field

@v-yutliu-msft 

 

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

Employees

Category

SubCategory

Room

RoomEquipment

 

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.

 

Thank you 

Highlighted
Helper I
Helper I

Re: How to form to only show items available based on entry in another field

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.

 

Thank you 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,202)