Answered

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

(0) Share

Report

Posted on by ktownshend

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.

Filter options ba...

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Verified answer

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@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.
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,
ktownshend 83 on at

Like
a
(0)

Report
Copy link

Link copied!

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
ktownshend 83 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
ktownshend 83 on at

Like
a
(0)

Report
Copy link

Link copied!

@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

coding for dropdo...
ktownshend 83 on at

Like
a
(0)

Report
Copy link

Link copied!

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