Power Apps - SharePoint - do not display items that have Blank/Empty value in specific columns (larger dataset)

(0) Share

Report

Posted on by Gwynn

Hi all,

I am working on a small app that will track asset location.

There are 3000+ assets and not all of them have assigned City and/or Room.

I want to display in gallery only items that have at least 1 of those assigned. City and Room are choice columns in SharePoint with multiple values (single select).

Here is my Filter used in Gallery:

 Filter(
 'Asset management', 
 (City.Value = Dropdown_City.Selected.Value || IsBlank(Dropdown_City.Selected.Value)) 
 && (Room.Value = Dropdown_Room.Selected.Value || IsBlank(Dropdown_Room.Selected.Value))
 && ('Type of asset'.Value = Dropdown_Type_of_asset.Selected.Value || IsBlank(Dropdown_Type_of_asset.Selected.Value))
 && (!IsBlank(City.Value) || !IsBlank(Room.Value))
 )

Issue:

&& (!IsBlank(City.Value) || !IsBlank(Room.Value))
I get a delegation error.

Same goes for following options:

&& (Not(IsBlank(City.Value)) || Not(IsBlank(Room.Value)))

&& (City.Value <> Blank() || Room.Value <> Blank())

I am at a loss... Tried to create Collection OnStart with similar filter with delegation error on ">=" which does not make sense as it is supported.

Any Idea, suggestion, guidance is strongly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

mmbr1606 14,615 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
hey @Gwynn

can you try using a collection:

ClearCollect( AssetCollection, Filter( 'Asset management', 'Type of asset'.Value = Dropdown_Type_of_asset.Selected.Value || IsBlank(Dropdown_Type_of_asset.Selected.Value) ) ); Filter( AssetCollection, !IsBlank(City.Value) || !IsBlank(Room.Value), (City.Value = Dropdown_City.Selected.Value || IsBlank(Dropdown_City.Selected.Value)) && (Room.Value = Dropdown_Room.Selected.Value || IsBlank(Dropdown_Room.Selected.Value)) )

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
Gwynn 35 on at

Like (0)

Report
Copy link

Link copied!

Thank you @mmbr1606 for replay!

This is not exactly what I'm looking for. Unfortunately i believe my explanation was not precise and has mislead you.
Let me try again 🙂

1. Ignore dropdowns in my code. They do work and there are no issues there.
2. We have a share point list. Something like this:
| Asset | Serial Number | City | Room |
| Computer | 001 | London | Big |
| iPad | 002 | New York | |
| Mobile | 003 | | Small |
| iPad | 004 | | | |
3. Gallery needs to display only 001, 002, 003.
4. Additional dropdowns are used for furthere filtering of content where City and/or Room are available.
5. Size of SharePoint list 3500 items.

I hope this clarifies it.

Was this reply helpful? Yes No
Gwynn 35 on at

Like (0)

Report
Copy link

Link copied!

It appears i have missed major part of information while reading documentation.
Document

"=" is delegable but "<>" (not equals) is not. A bit strange if you ask me.

My current plan is to create a collection as suggested by @MM.
It will exceed 2000 items limit thus share point list will required additional Index_ID column that will be indexed to gather 2000+ items (ClearCollect(colList, Filter('source', Index_Id >= 1 && Index_ID <= 2000))...etc.)
This new collection will be filtered out which should avoid delegation bug.

For those who are in similar situation i will update this thread when I'm done testing.

Was this reply helpful? Yes No
Verified answer

Gwynn 35 on at

Like (0)

Report
Copy link

Link copied!

Found solution.
It appears my code was correct. Issue was with SharePoint ID.
SharePoint row ID is not delegable.

Workaround:
1. Create new Number column
2. Run power automate flow. Which should copy row ID into this new column.
3. Index new column (settings > List settings > Index Columns)

After that i have replaced all references of ID to Index_ID (my column name) and App started to run properly.

Please note that am using a collection OnStart that gattheres 5000 items. This is done with few collections that filter out Index_ ID range. For example: Filter('source', Index_ID >= 1 && Index_ID <=2000)
later on i combine multiple collections in to 1 which is used in app.

Was this reply helpful? Yes No