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

11 REPLIES 11
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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (62,280)