Hello Power Apps community! I really need your help.
I need to filter a gallery using all the distinct values present in a list, allow me to elaborate:
I have a main database with the product, pricing and region information and a secondary database with region and country information, just as follows:
|Product A||100||US East Coast|
|Product A||120||US West Coast|
|Product A||150||US Gulf Coast|
Secondary database (region_country referential):
|US East Coast||US|
|US West Coast||US|
|US Gulf Coast||US|
The users must input the country and obtain the products, prices and regions that apply to that country.
Today I am using multiple labels (11 total since there are countries that are a part of 11 regions), what happens is as following:
User inputs: US
Label1 = Last(FirstN(Distinct(Filter(Secondary_Database, Country = CountryInput.Text),Region),1)).Result
Label2 = Last(FirstN(Distinct(Filter(Secondary_Database, Country = CountryInput.Text),Region),2)).Result
Label3 = Last(FirstN(Distinct(Filter(Secondary_Database, Country = CountryInput.Text),Region),3)).Result
And then in the gallery:
Filter(Primary_Database, Region = Label1.Text || Region = Label2.Text || Region = Label3.Text) (goes up to 11)
My concern is that eventually more regions for a specific country will be introduced and I require a solution that doesn't need for me to create more labels and adding more OR statements in order to retrieve all the data.
Therefore, is there a way I can use ALL the items in a list to filter the Gallery?
I need the list to be distinct so something like = Distinct(Filter(Secondary_Database, Country = CountryInput.Text),Region) and then use all of the retrieved items to filter the gallery.
BTW the main database is over 10k lines but the secondary is around 1k.
I appreciate your support!
Hey! If you have 1 contry and many reagions you could create another combobox for user to choose the region.
You can filter the 2 dropdwon to only show regions from the cpuntry. If a country only has 1 region, the only one that appears is that. I have that filter in a app of mine, I can show you if u want
If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!
Check my LinkedIn!
The issue is that the user is oblivious to the regions, I need the result for the "US" input to be all of the products with their prices for all the regions that have US as part of their country. I can't have the users selecting the regions, as I mentioned before there are some countries that can have up to 11 regions associated. Imaging having the user select all of the regions each time they input a new country!!
Is there a way to have the application automatically select all available items in a combobox?
I appreciate your help!
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.