Filtering Drop Down control using a SharePoint Managed Metadata column

[JoeFox79]

Hi,

I'm having some trouble filtering a drop down control using a the value selected in another drop down control which is linked to a SharePoint Managed Metadata column.

Scenario:

I have a SharePoint list called 'Projects' with three columns:

1. Client (Managed Metadata, column internal name is 'Client')
2. Project Name (Text, column internal name is 'Title')
3. ProjectTask (Text, column internal name is 'ProjectTask')

A Client has many Projects, and a Project has many Project Tasks.

What I want to achieve is effectively a cascading drop down mechanism using three drop down controls, so when the 'Client' is picked in the first drop down control, only the corresponding 'Project Name' data appears in the second drop down control, and when a 'Project Name' is selected from the second drop down control, only the corresponding 'ProjectsTask' data appears in the third drop down control.

If I used text for each column I can get it all to work but as the 'Client' data is stored in the Term Store (and therefore surfaced in the list via a managed metadata column), I'm having problems.

The 'Client' drop down control is called 'Client_Dropdown' and has the following formula for the 'Items' property:

Sort(Choices(Projects.Client),Value)

This works fine, returning the values within the 'Client' term set in alphabetical order.

The 'Project' drop down control has the following formula:

Filter(Projects,Client=Client_Dropdown.Selected.Value)

This throws an error at the = symbol that reads: "Invalid argument type".

It's frustrating me that I can't work out why this is happening.

If I switch to a different site column that uses plain text to represent the 'Client', it all works fine so I can only assume the issue is with the column type.

I wonder if anyone has been in a similar situation and managed to solve the problem.

Thanks,

Joe

[RandyHayes · Accepted solution]

@JoeFox79 

Your issue is that you are trying to do a filter on a Managed Metadata column and treating it like it is just text...it's not.

    Filter(Projects,Client=Client_Dropdown.Selected.Value)  <== Client is a MMd column, not text - thus invalid.

The answer will depend on how you have your column configured.  If it is a simple setup where it does not allow multiple values and it is using the term store name as its value, then your formula will be this:

    Filter(Projects,Client.Label=Client_Dropdown.Selected.Value)

If it is not that simple of a setup on the column, you might need to do more.  If you look at the column in PowerApps, you will see that it has several properties on it - one of which, in this case to use, is the Label property.  Others may be needed in your solution based on your column setup.

Hope this helps some.

[JoeFox79]

Thanks @RandyHayes - knowing about the Client.label part has helped me resolve this.

One other thing I needed to do was to wrap the filter with a Distinct function so that only the relevant project names were returned.

Here is my final formula:

Distinct(Filter(Projects,Client.Label=Client_Dropdown.Selected.Value),'Project Name')

[Anonymous]

@JoeFox79  thanks for posting this, I am taking this as reference for one of my implementations. Question, where are you storing project names and project tasks? Is it stored as nested termset where your client info resides, please help.