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

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

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

 

 

Johany_Navarro_2-1617634659003.png

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

 

Johany_Navarro_1-1617634264724.png

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 

10 REPLIES 10
Eelman
Super User
Super User

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

 

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?

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

😅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 

@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
Helper I
Helper I

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

@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
Helper I
Helper I

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

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,487)