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.
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 @Anonymous
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?
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
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)
)
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. 😮
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?
Many many thanks @Anonymous 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
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 🙂