Powerapp, Sharepoint Gallery, filter a filtered gallery

[RalfsZ]

Hi guys,

I'd like to ask for advice how to achieve a certain scenario.
I am 100% sure that the topic title sounds weird and there are other terms on how to call this.

We are building an "editor" app for our employees based on SP list. 
They have the ability to acccept/decline items in the list. That works absolutely fine with patch.

As the items generated per day increase, we would need an option to narrow down the items in the gallery. (Also, the 2k/5k tresshold is a concern)

What's there:

1. Gallery with items from SP list, which, currently, just shows all items from the list
2. Two filter dropdowns for the gallery

3. Icons to accept/decline the item (It uses patch function)

What we would like to achieve:

The items in the gallery should be "pre-filtered" to, say, items created this month, with the possibility to filter it further using the two dropdowns, hence the title.

Filtering the "all items" gallery using the dropdowns is easy, If and || IsBlank, but how do I achieve that only items of certain parameters show up in the gallery before the dropdowns are used? I assume delegations are to be used, but I am confused as to how I do this.

Any advice would be appreciated, thanks!

[BCBuizer]

Hi @RalfsZ ,

The filter for the Gallery should look something like the below. The first condition filters out any items that are older than 31 days. The second and third conditions check if anything has been selected in the respective DropDowns and apply a filter in case something has been selected:

Filter(
    DataSource,
    DateDiff(Now(),Created)<31,
    IsBlank(DropDown1.Selected.Value) || ColumnName1 = DropDown1.Selected.Value,
    IsBlank(DropDown2.Selected.Value) || ColumnName2 = DropDown1.Selected.Value
)

[RalfsZ]

Hi @BCBuizer 

The code you have provided would work just perfectly if not for the fact that the SP list we are using as data source contains more than 5K items in it already hence the filtering does not work and PowerApps gives delegation warning - indeed, the gallery does not filter by the date criteria.

Are there workarounds/functions/ways of doing to work with the 5k limitation? The list has quite a lot of columns.

One that comes to mind, which would be rather jank solution, use MS Flow to move "completed" items to separate SP list (we also use this SP list for BI reporting) so the "workable" SP list only contains "fresh" items and PowerApps would work just fine.


As a side question, is this a PowerApps limitation or would this work fine if we used, say, Azure SQL or Dataverse as our data source? Technically there is no difference as to where we store the data, we want to move away from working with "raw" data, hence the editor app.

[BCBuizer]

Hi @RalfsZ ,

Indeed the Date column is not delegable, so you'll have to find other (delegable) columns to (pre-)filter for. You mentioned that "completed" items may be moved to another list, so perhaps corresponding column can be used for pre-filtering:

Filter(
    Filter(
		DataSource,
		Status.Value = "In Progress" || Status.Value = "On Hold"
	),
    DateDiff(Now(),Created)<31,
    IsBlank(DropDown1.Selected.Value) || ColumnName1 = DropDown1.Selected.Value,
    IsBlank(DropDown2.Selected.Value) || ColumnName2 = DropDown1.Selected.Value
)

Status.Value != "Completed" is not delegable, hence the need to filter for other possible values such as "In Progress" and "On Hold". 

Assuming that you'll not have more than 2K items that have a status value other than "Completed", this will work.

[RalfsZ · Accepted solution]

To update this topic and, somewhat mark it as completed - 
We ended up going through the delegable functions documentation.
New Text columns have been added which by default have a value of AppNew so StartsWith can be used to filter out those entries.
In addition, Patch updates that column as well with a new Text, such as AppComplete, AppReview, AppRejected which further down gives options to filter out said records by using StartsWith.

This does not solve the issue with the rest of the entries, but as they are completed (reviewed) no further action is required for them and for the overall outlook a BI report is used, so I guess this is somewhat solved.

Thank you @BCBuizer for leading me in the correct direction 🙂