cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sperry1625
Resolver I
Resolver I

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,154)