Combobox filtering different lists based on the value before

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all,

As first field I have a LookUp Function that selects the country of the user respective to their user email.

LookUp('User Countries', UserMail = User().Email, UserCountry)

As a second step I've created a ComboBox. Up to now, the ComboBox always filtered one list.

Distinct(Filter('List', CountrySystem = CountryLabel.Text).Division, Division)

Now I've created different lists for the countries (Prices Indonesia, Prices Thailand) as I don't want that all User can access every lists. So I want that they can only filter the list respective to their country. I was thinking about doing this with an if formula but it's not working.

I've tried this:

Distinct(Filter(If(CountryLabel.Text = 'Indonesia', 'Prices Indonesia', 'Prices Thailand'), CountrySystem = CountryLabel.Text).Division, Division)

(I'm only using 2 Lists at the moment)

Anyone an idea how I can solve this?

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (17)

Answers (0)

Sort by

eka24 20,925 on at

Like
a
(1)

Report
Copy link

Link copied!

With this objective, you need cascading dropdown. This video from Raul will help achieve that:
https://youtu.be/URe94EYCSz8
Also from Shane Young
https://youtu.be/pkZG2boN7jQ

Kindly Give feedback

If you like this post, give a thumbs up. Where it solved your issue, Mark as a solution

Was this reply helpful? Yes No
WarrenBelz 156,293 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Anonymous ,
I posted this item today for a similar requirement - happy to elaborate further.
Also the videos mentioned by @eka24 are worth a watch.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I think that's a different case as I want to select from different lists. If I understood it right, they're are always using one list. This I did it before and it worked. But now I've created different Lists, so in the second ComboBox I want that the User can only filter one list, dependent on their country which was auto-selected in the first step.

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Please take a look at the post by @WarrenBelz
If that doesn't work then give us the Column names for both list.

If you like this post, give a thumbs up. Where it solved your issue, Mark as a solution

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

It's not working...

So for the first field I have a list called "User Countries". Here I have to columns, one with the email addresses and one is the country. With a lookup function it auto selects the country dependent on their e-mail address.

Now in the second step I'm using two different lists. One list is called "Prices Indonesia" and the other is called "Prices Thailand". So if now in the first field Indonesia was selected, I would like that the Use can filter the Division which are listed in the List "Prices Indonesia". If the first field is Thailand, I would like that the User can filter the Division which are listed in the List "Prices Thailand".

Was this reply helpful? Yes No
Michael Shen Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous from the formula you shared, I understand that you now have 2 lists for 2 countries, and when different user is using(or selected), you need identify the user's country and show them different comboBox content based on the user's country.

If in this case, first you can use Switch:
Set the Items properties of your comboBox to Switch(TextInput1.Text,"indonesia",Indonesia.ColumnName,"thailand",Thailand.ColumnName)
TextInput1.Text need be replaced with your user's country name formular
Indonesia.ColumnName/Thailand.ColumnName need be replaced with your data sources and columns

*doc about Switch/If control
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-if

Was this reply helpful? Yes No
WarrenBelz 156,293 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Ok @Anonymous , My post probably gets you some of the way, but you have some different requirements here.
Let's look at this from the start. Firstly, User().Email is not a delegable query, so set a variable at App OnStart
Set(vUserMail,User().Email)
Then set another Variable for the country
Set( vCountry, LookUp( 'User Countries', UserMail = vUserMail, UserCountry ) )
You now have vCountry as the User's country.
I am assuming from here that you have a list of items (I have re-read your post and it is not entirely clear in my mind) with the country reference and you only want the user to see a drop-down referring to categories belonging to that country. To avoid any further filters, I suggest (if you have not already), add the country name to the list (I will call it CountryName and the field for the ComboBox PriceList.
So back to the last item of my first post - your list would be
Filter( 'List', CountryName = vCountry, ) .PriceList

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 156,293 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,
I have had a further thought on the lookup you are trying to do between the user's country and the list item.
To save explaining how it all fits, I have posted an amended item below
Let's look at this from the start. Firstly, User().Email is not a delegable query, so set a variable at App OnStart
Set(vUserMail,User().Email)
Then set another Variable for the country
Set( vCountry, LookUp( 'User Countries', UserMail = vUserMail, UserCountry ) )
You now have vCountry as the User's country.
Now you need to do the bit you were trying - referencing the price list from the country - so do a Collection
ClearCollect( colPrices, {Country: "Indonesia", List: "Prices Indonesia"}, {Country: "Thailand", List: "Prices Thailand"}, {Country: "Vietnam", List: "Prices Vietnam"} )
and so on for the rest
So back to the last item of my first post - your list would be
Filter( colPrices, Country = vCountry, ) .List

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry for the late reply, I was travelling the last days.

The switch formula is working, thank you!
But how can I combine it with distinct?
I've tried the following:
Distinct(Switch(CountryLabel.Text, "Indonesia", 'IB Pricelist'.Division, "Thailand", 'Prices Thailand'.Division), Division)
But it's not working...

And how can I use the list that was selected for my next ComboBox? Because there I want to filter for the system in the same list I've filtered the division. I've tried to just include the switch formula again instead of one list name but it's not working.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Warren,

I've created the collection as you said but the last step doesn't work.. I want to filter the Division (it's a column in the lists) but when I use the your last formula there's nothing to select.
What am I doing wrong?

Was this reply helpful? Yes No