Filter gallery by empty values in Choice column

(1) Share

Report

Posted on by craig_l

Hi all,

Example Sharepoint List name: SPList

Example Column name: Colour

I have a simple PowerApp which asks users to apply a 'Colour' to each item, using a gallery to choose the item, and a form to select the Colour. I've got that part of the app working using a simple patch back to the data source, which is a Sharepoint List.

Now, I want to add a Radio button where users have three options:

All, Colour Assigned, or No Colour Assigned.

That way, users don't have to sift through items that have already had a Colour assigned to them, but have the option to see them and make changes if necessary.

For the 'Items' property of my Gallery, this is what I have:

Sort(
If(
Radio1.Selected.Value = "All",
Filter('SPList', IsBlank(Colour) || Not(IsBlank(Colour))),

Radio1.Selected.Value = "Colour Assigned",
Filter('SPList', Not(IsBlank(Colour))),

Radio1.Selected.Value = "No Colour Assigned",
Filter('SPList', IsBlank(Colour))
),
ID,
SortOrder.Descending
)

Essentially how I would like it work is:

Items with a value in the Colour column should show up when the Radio button value is All or Colour Assigned

Items without a value in the Colour column should show up when the Radio button value is All or No Colour Assigned

So, what am I doing wrong? Any help would be appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

QuentinDURIEZ 547 Moderator on at

Like (1)

Report
Hi,

This solution can work :
Filter(SPList, Switch(Radio1.Selected.Value , "No Colour Assigned" , Colour = Blank() , "Colour Assigned" , Colour <> Blank() , true ) )

Use the formula Switch for testing Radio.Selected.Value, one case "No colour", another "Colour", and in default you ask for all items in SPList with the "true".

This will avoid having more than one filter formula.

But, you will get delegation warning : in general, comparing a value in SharePoint list with Blank will lead to a delegation warning, so maybe collect your gallery only this your list will not be +2000 long.

Regards,

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @craig_l,

We can avoid multiple Filters and conditional statements via the code below:

Filter( 'SPList', Radio1.Selected.Value = "All" || (Radio1.Selected.Value = "Colour Assigned" && Colour <> Blank) || (Radio1.Selected.Value = "No Colour Assigned" && Colour = Blank()) )

Due to the '<>' operator this will result in a non-delegable query. Should your list grow above 2000 records, you may want to pre-filter the dataset with a delegable query first or load all records within a >2000 record collection as stated by @QuentinDURIEZ.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
Verified answer

craig_l 27 on at

Like (0)

Report
Thank you all for your responses. As you all pointed out, I had a delegation issue with having my data source as my Sharepoint List, so I'm instead I'm now using a collection called ColAllData and patching that back to Sharepoint.

The message appears to have disappeared but the first response from @Ali_SZ_365 contained the code that I have used to filter my gallery successfully:

Sort( Filter( colAllData, If( Radio1.Selected.Value = "All", true, Radio1.Selected.Value = "Colour Assigned" && Not(IsBlank(Colour)), true, Radio1.Selected.Value = "No Colour Assigned" && IsBlank(Colour), true ) ), ID, SortOrder.Descending )

Thank you all again. Very much appreciated!

Was this reply helpful? Yes No