cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

If and filter statement

Hi Guys,

 

Below filter is working perfectly, but I want to add something new. In my dropdown5 I have now value "All". So I want by default that this dropdown will be select as All. Then I want when All is selected that I can see all the status (dropdown5 is status field and this exisit All, New, Saved and Closed). 

 

I tried many ways to get this but unfortunately. It still doesn`t work. 

 

 

Spoiler
SortByColumns(Search(If(
_managerView = false,
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && Owner in Gallery3.AllItems.GroupName && State = Dropdown5.Selected.Result),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && State = Dropdown5.Selected.Result && Owner in Gallery3.AllItems.GroupName)),
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && State = Dropdown5.Selected.Result),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && State = Dropdown5.Selected.Result))),

SearchBox_1.Text,
"Owner",
"CommentType",
"State",
"UnitCode"),"Period",
Descending)

 

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: If and filter statement

Hi @PowerAddict ,

 

I think that I have the solution. I used If statement and now it seems likes that works.

 

Thank you!

 

Spoiler
SortByColumns(Search(If(
_managerView = false,
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && Owner in Gallery3.AllItems.GroupName && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status) && Owner in Gallery3.AllItems.GroupName)),
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)))),

SearchBox_1.Text,
"Owner",
"CommentType",
"State",
"UnitCode"),"Period",
Descending)

View solution in original post

8 REPLIES 8
Highlighted
Super User
Super User

Re: If and filter statement

Hi,

Wherever you have the following code:

&& State = Dropdown5.Selected.Result

Replace it with my suggestion:

&& (State="All" || State = Dropdown5.Selected.Result)

To default your dropdown to All, set its default property to All.

Let me know if this works.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com
Highlighted
Anonymous
Not applicable

Re: If and filter statement

Hi @PowerAddict ,

 

Thank you for your quick responce! Unfortunately I don`t see any data. But I think why is this the problem, becuase I don`t have the value All in my table. So I don`t know how to fix this.

 

Thank you!

 

 

Spoiler
SortByColumns(Search(If(
_managerView = false,
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && Owner in Gallery3.AllItems.GroupName && (State="All" || State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && (State="All" || State = Dropdown5.Selected.Status) && Owner in Gallery3.AllItems.GroupName)),
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && (State="All" || State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && (State="All" || State = Dropdown5.Selected.Status)))),

SearchBox_1.Text,
"Owner",
"CommentType",
"State",
"UnitCode"),"Period",
Descending)
Highlighted
Super User
Super User

Re: If and filter statement

Oh you need to add the value to your dropdown first.

As of now, how are you populating your dropdown? What is the Items property of your dropdown?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com
Highlighted
Anonymous
Not applicable

Re: If and filter statement

For my dropdownmeny I have a clearcollect table:

Spoiler
ClearCollect(
StatusTypes,
{Status: "All"},
{Status: "New"},
{Status: "Saved"},
{Status: "Reopen"},
{Status: "Submitted"},
{Status: "Close"}
);

The forumula in my dropdown is StatusType

 

So the dropdown works correctly.

 

 

Anonymous
Not applicable

Re: If and filter statement

Hi @PowerAddict ,

 

I think that I have the solution. I used If statement and now it seems likes that works.

 

Thank you!

 

Spoiler
SortByColumns(Search(If(
_managerView = false,
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && Owner in Gallery3.AllItems.GroupName && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status) && Owner in Gallery3.AllItems.GroupName)),
If(IsBlank(
'ComboBox Period_1'.Selected.Result),
Filter(
'[dbo].[test1]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()) && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)),
Filter ('[dbo].[test1]',
'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && If(Dropdown5.Selected.Status = "All",true, State = Dropdown5.Selected.Status)))),

SearchBox_1.Text,
"Owner",
"CommentType",
"State",
"UnitCode"),"Period",
Descending)

View solution in original post

Highlighted
Super User
Super User

Re: If and filter statement

Yea that will work but check if you are getting any delegation warning (blue line). If so, try my suggestion.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com
Highlighted
Power Apps
Power Apps

Re: If and filter statement

Formatted formula:

SortByColumns(
    Search(
        If(
            _managerView = false,
            If(
                IsBlank('ComboBox Period_1'.Selected.Result),
                Filter('[dbo].[test1]',
                    Year(Period) = Year(Now()) && 
                    Month(Period) = Month(Now()) && 
                    Owner in Gallery3.AllItems.GroupName && 
                    State = Dropdown5.Selected.Result
                ),
                Filter('[dbo].[test1]',
                    'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && 
                    State = Dropdown5.Selected.Result && 
                    Owner in Gallery3.AllItems.GroupName
                )
            ),
    
            If(
                IsBlank('ComboBox Period_1'.Selected.Result),
                Filter('[dbo].[test1]',
                    Year(Period) = Year(Now()) && 
                    Month(Period) = Month(Now()) && 
                    State = Dropdown5.Selected.Result
                ),
                    
                Filter ('[dbo].[test1]',
                    'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy") && 
                    State = Dropdown5.Selected.Result
                )
            )
        ),

        SearchBox_1.Text,
        "Owner",
        "CommentType",
        "State",
        "UnitCode"
    ),
    "Period",
    Descending
)

 

I do see some possible delegation issues. The 'in' operator can check if a text field in SQL is in a given text string anywhere in Power Apps, but you'll hit some limits if you check for membership instead.

 

In this case, "Owner in Gallery3.AllItems.GroupName" is using 'in' to check if the owner is a member in a set of strings and may not return what you are expecting if you have more data.

 

One other revision I'd suggest is in the conditions for your filter, I'd list the column on the left and the value to compare against on the right side for consistency:

Text(Period,"[$-en-US]mmmm-yyyy") = 'ComboBox Period_1'.Selected.Result

instead of:

'ComboBox Period_1'.Selected.Result = Text(Period,"[$-en-US]mmmm-yyyy")
Highlighted
Community Support
Community Support

Re: If and filter statement

Hi @Anonymous ,

 

Has your issue been solved?

If it is solved, please click "Accept as Solution" on the post that answers your problem.

Sik

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (11,119)