Filtering a Dataset

(0) Share

Report

Posted on by sperry1625

765 Super User 2024 Season 1

This should be easy but I seem to be fumbling this.

I have a SharePoint list. It is used as a lookup for another list. In my App I need to show two drop downs. The first shows a Distinct list of items from a column in the list (Country/Region). The second list will show another column from the list but filtered based on the selection in the first. I typical cascade lookup scenario.

The list (we'll call it SelList) has a column in it called Country/Region. This is a lookup column to another list that contains all Country/Regions (we'll call it CtryList). The first drop down needs to display a Distinct list of all the Country/Region values in the SelList.

NOTE: I have noticed one thing, if I just assign the entire SelList to a ComboBox I cannot select the Country/Region column as a Display Field. Is that because it is a lookup column?

I have even tried doing a Distinct on the SelList and assigning the result to a combo box but the list has the correct # entries, but no display data.

Any help would be appreciated.

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

PaulD1 2,914 on at

Like (1)

Report
Copy link

Link copied!

Edited: Will try to come up with a better solution...

Was this reply helpful? Yes No
PaulD1 2,914 on at

Like (1)

Report
Copy link

Link copied!
I don't normally work with SharePoint as a datasource (for many reasons, including exactly these sorts of issues) but I've had a stab at this problem.

I have experimented with the Dropdown, Combobox and list controls and I don't think any of them will allow you to display a 'complex type' such as a Look Up field, however, if you don't mind tweaking your UI a little I think you should be able to achieve this.

I was able to get the following to work with a Gallery:

Distinct( Filter('Traffic Light Setting', 'Project ID' = Text(conProjects.ID)),'Colour:Status')

In this formula, 'Project ID' is a look up to another List and is allowing me to filter as long as I convert the ID to text. Colour is a look up to another list and has been configured to also show Status.

So, I was able to get the list to filter based on an ID and to perform a Distinct on the value of a looked up column - I can show those values in the Gallery by doing:

ThisItem.Result.Value

So, if you can work with Galleries instead of Dropdowns you may be able to get this work.

That said, I would advise strongly against using SharePoint if you have any other viable alternative (SharePoint isn't a database and trying to make it work like one involves a lot of effort) and if not, avoid using LookUp columns. Just store the ID of the related item as number field and manage the interface through Power Apps (you want to avoid anyone going direct to the SharePoint list anyway as that will bypass any business logic enforced in your Power App).

Was this reply helpful? Yes No
Verified answer

sperry1625 765 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Thank you @PaulD1 for your response.

I actually found a solution that is working well for me. Based on several different posts on some other related, and unrelated, topics I was able to create a solution that works.

First, here is the complete "Items" property for my Dropdown:

RenameColumns( AddColumns( DropColumns( GroupBy( AddColumns( 'Tax Types', "Country", 'Country/Region'.Value, "CountryKey", 'Country/Region:Key'.Value ), "Country", "CountryKey", "Extra" ), "Extra" ), "Active", true ), "Country", "Country/Region", "CountryKey", "Name" )

Let me explain.
Step One: How Do I Use a Lookup Column
Answer: Use AddColumns

AddColumns( 'Tax Types', "Country", 'Country/Region'.Value, "CountryKey", 'Country/Region:Key'.Value )

AddColumns basically converted my Lookup column to real data columns that I could use elsewhere.
Step Two: How to Get Distinct Country Values
Answer: Use GroupBy

GroupBy( AddColumns( 'Tax Types', "Country", 'Country/Region'.Value, "CountryKey", 'Country/Region:Key'.Value ), "Country", "CountryKey", "Extra" )

GroupBy created a new table that gave me Distinct values for Country utilizing the new columns created with the AddColumns.
Step Three: Match Table Structure
Answer: Manipulate Table Structure to Match Need
This last step is specific to my requirement. I needed the Table to have a specific structure so I used DropColumns, AddColumns and RenameColumns to get the table structure I needed. Others may not need to do this for their solutions.

I hope this will help others that may come across this problem.

Was this reply helpful? Yes No