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

[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. 

[mmbr1606]

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

[Gwynn]

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. 

[Gwynn]

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. 

[Gwynn · Accepted solution]

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.