Delegation Warnings... Filtering SharePoint list by Combo Boxes, range of dates also blank date field

[Johany_Navarro]

Hope you all are ok.

I've been trying to avoid delegation warnings on my filter function for this app, I have this formula that works well thanks to @RezaDorrani 

Filter(
    'SharePointList',
	//FILTERING BY COMBO BOX
    IsBlank(cbCompanyName.Selected.Result) || IsEmpty(cbCompanyName.SelectedItems) || Title = cbCompanyName.Selected.Result,
	// FILTERING BY RANGE OF DATES
    IsBlank(DatePicker_From) || IsBlank(DatePicker_To) || SharePointListDateColumn >= DatePicker_From.SelectedDate && SharePointListDateColumn <= DatePicker_To.SelectedDate,
    // FILTERING BY RADIO BUTTONS
	IsBlank(rbYesOrNo.Selected.Value) || SharePointListTextColumn = rbYesOrNo.Selected.Value
)//Filter

besides, I also need to filter when a date field is empty, I'm doing this by using a boolean variable however as soon as I write the If() within the Filter() I get a delegation warning, I'm using checkboxes to make true or false the variables, see below code and screenshot.

Filter(
    'SharePointList',
	//FILTERING BY COMBO BOX
    IsBlank(cbCompanyName.Selected.Result) || IsEmpty(cbCompanyName.SelectedItems) || Title = cbCompanyName.Selected.Result,
	// FILTERING BY RANGE OF DATES
    IsBlank(DatePicker_From) || IsBlank(DatePicker_To) || SharePointListDateColumn >= DatePicker_From.SelectedDate && SharePointListDateColumn <= DatePicker_To.SelectedDate,
    // FILTERING BY RADIO BUTTONS
	IsBlank(rbYesOrNo.Selected.Value) || SharePointListTextColumn = rbYesOrNo.Selected.Value,
	//FILTERING BY BLANK DATES
	If(
        varSharePointDateColumn,
        SharePointListDateColumn = Blank(),
        true
    )
)//Filter

also, I need to add another logical test that depends on what's selected in this combo box.

This is my final Filter() formula.

Filter(
    'SharePointList',
	//FILTERING BY COMBO BOX
    IsBlank(cbCompanyName.Selected.Result) || IsEmpty(cbCompanyName.SelectedItems) || Title = cbCompanyName.Selected.Result,
	// FILTERING BY RANGE OF DATES
    IsBlank(DatePicker_From) || IsBlank(DatePicker_To) || SharePointListDateColumn >= DatePicker_From.SelectedDate && SharePointListDateColumn <= DatePicker_To.SelectedDate,
    // FILTERING BY RADIO BUTTONS
	IsBlank(rbYesOrNo.Selected.Value) || SharePointListTextColumn = rbYesOrNo.Selected.Value,
	//FILTERING BY BLANK DATES
	If(
        varSharePointDateColumn,
        SharePointListDateColumn = Blank(),
        true
    ),
    If(
        IsBlank(cbPresetReports.Selected.Value) || IsBlank(cbPresetReports.SelectedItems),
        true,
        cbPresetReports.Selected.Value = "Report1",
        Value(SharePointDateColumn) <> 0 && IsBlank(PersonInCharge),
        cbPresetReports.Selected.Value = "Report2",
        Value(SharePointDateColumn_1) <> 0 && IsBlank(SharePointDateColumn_2),
        cbPresetReports.Selected.Value = "Report3",
        Value(SharePointDateColumn_3) <> 0 && IsBlank(SharePointDateColumn_4),
        cbPresetReports.Selected.Value = "Report4",
        Value(SharePointDateColumn_5) <> 0 && IsBlank(SharePointDateColumn_6)
    )
)//Filter

I've been searching for the correct formula for a couple of months without any success.

Hope one of the Power Apps legends could help me.

@RezaDorrani  @aprildunnam  @RandyHayes  @WarrenBelz 

[Eelman]

@Johany_Navarro 

If I may add my experience here, the basic premise is that If()'s must not be inside Filter()'s, otherwise you'll get delegation issues. To fix this you reshape your code like so:

If(

   [condition 1],

   Filter( 1 ),

   [condition 2],

   Filter( 2 ),

etc ...

)

This makes your code way longer but does the job.

[Johany_Navarro]

Your experience is absolutely welcome @Eelman 

Could you give me a bit more help? Following your instruction, I wrote this. 

If(
    !IsBlank(cbCompanyName.Selected.Result) || !IsEmpty(cbCompanyName.SelectedItems),
    Filter('SharePointList', cbCompanyName.Selected.Result = Title)
)

however, the gallery doesn't show anything unless you have a selected item in the combo box, then I tried this

If(
    IsBlank(cbCompanyName.Selected.Result) || IsEmpty(cbCompanyName.SelectedItems),
    true,
    Filter('SharePointList', cbCompanyName.Selected.Result = Title)
)

an error comes out saying "Invalid argument type (table) expecting a boolean type instead."

Do you mind please give me an example of how to write the formula?

[Eelman]

@Johany_Navarro 

Try adding your 'true' statement to the ELSE part of your first formula. I typically write mine this way and it works.

I cannot check though until tomorrow as it's evening here and I don't have access to my apps. I can check tomorrow though if that doesn't work for you.

[Johany_Navarro]

😅sorry If you don't mind I rather waiting for your help, the app it's working but a bit slowly, I'm trying to improve its performance 

[Eelman]

@Johany_Navarro 

So, I managed to have a good look at your formula ... without sleepy eyes, haha ... and in your second formula the error is saying you are mixing return types ie a

- Boolean (true) and

- a Table Filter('SharePointList', cbCompanyName.Selected.Result = Title)

The fix is to replace the Boolean with a Table, ie

If(
    IsBlank(cbCompanyName.Selected.Result) || IsEmpty(cbCompanyName.SelectedItems),
    'SharePointList',
    Filter('SharePointList', cbCompanyName.Selected.Result = Title)
)

[Johany_Navarro]

Thank you for spending time on my question, I really appreciate it.

I think there was a misunderstanding, my issue is how to filter by blank dates using Sharepoint as the data source without getting into any delegation warning, I already have the formula to filter by combo boxes and range of dates.

In addition, I need to filter the SharePoint list depend on the selected value on one of the combo boxes, like below.

If(
        IsBlank(cbPresetReports.Selected.Value) || IsBlank(cbPresetReports.SelectedItems),
        true,
        cbPresetReports.Selected.Value = "Report1",
        Value(SharePointDateColumn) <> 0 && IsBlank(PersonInCharge),
        cbPresetReports.Selected.Value = "Report2",
        Value(SharePointDateColumn_1) <> 0 && IsBlank(SharePointDateColumn_2),
        cbPresetReports.Selected.Value = "Report3",
        Value(SharePointDateColumn_3) <> 0 && IsBlank(SharePointDateColumn_4),
        cbPresetReports.Selected.Value = "Report4",
        Value(SharePointDateColumn_5) <> 0 && IsBlank(SharePointDateColumn_6)
    )

Everything together in the same formula without a delegation warning, I'm afraid I started to think that this is not possible. 😮

[Eelman]

@Johany_Navarro 

I'm struggling a little with your full filter logic but you can check for Blank dates.

This is a Filter I use on an Employee List using a Toggle (showterminated) as well as StartsWith searching on Names and EmployeeID.

Note the way I've shaped the formula ie Filter inside the If() and using myDateColumn = Blank(). IsBlank() will cause a delegation warning, I believe:

If(
    showterminated.Value,
    
    // Show ALL Employees, including 'Terminated'
    Sort(
        Filter('Employee Details',
            (StartsWith('Last Name',search.Text) || 
            StartsWith('First Name',search.Text) || 
            StartsWith(EmployeeID,search.Text)),
            'Termination Date' = Blank()
        ),'Full Name', Ascending
    ),
    
    // Show current Employees
    Sort(
        Filter('Employee Details',
            (StartsWith('Last Name',search.Text) || 
            StartsWith('First Name',search.Text) || 
            StartsWith(EmployeeID,search.Text))
        ),'Full Name', Ascending
    )
)

Maybe you could try re-shaping your formula in a similar way?

[Johany_Navarro]

Many many thanks @Eelman understood, the problem if a check box is checked filter my data source by DateColumn = Blank() and obviously if the check box is unchecked Show all the SP items on the list.. I believe there is no way to do it without delegation warnings cause I have to use If()...

Again many many thanks

[Eelman]

@Johany_Navarro 

I wouldn't give up on getting this to work how you want. I guess the point I was trying to make ... and possibly made it badly ... is that by re-shaping the formula you may be able to solve this. For example, the above code I posted, I actually re-wrote this yesterday to that format.

I previously had it written like this:

Sort( 
    Filter('Employee Details',
        (StartsWith('Last Name',search.Text) ||
        StartsWith('First Name',search.Text) ||
        StartsWith(EmployeeID,search.Text)) &&
        If(showterminated.Value,IsBlank('Termination Date'),true)
    ),'Full Name',Ascending
)

This formula throws a delegation warning for the If() statement. I wasn't concerned because the dataset getting referenced would never go beyond 2000 records.

I re-wrote it to get rid of one of my little yellow triangles and also to demonstrate my point. By moving the If() statement outside the Filter() I was able to get rid of the delegation is. I even tested the Data Row Limit (I thought that by using Blank() I may get an issue) by reducing it to '2' and my formula still returned ALL records (around 140).

If your If() condition/s are too complex you could consider using a Switch statement instead. I've used this setup before where a user selects a button, the OnChange sets a Text variable and the Switch statement uses that variable to Filter the gallery.

I'd be fairly confident that you can solve this, it just may take some 'out-of-the-box' thinking 🙂