cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BramS
Level: Powered On

Filtering inside ClearCollect

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?

1 ACCEPTED SOLUTION

Accepted Solutions
BramS
Level: Powered On

Re: Is the order of formula's important?

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)
3 REPLIES 3
tommyly
Level 8

Re: Is the order of formula's important?

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
Level: Powered On

Re: Is the order of formula's important?

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
Level: Powered On

Re: Is the order of formula's important?

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)

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Top Community Contributors for September  2019

Top Community Contributors for September 2019 🎉🎉

Let's thank our top community contributors

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 527 members 6,066 guests
Please welcome our newest community members: