cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jliv
Advocate I
Advocate I

Filter textinputsearch and combox from gallery SharePoint list

I cant seem to filter (delegable) gallery items (from SharePoint list) from a textinputsearch and combobox(multiple select) . I am able to get the textinput code working but not together with the combobox. 

 

Code: 

Filter('Case Management Tracker', StartsWith(Title,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_First_x0020_Name,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_Last_x0020_Name,TextInputSearch.Text) Or

     Filter('Case Management Tracker', field_Current_x0020_Status = ComboBox1.SelectedItems.Value)

error - incompatible types for comparison record, table

18 REPLIES 18
poweractivate
Super User
Super User

@Jliv 

 

Filter('Case Management Tracker', StartsWith(Title,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_First_x0020_Name,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_Last_x0020_Name,TextInputSearch.Text) Or

    field_Current_x0020_Status = ComboBox1.SelectedItems.Value

)

 

Try something like above, because there is no need to start another Filter there, you are even still filtering on the same Data Source too so there is really no need there especially.

 

v-qiaqi-msft
Community Support
Community Support

Hi @Jliv,

Do you want to filter gallery based on search box and Combo Box selected items?

Could you please tell me that how you set the Items property of the Combo Box1?

What is your data source?

 

Not sure how you set the Items property of the Combo Box1, but just modify and take a try as below:

 

Filter('Case Management Tracker', StartsWith(Title,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_First_x0020_Name,TextInputSearch.Text) Or

    StartsWith(field_Customer_x0020_Last_x0020_Name,TextInputSearch.Text) Or

    CountRows(Filter(ForAll(field_Current_x0020_Status.Value,If(Value in ComboBox1.SelectedItems.Value,{t:1},{t:0})),t=1))>=CountRows(ComboBox1.SelectedItems.Value))

 

I just assume that field_Current_x0020_Status is a Choice column allowing multi select.

 

In addition, if you want to implement this with delegation, please check the following video from @Reza Dorran :

PowerApps Delegation & Gallery Filtering using multi select Choice and Lookup Columns (Part 4) - You...

Best Regards,
Qi
Jliv
Advocate I
Advocate I

Hi @v-qiaqi-msft ,

 

Do you want to filter gallery based on search box and Combo Box selected items? Yes

Could you please tell me that how you set the Items property of the Combo Box1?

Choices('Case Management Tracker'.field_Current_x0020_Status)

What is your data source? Sharepoint list-

'Case Management Tracker'

I tried the code below and it still gave me an error-

CountRows(Filter(ForAll(field_Current_x0020_Status.Value,If(Value in ComboBox1.SelectedItems.Value,{t:1},{t:0})),t=1))>=CountRows(ComboBox1.SelectedItems.Value)

 error-

Jliv_0-1634805699974.png

 

thank you for your help!

 

Thank you, @poweractivate,

 

I tried this one as well 

Filter('Case Management Tracker', StartsWith(Title,TextInputSearch.Text) Or

StartsWith(field_Customer_x0020_First_x0020_Name,TextInputSearch.Text) Or

StartsWith(field_Customer_x0020_Last_x0020_Name,TextInputSearch.Text) Or

field_Current_x0020_Status = ComboBox1.SelectedItems.Value

)

and it shows the error expected record value.

 

@Jliv 

 

It is not good practice because it probably will give delegation warning, but could you try simply:

 

 

field_Current_x0020_Status in ComboBox1.SelectedItems.Value

 

 

If this works at all, you can keep it if you want but if you got delegation warning yellow triangle, then the delegation limit (500, can be changed to max 2000 in Settings of the app) means that if the total number of records is greater than that number, the results will be inaccurate and only apply to that number. If that bothers you the above will need to be rewritten - but can you check if even just the above works at all perhaps in case? If the warning is not there in first place - maybe even better!

v-qiaqi-msft
Community Support
Community Support

Hi @Jliv,

Could you please show me a complete screenshot for that yours is to small to view its content.

vqiaqimsft_0-1634805943335.png

I have seen that you have something syntax wrong with your formula, you did not refer to your data source!

Have you tried my complete formula?

I have referred to the SP list as below:

Filter('Case Management Tracker',CountRows(Filter(ForAll(field_Current_x0020_Status.Value,If(Value in ComboBox1.SelectedItems.Value,{t:1},{t:0})),t=1))>=CountRows(ComboBox1.SelectedItems.Value))
Best Regards,
Qi

I tried this one, and it didnt give me an error! However, when I tested the choice combo it didnt filter any of the records neither. Maybe I can play around with this more but I also tried it with startswith / = instead of in but no luck. I wonder if I need to do something else with the .value field for it to filter that column in the list or recognize it as a text?

 

Filter('Case Management Tracker', StartsWith(Title,TextInputSearch.Text) Or

StartsWith(field_Customer_x0020_First_x0020_Name,TextInputSearch.Text) Or

StartsWith(field_Customer_x0020_Last_x0020_Name,TextInputSearch.Text) Or
field_Current_x0020_Status in ComboBox1.SelectedItems.Value
)

@Jliv 

 

field_Current_x0020_Status in ComboBox1.SelectedItems

 

This work better instead or not?

@v-qiaqi-msft ,

 

The code below makes the list disappear, but it doesn't give me an error either. I'm not too sure how to tweak this one since I'm unfamiliar with some of the syntax. I hope you can see the screenshot below-

 

Jliv_0-1634807581602.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

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 (4,186)