Answered

SharePoint lists: how can I limit dropdown two by what is selected in dropdown one in a PowerApp?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Two SharePoint lists. First list: single column of text. Second list: one column of text, and one lookup column that refers to the first list. PowerApp: two dropdown menus; the first refers to the first list and the second refers to the second list. Question: how can I limit dropdown two by what is selected in dropdown one?

I have asked this question before in relation to the Common Data Service. I got a great and effective answer too! However, I'd like to avoid the cost of the CDS if at all possible 😛

Rant: My PowerApps journey has been fun and frustrating. There are so many cool tools built into PowerApps that allow you to build an app really quickly. However, as soon as you want to do something remotely fancy, you are in for a numbing experience.

The traditional development experience: Write code, run code, code breaks, dump variables and look at them to debug

The PowerApps development experience: Write formula, PowerApps says no, rant on PowerApps Community

It's so good when you get on a roll with a PowerApp and you end up with a functioning app in no time at all. But if you get stuck, you are truly stuck!

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Assuming you have used dropdowns (and not comboboxes which are different).

This is the generic code you would put in the Items property of the Dropdown for List2.

Filter( your_sharepoint_list2.text_column_name, lookup_column_name.Value = dropdown_list1.Selected.Value )

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney ,
Cheers for replying! I've tried that formula but I get data type errors. If I add '.Value' to either side of the comparison, I get 'name is invalid' errors. Here's a screenshot of the formula with the least number of errors!
In the data preview, you can see the Market column with [Record],[Record]. How I'd get the text from that record for comparison is beyond me.
As a note, here is the functioning CDS equivalent: Filter(Brands,Market.Market=Dropdown1.Selected.Market)
In the CDS, there are two columns for a lookup relationship; one is the friendly text value and the other is the unique identifier. Hopefully SharePoint has an equivalent, although trying the same CDS syntax for SharePoint lists doesn't work for me.
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous
I don't know anything about CDS so explaining the problem to me in those terms won't help, LOL. I know you are trying 🙂

To me it looks like Brands.Market is a record so you'll have to drill-down one level further. What appears in the autocomplete if you type this? Let me know the available fields. You want to choose the one that will match your dropdown.

Brands.Market.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney
No worries :D. Indeed, I feel I'm trying everything, even things that don't make sense!
Unfortunately nothing else appears when a type a dot after Market. If I click on Market, I see the column name 'Market' followed by [Record], but if I try to drill down further, nothing else appears (and things like Value, Text, Result etc... produce errors about not existing).
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
I would like to see what’s in Brands.Brand. Can you put this code in the OnSelect of a button and send me a screenshot from the View tab > Collections.

ClearCollect(Brands.Brand)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney ,
Thanks very much for your help on this one. Unfortunately I've run out of time so I'm going down an alternative path. As a note, the collection above returned a list of brands: so Title: Brand1, Brand2 etc...
You did give me an idea with using the collection functionality. I thought perhaps I could collect the data from SharePoint, store it in a collection and then refer to the collection. Unfortunately that had unexpected results! I added the SharePoint list data to a collection like so: ClearCollect(BrandCollection, {Brand.Brand, Brand.Market}). The end result was a collection with two tables inside rather than two columns of data. Table 1 had the list of brands. Table 2 had two more tables inside, each with one of the Markets!
Some final and useless information: I'm going to build this functionality in a C# Web Application. CDS is too expensive and PowerApps is too fiddly and unhelpful when it comes to implementing programmatic functionality.
Verified answer

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

The Market is the Lookup column, right?
The lookup column in SharePoint is a record containing Id and Value fields from the related record. Please try this code:
Filter(Brands, Market.Id=Dropdown1.Selected.ID)
Or
Filter(Brands, Market.Value=Dropdown1.Selected.Market)
Hope this helps.
Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-siky-msft ,
Thanks very much for your reply. Your first formula worked like a dream! I had given up on an inexpensive PowerApp solution but it looks like it's now a possibility.