cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
PowerAddict
Super User II
Super User II

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
Anonymous
Not applicable

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)

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
Anonymous
Not applicable

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

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

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

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")

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (51,274)