cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sperry1625
Helper V
Helper V

Filtering a Dataset

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

View solution in original post

3 REPLIES 3
PaulD1
Super User
Super User

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

PaulD1
Super User
Super User

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

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.

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,857)