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

Is it possible to filter dropdown on SharePoint lookup field's another column

I have to SharePoint lists: 1) Machine Faults and 2) Machines.

 

In Machine Faults I have a Lookup column Machine, which points to the Machines list.

In Machines list I have two columns: Machine Name (Text) and Department (Choice).

 

In PowerApps Form I want to have first a Department dropdown, which then filters a Machine dropdown. So Machine dropdown would only show the machines in the particular Department.

 

How could this be achieved? 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Finnair 

On your first dropdown (Departments), set the Items property to:

Sort(
    GroupBy(Machines,
        "Department",
        "_data"
    ),
    Department
)
    

 

On the second Dropdown, set the Items property to:

yourDepartmentDropdownName.Selected._data

 

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@Finnair 

On your first dropdown (Departments), set the Items property to:

Sort(
    GroupBy(Machines,
        "Department",
        "_data"
    ),
    Department
)
    

 

On the second Dropdown, set the Items property to:

yourDepartmentDropdownName.Selected._data

 

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks @RandyHayes 

 

I think the direction is right, but I'm still having issues.

I realized my second control is a Combobox.

 

Since in my Department in Machines list is a choice field, it's not directly recognised (or allowed) by PowerApps.

 

I had to do this in Department dropdown's (First dropdown) Items:

 

Sort(GroupBy(AddColumns(Machines;"NewDepartmentname";Department.Value);"NewDepartmentname";"Departmentgroup");NewDepartmentname)

 

This is error free and I got the first dropdown populated.

 

I set Machine combobox' (second "dropdown") Items:

 

DepartmentDropdown.Selected.Departmentgroup 

 

This also is error free, but I won't get the second dropdown populated: it's empty.

 

Update:

 

I tracked this: The second dropdown is populated, but the entries are only empty rectangles with a dashed line border. I wonder what's the problem with the visibility.

 

Maybe the visibility has something to do with the original Title column of the Machines list: I have changed it to "Machinename". In PowerApps I don't define Machinename in any way.

 

 

 

Thank you @RandyHayes 

 

I managed to solve the issue. I had to make a little addition to

 

DepartmentDropdown.Selected.Departmentgroup  =>

 

Sort(DepartmentDropdown.Selected.Departmentgroup.'Machinename (Title)'; Ascending) 

 

So, like I assumed, there were missing the Column Name from the SharePoint list. I began to add Sorting and I realized the column title thing.

Luckily it came as a predictive choice after I hit period after Departmentgroup 🙂

 

Thanks a lot!

 

 

RandyHayes
Super User
Super User

@Finnair 

Yes, actually what you needed to do was to set the display field on the Combobox to the Machinename field.  When you provide a full record to a combobox in the items property (which is what would be there from the groupby of the datasource) it tries to guess the field you want to display.  It's usually wrong!  So you just need to set it in the edit fields of the combobox.

 

What you did was to narrow your table.  When you specify a column name at the end of a table name, the result will be a table with just that column.

So, Department group is a table of records with all the columns associated with it.  If you do DepartmentGroup.'Machinename (Title)' then you are narrowing the table to only have that column.  

This is perfectly fine as long as you do not need to know any other columns.

 

When it is not good is if you need to do more with the record elsewhere.  For example, seeing the Title list by itself is fine, but if you ever needed to reference, let's say, the ID of that record, then you would not have it.  So just keep that in mind when narrowing...no other columns will be available after narrowed.

 

Glad you got working what you needed!!  Happy New Year!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Finnair
Resolver I
Resolver I

I use this method in a form.

 

So, I got the dropdowns working but I found a new problem: the second dropdown doesn't sen anymore the Program title to SharePoint list: the field is empty. I don't know if it's about logic or is the new Items formula missing some definition?

 

Originally the second dropdowns Items was:

 

Choices([@MachineFaults].MachineName)

(MachineName is a lookup field to Machines List)

Now it is:

Sort(DepartmentDropdown.Selected.Departmentgroup.'Machinename (Title)'; Ascending) 

 

 

Update:

 

I found a hint: The DataCard's Update should be modified.

RandyHayes
Super User
Super User

@Finnair 

Sorry for delay...trying to catch up on all the posts.  Are you still experiencing issues that you need help with?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

Yes, thanks, this is ok.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,324)