cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgokul95
Helper I
Helper I

Filtering on large data sets warning

Hi All,

 

I am getting this warning in the Or condition and "in" condition as you can see in the below image. Due to this warning, the filtering is not working properly.

IsEmpty(systemFilterComboBoxTSF.SelectedItems.Value) || System.Value in systemFilterComboBoxTSF.SelectedItems.Value

 

sgokul95_0-1641998438159.png

 

Can anyone please let me know how to overcome this warning?

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

 

@sgokul95 

You are understanding correctly.  But the challenge is trying to bring in 5000 records into your app.  PowerApps is not really designed to have so much data in it as it is an interface to the data (how is a user looking at 5000 records practical?)

 

However there is the balance of what you can delegate and what you cannot.  In your formula, almost all of your criteria is non-delegable.  So you really need to look at your app in a different way.

Choices are:

1) Endure the big performance hit and memory restraints of trying to pull all of your data into your app.

2) Review your filtering criteria to determine if there is some delegable criteria you can apply as a pre-filter.  Things like the "open" "closed" concept mentioned before, or only tests within a certain time period (i.e. last 30 days)

3) Augment your data list with ancillary lists that will hold information about your main list in a delegable way.  Ex. a list that contains the options in a text format and then record numbers for the items in the main list with those values.

 

#1 and #3 will have big performance hits to deal with.  #2 is best, but if you absolutely cannot find a pre-filter, then you are left with the other two to choose from.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes
Super User
Super User

@sgokul95 

So one thing to keep in mind...Lookup columns are Great in SharePoint...not in PowerApps!  Lookup columns are more for user interface in SharePoint than they are anything else.  They do contain some relationship logic (which is nice), but beyond that, they cause issues from the PowerApps side of things.

 

To start, your formula has the ForAll backward - you are trying to use it like a ForLoop in some development language that PowerApps is not.  It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

So, the formula should be:

Collect(
    colTSFeatures,
    ForAll(
        Filter(
            Test_Suite_Features,
            'TestSuiteID:TestNumber'.Id = varNewItemTestNumber
        ) As _item,
        Patch(_item, 
            {'FeatureName': ThisRecord.'FeatureID:Name'.Value}
        )
    )
);

 

Now, that said, if your list will only have 500 records, then you can ignore the delegation warning and make sure your Record limit in your app is set to accommodate the size of the list.

 

Otherwise, it is better in PowerApps to work from the Looked up list back.

In the sense of PowerApps, having a numeric column in your list that contains the record ID of the record from the looked up list is more efficient.

 

So if I am understanding your formula correct, then what you are really trying to do is lookup all the records in your looked-up list for the TestSuite list where the TestNumber is varNewItemTestNumber and THEN find all records in the Test_Suite_Features that are linked to that TestNumber, THEN look up the feature in the Feature List that is related to this record.

 

In the database world, this would be a junction table (your Test Suite Features list that is).

Also, this would be a many-to-many relationship.  As you can have one test with many features, and yet your features will have many tests.

 

Let me know if I am following along what you have so far.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
hemrhk
Super User
Super User

@sgokul95 

 

Try setting the control value to a variable and passing it to the filter action.

RandyHayes
Super User
Super User

@sgokul95 

The In operator is not delegable.  Can you provide your full formula and I can provide a replacement formula that would be delegable?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
sgokul95
Helper I
Helper I

Hi @RandyHayes ,

 

Below is the filter code that I use to filter the items based on the user selection. I have attached the screenshot of the code and also of the UI for better understanding. 

Filter(
            Test_Suites,
            IsBlank(testNumberFilterTextboxTSF.Text) || TestNumber = Value(testNumberFilterTextboxTSF.Text),
            IsBlank(ownerFilterTextboxTSF.Text) || ownerFilterTextboxTSF.Text in Owner,
            IsEmpty(systemFilterComboBoxTSF.SelectedItems.Value) || System.Value in systemFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(segmentFilterComboBoxTSF.SelectedItems.Value) || Segment.Value in segmentFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(platformFilterComboBoxTSF.SelectedItems.Value) || Platform.Value in platformFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(organizationFilterComboBoxTSF.SelectedItems.Value) || Organization.Value in organizationFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(testerModeFilterComboBoxTSF.SelectedItems.Value) || TesterMode.Value in testerModeFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(execCategoryFilterComboBoxTSF.SelectedItems.Value) || ExecutionCategory.Value in execCategoryFilterComboBoxTSF.SelectedItems.Value,
            IsBlank(nameFilterTextboxTSF.Text) ||  nameFilterTextboxTSF.Text in Title,
            IsEmpty(statusFilterComboBoxTSF.SelectedItems.Value) || Status.Value in statusFilterComboBoxTSF.SelectedItems.Value,
            IsEmpty(projectNameFilterComboBoxTSF.SelectedItems) || LookUp(Projects, ID = Test_Suites[@ProjectID].Id, 'Name (Title)') in projectNameFilterComboBoxTSF.SelectedItems.'Name (Title)',
            IsEmpty(subSystemFilterComboBoxTSF.SelectedItems) || LookUp(SubSystems, ID = Test_Suites[@SubSystemID].Id, SubSystem) in       subSystemFilterComboBoxTSF.SelectedItems.Result
        )

 

sgokul95_0-1642002055447.png

sgokul95_1-1642002146697.png

 

Regards,

Gokul S

RandyHayes
Super User
Super User

@sgokul95 

So, your problem here is that you have multiple In operators in your formula.  In is not delegable, so let's start with the obvious question...how big is your datasource?

 

The typical approach is to pre-filter your data using delegable operators and then filter the pre-filter results with non-delegable operators, but your formula doesn't have anything that can pre-filter.

 

So, the Test_Suites list...is there anything you can pre-filter on?  Like perhaps there are closed ones or old ones that can be excluded?  The object is to have a pre-filter that will be less than the record limit and then the rest of your formula can apply to that.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes ,

 

The list currently has around 5000 items. 

 

Like perhaps there are closed ones or old ones that can be excluded? -----> Do you mean any items that aren't needed for this filtering and can be filtered out before this main filter code?

 

If this is what you mean, then i think no. The items in the list are all needed for the user to filter and then work on it. Because of this, the items need to be filtered at this point only. 

 

Also as I am new to this, am i understanding it correctly?

 

Regards,

Gokul S

RandyHayes
Super User
Super User

 

@sgokul95 

You are understanding correctly.  But the challenge is trying to bring in 5000 records into your app.  PowerApps is not really designed to have so much data in it as it is an interface to the data (how is a user looking at 5000 records practical?)

 

However there is the balance of what you can delegate and what you cannot.  In your formula, almost all of your criteria is non-delegable.  So you really need to look at your app in a different way.

Choices are:

1) Endure the big performance hit and memory restraints of trying to pull all of your data into your app.

2) Review your filtering criteria to determine if there is some delegable criteria you can apply as a pre-filter.  Things like the "open" "closed" concept mentioned before, or only tests within a certain time period (i.e. last 30 days)

3) Augment your data list with ancillary lists that will hold information about your main list in a delegable way.  Ex. a list that contains the options in a text format and then record numbers for the items in the main list with those values.

 

#1 and #3 will have big performance hits to deal with.  #2 is best, but if you absolutely cannot find a pre-filter, then you are left with the other two to choose from.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes ,

 

Thanks. As you have said, I am working on applying some pre-filter before this filter gets applied. 

 

Also, there's a similar delegation warning in another case. The Test_Suite_Features contains only around 500 items. The column I am using in the Filter condition is a LookUp column and I guess the warning is because of that. Since it is a LookUp column in SharePoint, it's return type is "Record", hence I am using ".Id"  ('TestSuiteID:TestNumber'.Id) to get the number and check with a variable. 

 

That list contains upto 4 items for a particular TestSuiteID:TestNumber and I want to get those (upto 4) items alone. Hence I am comparing that in the Filter. Is there any way to overcome this warning and get those 4 items?

 

sgokul95_1-1642610032054.png

 

sgokul95_0-1642609092379.png

ForAll(
    Filter(
        Test_Suite_Features,
        'TestSuiteID:TestNumber'.Id = varNewItemTestNumber
    ),
    Collect(
        colTSFeatures,
        {
            ID: ThisRecord.ID,
            TestSuiteID: ThisRecord.TestSuiteID,
            FeatureID: ThisRecord.FeatureID,
            'FeatureName': ThisRecord.'FeatureID:Name'.Value,
            Notes: ThisRecord.Notes
        }
    )
);

 

Thanks and Regards,

Gokul S

 

RandyHayes
Super User
Super User

@sgokul95 

So one thing to keep in mind...Lookup columns are Great in SharePoint...not in PowerApps!  Lookup columns are more for user interface in SharePoint than they are anything else.  They do contain some relationship logic (which is nice), but beyond that, they cause issues from the PowerApps side of things.

 

To start, your formula has the ForAll backward - you are trying to use it like a ForLoop in some development language that PowerApps is not.  It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

So, the formula should be:

Collect(
    colTSFeatures,
    ForAll(
        Filter(
            Test_Suite_Features,
            'TestSuiteID:TestNumber'.Id = varNewItemTestNumber
        ) As _item,
        Patch(_item, 
            {'FeatureName': ThisRecord.'FeatureID:Name'.Value}
        )
    )
);

 

Now, that said, if your list will only have 500 records, then you can ignore the delegation warning and make sure your Record limit in your app is set to accommodate the size of the list.

 

Otherwise, it is better in PowerApps to work from the Looked up list back.

In the sense of PowerApps, having a numeric column in your list that contains the record ID of the record from the looked up list is more efficient.

 

So if I am understanding your formula correct, then what you are really trying to do is lookup all the records in your looked-up list for the TestSuite list where the TestNumber is varNewItemTestNumber and THEN find all records in the Test_Suite_Features that are linked to that TestNumber, THEN look up the feature in the Feature List that is related to this record.

 

In the database world, this would be a junction table (your Test Suite Features list that is).

Also, this would be a many-to-many relationship.  As you can have one test with many features, and yet your features will have many tests.

 

Let me know if I am following along what you have so far.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks @RandyHayes for your prompt response and help. I am following your suggestions in this case as well as working with large data sets and trying to change the filtering in my app. 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,559)