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

[Finnair]

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? 

[RandyHayes · Accepted solution]

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

[Finnair]

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.

[Finnair]

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]

@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!

[Finnair]

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]

@Finnair 

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

[Finnair]

@RandyHayes 

Yes, thanks, this is ok.