Filtering inside ClearCollect

[BramS]

I'm loading a table 'Types', which contains a boolean column 'IsActive' to my collection 'TypeCache'. I want to trim the data for the app as much as possible, filtering out unneeded rows and columns and adding a 'selected' column.

When I use the following order, everything works fine.

=ClearCollect(
    TypeCache;
    Sort(
        ShowColumns(
            Filter(
                AddColumns(Types; "Selected"; false);
                RoutingEnabled = true
            ); "ID"; "Selected"; "Title"
        ); ID
    )
)

However, when I first drop the inactive rows (IsActive = true), the resulting table only has the inactive rows. Oddly enough, when I drop de active rows (IsActive = false), I get the same result.

=ClearCollect(
    TypeCache;
    Sort(
        ShowColumns(
            AddColumns(
                Filter(Types; RoutingEnabled = true) ;
                "Selected"; false);
            ); "ID"; "Selected"; "Title"
        ); ID
    )
)

Why is this?

[tommyly]

With limited amounts of information, it's hard to say what the issue could be, but my first thought is delegation.

The default threshold for delegation is 500, but it can be changed to 2000. Consider your syntax (which has some syntax errors, by the way, but I think I understand your gist):

=ClearCollect(
    TypeCache;
    Sort(
        ShowColumns(
            Filter(
                AddColumns(Types; "Selected"; false);
                RoutingEnabled = true
            ); "ID"; "Selected"; "Title"
        ); ID
    )
)

1. The innermost function is a non-delegable function: AddColumns. What that means is that PowerApps will take the first threshold-batch (either 500 or 2000) of records. Delegation-restriction makes PowerApps forget about the records beyond its delegation threshold... so it in a sense ignores the remaining records. So in step-1, (assuming for example-sake that you have the default 500 setting and that you have 1000 total records), you collect the first 500 records and the 501st to 1000th records are ignored--not collected into collection TypeCache.
2. Your next function is Filter. It just happens that filter is delegable, which means it can send instruction for Azure SQL or SharePoint to filter for an item before giving you the threshold batch (e.g. 500). But this dynamic characteristic is in vain, because it's not filtering based off of Azure SQL or SharePoint any longer... it's filtering based off of an imperfect, undelegated batch that got passed on to it from the AddColumns function. You then execute ShowColumns and then Sort.

Column functions (e.g. AddColumns, ShowColumns) are not delegable functions, while Sort and SortByColumns are. What I'm guessing is happening is that by chance and coincidence, your datasource Types happens to have the top-most (or maybe the more recent records) with "IsActive" columns set to false. And Step-1 (as I described) is pulling these (e.g. the first 500) due to AddColumns. And then your attempt to filter (Step-2) is in vain because it no longer filters based off of ALL data like one would instinctually presume it would (due to delegation restrictions).

So it's not the order of formula that is important, but the order of functions that makes-or-breaks. I would consider something like this:

ClearCollect(TypeCache,
  ShowColumns(
    SortByColumns(
      Filter(Types, Selected=False, RoutingEnabled=true),
      "RoutingEnabled", Descending
    ),
    ID
  )
)

1. The inner-most function is now Filter, which allows Azure SQL or SharePoint to sort the master datasource list in a way where RoutingEnabled is descending (true=1; false=0)
2. The SortByColumns and ShowColumns functions are now dealing with the top 500 RoutingEnabled=true records as opposed to the top 500 default records.

Of course, the problem you will run into is if you have more than 500 RoutineEnabled=true records... then you will have to figure out an algorithm or process for collecting all the records.

Edit: shortened to answer question only.

[BramS]

Thanks for your response tommyly. I should have clarified that my list is only 7 rows long, so delegation limits should not be a problem. I did try your reworked code, but unfortunately to no avail.

The problem seems to only occur when I filter inside my ClearCollect. This code returns the RoutingEnabled = false rows when I filter on true or false.

ClearCollect(TypeCache;Filter(Types; RoutingEnabled = true))

If I step my approach, I get the results as expected:

ClearCollect(TypeCache;Types);;

ClearCollect(TypeCache;Filter(TypeCache; RoutingEnabled))

The documentation on these functions provide no indication that the combined approach shouldn't work. What am I missing?

(edit: changed topic title to better match the question)

[BramS · Accepted solution]

This has been discussed before

It seems to be a bug, with two known workarounds

• stepping your formula
• filtering the boolean value on 0/1 (this gives a non-terminating error)

[Anonymous]

Just for anyone looking for answers on this, here is an example where I want to create a collection but only get items with a status of Approved or Pre-Qualified:

(Status is a choice field)

ClearCollect(
colCollectionName,
(Filter(
'ListNameGoesHere',
Status.Value = "Approved" Or Status.Value = "Pre-Qualified"
))
);