How to filter choices() on Combobox

[JanLektonen]

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)

[CarlosFigueira]

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")

[JanLektonen]

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

[CarlosFigueira]

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]

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

[CarlosFigueira · Accepted solution]

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.

[eopara]

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

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.

[kbarber]

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

[Brian-Hamilton]

CarlosFigueira,

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

[Stijn007]

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