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.

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 (2,006)