Answered

Filters and nested if statements on SharePoint lists

(0) Share

Report

Posted on by zaxxon12345

So this probably simple but it's driving me crazy.

First, by default, when a checkbox called "CheckOldEvents" is not checked I want my gallery that is linked to an SP List called "CISCoverageCal" to filter only to list items that are >= to today based on a date field called StartTIme.

Next, I want use a series of IF Statements that will further filter the list based on 2 Drop Downs.

This seems like a nested IF Statement but how do I first reduce the list and then allow for the second set of IF Statements (created using the great Shane Young's tutorial video) to run against the previously filtered dataset?
Here's my code:

If(CheckOldEvents.Value = false, Filter(CISCoverageCal, StartTIme >=Today()),
CISCoverageCal,
If(
//This is for All and all
drpRegion.Selected.Value = "All" && drpPrograms.Selected.Value = "All", CISCoverageCal,
//All Regions and selected Programs
drpRegion.Selected.Value = "All" && drpPrograms.Selected.Value <> "All",
Filter(CISCoverageCal, Program.Value = drpPrograms.Selected.Value ),
//Selected Regions and All Programs
drpRegion.Selected.Value <> "All" && drpPrograms.Selected.Value = "All",
Filter(CISCoverageCal, Region.Value = drpRegion.Selected.Value ),
//This is for selected Program and Region
drpRegion.Selected.Value <> "All" && drpPrograms.Selected.Value <> "All",
Filter(CISCoverageCal, Region.Value = drpRegion.Selected.Value && Program.Value= drpPrograms.Selected.Value )
),)

Any help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@zaxxon12345 try this
Filter(CISCoverageCal,
If((CheckOldEvents.Value, true, = false, StartTIme >=Today()) & &
(drpRegion.Selected.Value = "All", true, Region.Value = drpRegion.Selected.Value) & &
(drpPrograms.Selected.Value = "All", true, Program.Value= drpPrograms.Selected.Value )
))

Was this reply helpful? Yes No
zaxxon12345 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the reply! Sorry, not sure I completely follow. But your idea did jog my brain a bit. Initially, I wanted to reduce the whole data set first to only show dates from Today forward (if the CheckOldEvents box is True) and then run my drop down filters off that data set, which I can't seem to do with 2 nested IF Statements. However, it does seem possible now to just add the 3rd permutation >= Today (CheckOldEvents box is True) to each individual IF test and resulting Filter function.
Let me know if I'm missing something here, which I probably am. 🙂
Thanks!

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@zaxxon12345 the formula I sent does what you wanted. Give it a try and let me know if it works.

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@zaxxon12345 Did you get a chance to try it out 🤔?

Was this reply helpful? Yes No
zaxxon12345 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for checking back. I can't get it to work. But it's probably just me and my weak formula skills. 😉

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@zaxxon12345 Could you please share a screen shot.

Was this reply helpful? Yes No
zaxxon12345 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@zaxxon12345 Please try this,

Filter(CISCoverageCal, If((CheckOldEvents.Value=true, true, StartTIme >=Today()) & & (drpRegion.Selected.Value = "All", true, Region.Value = drpRegion.Selected.Value) & & (drpPrograms.Selected.Value = "All", true, Program.Value= drpPrograms.Selected.Value ) ))

Was this reply helpful? Yes No
zaxxon12345 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks again for the help. I ended up reconfiguring the formula and controls a bit. I had the checkbox update a collection that fed the dropdown rather than using a boolean. In the end this is what I got to work:
Filter(
CISCoverageCal,
IsEmpty(drpRegion.Selected.Value) || IsBlank(drpRegion.Selected.Value) || Region.Value = drpRegion.Selected.Value,
IsEmpty(drpPrograms.Selected.Value) || IsBlank(drpPrograms.Selected.Value) || Program.Value = drpPrograms.Selected.Value,
IsEmpty(drpCoverFilter.Selected.Value) || IsBlank(drpCoverFilter.Selected.Value) || Status.Value = drpCoverFilter.Selected.Value
)

Thanks again!

Was this reply helpful? Yes No
Verified answer

CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@zaxxon12345 Glad it works!

Please remember to give a 👍 and accept the solution as it will help others in the future.

Was this reply helpful? Yes No