Combobox filtering different lists based on the value before

[Anonymous]

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!

[eka24]

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

[WarrenBelz]

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.

[Anonymous]

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.

[eka24]

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

[Anonymous]

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".

[Michael_Shen_JT]

@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

[WarrenBelz]

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.

[WarrenBelz]

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.

[Anonymous]

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.