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)

20 REPLIES 20

sorry @Stijn007  - the project i was working on a year ago never went anywhere so I never did get this problem resolved.  good luck; if you do get an answer please post the solution because i'm still interested...

orkavic
Frequent Visitor

I'm working through a similar issue where I am accessing data from lookup columns in my sharepoint list with a series of cboxes. The first combobox accessess my list of customers, then the next combobox accesses the same list but a different column populated by all of the sites owned by each customer, then in the next column I have all the different components owned located on each site.  I would like to show distinct values in the customer combobox, but for some reason with my current formula, the customer names are showing up as blank.  I think there is definitely something there because I can click on one of the blank fields, and the customer sites field populates correctly.. Can someone help me understand what is wrong my formula?

 

 
Cosmic_Kitchen
Frequent Visitor

Just dropping this in here for added reference since this post was so helpful and had several replies over the years.

Was able to filter a list of Payment Methods with a Choice called 'Type' when all I wanted to show was "Credit Card" or "Debit Card".

 

Filter(Choices(Type), Value = Type.'Credit Card' || Value = Type.'Debit Card')

 

Hi @CarlosFigueira 

 

Can you explain why this is not working.

 

Filter(Choices(KUADictionary.Category_type), Value = Products.Value)

I have a gallery which I want to be filtered based on a collection. 

@JHS2020 what error do you get (or if you don't get errors, how is it not working)?

If 'Products' is a collection, and you want to filter the Category_type choices only for those whose values are present in the gallery, then you can use should use the in operator instead of the equality one:

Filter(Choices(KUADictionary.Category_type), Value in Products.Value)

Hope this helps! 

@CarlosFigueira I get this. Is it worth mention that the Category_type is a choice column 

 

JHS2020_0-1643178723281.png

 

Lakshayg
New Member

Thanks to all the life savers here. 😀

BarryDinh2019
New Member

@JHS2020 

Since the Filter is requiring to use value instead table. I’ve used ‘AddColumns’ function to convert it as a value. Then, I used Distinct to extract duplicate values.

 

Items:

Distinct(Filter(AddColumns(Test,"lookup1value",Lookup1.Value),Choice.Value <> "Choice 2"),lookup1value)

 

BarryDinh2019_0-1654660465287.png

 

I hope this will save someone from headache. 😄

Hey Carlos,

I am having a lookup field that referred to another list which has more than 5k rows. Threshold view error message is spamming at me. From what I understand, as long as I filter my lookup combobox to less than 5k rows, it will work right? 
Have you ever encounter such a scenario? Appreciate if you can feed some formula reference here. 🙂

 

bradt30h_0-1656040551876.png

 


@CarlosFigueira wrote:

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)

This thread still seems to be active, and I think this is relevant:

 

I think this is VERY close to the solution I need, but I can't seem to dial it in.  I'm building an app where users may register for a class by selecting a date.   The date selection is 'Attendee Registration'.DatesFirstChoice  .  The available class dates are in a lookup table 'Lookup Dates'.StartDate  .  The Lookup Table also has column .Status , which I'd like to be able to use as a choice filter. 

 

I've tried to apply this formula but it doesn't like the way I'm using "Value".  

 

 

Filter(
    Choices(
        'Attendee Registration'.DatesFirstChoice
     );
    Value in Filter(
        'Lookup Dates',
         Status="Registration Open"
    )
    .'Start Date'
)

 

 

I get "Name isn't valid. 'Value' isn't recognized"

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,774)