cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JanLektonen
Advocate III
Advocate III

How to filter choices() on Combobox

I am not apple to filter Sharepoint data where the field is a lookup-field in a form.

 

Is it possible to filter like this:

 

Filter( Choices(ProjectTasks.Sprint); Project.Value="PROJ0112")

 

Unfortunately the above is not working when using FILTER.

 

If I do not use FILTER - it works but lists all data without filterering like: Choices(ProjectTasks.Sprint)

 

Please help me understand how to filter the new Combobox in a Form using the commando Choices(dbsource.field)

1 ACCEPTED SOLUTION

Accepted Solutions

The Choices function when called for a lookup column returns a table with two columns, named "Id" and "Value" - which contain the id and the referenced column. It doesn't return all columns for the referenced list. So if you want to return only a subset of items from the referenced list, you need to use the in operator to only return choices that match a specific condition on the second list.

View solution in original post

12 REPLIES 12
CarlosFigueira
Power Apps
Power Apps

The Choices function returns a table with two columns: Id and Value. If you want to filter by the value of the choice, you can use a Filter expression similar to the one below:

Filter(Choices(ProjectTasks.Sprint); Value = "PROJ0112")

Hi @CarlosFigueira

 

Thanks for you reply. I can see that the code is accepted but it will not work as intended.

 

The datafield Sprint is a Sharepoint Lookup-field on the table ProjectTasks pointing to the table ProjectSprints and I have to filter the combobox based on all the entries in the table ProjectSprints where the Column Project="PROJ0112".

 

So what I woul like to do would be something like this (red): 

Filter(Choices(ProjectTasks.Sprint); Project.Value = "PROJ0112")

 

If I use Value="PROJ0112" nothing is listed? 

 

But I can filter based on the Value like this: Filter(Choices(ProjectTasks.Sprint); Value="All Tasks") but that is only on the value inside the listbox. I would like to filter the list based on the Project in the lookup table ProjectSprints.  

 

Best regards, Jan

Got it; in this case, you can filter the result of the "Choices" based on another filter on the linked table, something along the lines of

Filter(
    Choices(ProjectTasks.Sprint);
    Value in Filter(ProjectSprints, Project = "PROJ0112").Title)
Jeremyleff
New Member

Carlos, can you explain what's happening here in this last formula?

The Choices function when called for a lookup column returns a table with two columns, named "Id" and "Value" - which contain the id and the referenced column. It doesn't return all columns for the referenced list. So if you want to return only a subset of items from the referenced list, you need to use the in operator to only return choices that match a specific condition on the second list.

View solution in original post

Just to add to this. I used this and it worked for me.

* Filter - Choices where the Value is equal to a selected radio button value

pwa choices option.PNG

 

You can use IN if you have want a list of more than one option.

In my own case, I needed a default value in the DefaultSelectedItems field.

Hello @CarlosFigueira ,

I'd like to filter a combobox by [JobTitle] using the Choices/Filter pattern you laid out (for a SharePoint person column), but I keep getting stuck.  Perhaps you could provide the formula for such example?

Thank you much,

- Kurt

CarlosFigueira,

 

Come here so I can kiss you. This helped so much! Smiley Very Happy

Hello, 

 

I have the same question (i think) Did you find a solution? 

 

I have a list with a person column ( named "Naam_user")  and a choice column (named "functie_user")

 

I want to filter the functions in the dropdown based on the name. 

I tried this but it doesn't work. 

 

 

Filter(Choices(Userlijst.Functie_user);
Value in Filter(Userlijst; Naam_user.Email = User().Email).Naam_user)

 

@CarlosFigueira , @kbarber 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Top Kudoed Authors
Users online (985)