How can I filter a SharePoint Lookup Choice list based on list items that have a value in a ComboBox?

[Anonymous]

I have two SharePoint lists. ListA has two columns: Name and Topic.

ListB has a lookup column that pulls from ListA[Name]. When adding items to ListB using a PowerApp, I would like to use a ComboBox that pulls items from ListA[Topic], thus filtering the choices for the ListA[Name] lookup column so that it only shows entries with the specified topic.

Here is a sample list from ListA:

Name       Topic

Bill         Computers

Leslie        Music

Mike      Computers

If I select Computers in a ComboBox, I would like my choices for the lookup column in ListB to be Bill and Mike.

[LRVinNC]

What you want is called a cascading dropdown.  Here are two good resources to help you:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/dependent-drop-down-lists

Video:  https://www.youtube.com/watch?v=pkZG2boN7jQ

[Anonymous]

@LRVinNC,

Thank you for providing that link. Unfortunately, I think that will not work as the source list (ListA) column allows for multiple selections.

[v-yutliu-msft]

Hi @Anonymous ,

If you set the listA[name] filed's data type to lookup, it should be related to listA's Name field directly.

But as your description, the listA[name] filed's data is filtered according to the listA[Topic]'s choice.

In this situation, the data in the listA[name] filed will not be updated successfully.

As an alternative way, I recommend you set listA[name] filed's data type to text.

By the way, could you tell me the listA[Topic]'s data type?

I assume that it's lookup filed.

Here's my test for your reference:

1) change the textinput control to combo box control. (listA[name])

2)Unlock the control

3)Set the combo box control's Items:(DataCardValue3 is listA[Topic]'s contro name)

Filter(listA,Topic in DataCardValue3.SelectedItems.Value).Name

4)Set the card's Update belongs to listA[name]:

Concat(ComboBox1.SelectedItems,Name&",")

Best regards,

Community Support Team _ Phoebe Liu

[Anonymous]

@v-yutliu-msft,

Thank you for providing that information. Your suggestion certainly sounds like it might work for me, but I am having a difficult time putting it into practice. I also realized I forgot that there are three other lists involved, so I will restate my scenario:

NamesList is structured like so:

Name (Text)

Bill

Leslie

Mike

Mark

TopicsList is structured like so:

Topic (Text)

Computers

Music

CDs

CatalogList is structured like so:

Catalog Item (Text)       Topic (Multi-select TopicsList Lookup)

1A10 Laptop                           Computers

RC10 CD                                  Music, CDs

A1230 Ferrari                               Cars

OfficeCatalogList is structured like so:

Catalog Item (CatalogList Lookup)       Topic (CatalogList Lookup)

1A10 Album                                                          Music

RC10 CD                                                            Music, CDs

A1230 Ferrari                                                          Cars

Entries has the following:

Name (NamesList Lookup)       Catalog Item (OfficeCatalogList Lookup)

Bill                                                        1A10 Album  

Bill                                                           RC10 CD

Mike                                                     A1230 Ferrari 

My goal is to create a PowerApp that allows users to add items to the Entries list but filter the CatalogItem choices based on the associated Topic in the OfficeCatalogList.

For example, if Mike selects Music as the Topic from the OfficeCatalog drop-down, I want the choices for the CatalogItem from the OfficeCatalog drop-down to only show 1A10 Album and RC10 CD:

I hope this helps. My deepest apologies for not providing all of this information earlier.

[v-yutliu-msft]

Hi @Anonymous ,

I've made a similar test for your reference:

Insert two forms.

Form1 choose Name and Catalog Item filed, set its data source: Entries

Form2 choose Topice field, set its data source: OfficeCatalogList

Unlock Catalog Item's data card, set its Items:

Filter(OfficeCatalogList,DataCardValue23.Selected.Value in Topic.Value)

Set the datacard's Update:

DataCardValue19.Selected.'Catalog Item'

Then, the Catalog Item will filter according to the topic in OfficeCatalogList.

Best regards,

Community Support Team _ Phoebe Liu

[bronsonplanb]

Not certain if this helps, but consider "Incident Reports" as the SP list and "Unit# Reporting" (which is a lookup column in SP) as the Column.  To filter out any of the Unit# Reporting that start with an '*' from the from the Choices(), the following:

Filter(Choices('Incident Reports'.'Unit# Reporting'), Not(StartsWith(Value, "*")))