Filtering on large data sets warning

[sgokul95]

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

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

[hemrhk]

@sgokul95 

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

[RandyHayes]

@sgokul95 

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

[sgokul95]

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
        )

Regards,

Gokul S

[RandyHayes]

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

[sgokul95]

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 · Accepted solution]

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

[sgokul95]

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?

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 · Accepted solution]

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

[sgokul95]

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.