cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Display Active Projects Only by Default - Checkbox to show Closed items

@RandyHayes 

 

Hello,

 

I have a project list where the Status is either "Active" or "Closed". I would like only Active items to be shown by default, but allow the user to click a checkbox and display Closed items if needed.

 

Community-FilterClosed.png

 

I've inserted a checkbox control, but other than the Text, I haven't entered any formulas into it. Here is the Items property formula of my gallery. I borrowed it from the home screen of this app so there are some filter pieces that don't apply to this screen (I also don't need the display name trimming feature for this list). When I attempt to remove these pieces, the formula breaks so I could use some help customizing it to this particular scenario. To confirm, the Search box and column Sort functionality is desired and is working correctly. I keep notes on all of the gallery formula variations I come to the Community with so I really appreciate the help!

 

With({galProjects:
AddColumns(
Filter('Project List',
TxtPLSearchBox.Text in 'Project Number' ||
TxtPLSearchBox.Text in 'Project Name'
),
"projOwner", With({lName: Split('Project Owner'.DisplayName, " ")},
First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
"projType",'Project Type'.Value,
"projStatus",Status.Value
)
},
DropColumns(
SortByColumns(
galProjects,
Coalesce(locSortColumn, "PROJECT_NAME"),
If(locSortAscending, Descending, Ascending)
),
"projOwner","projType","projStatus"
)
)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@tagustin2020 

OR!!

See the modification below.

 

With({galProjects:
    AddColumns(
        Filter('Project List',
            TxtPLSearchBox.Text in 'Project Number' ||
            TxtPLSearchBox.Text in 'Project Name'
        ),
        "projOwner",     
            With({lName: Split('Project Owner'.DisplayName, " ")},
                First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "projType",'Project Type'.Value,
        "projStatus",Status.Value
    )
    },
    
    DropColumns(
        SortByColumns(
           Filter(galProjects,
               yourCheckBox.Value || (projStatus <> "Closed")
           ),
           Coalesce(locSortColumn, "PROJECT_NAME"),
           If(locSortAscending, Descending, Ascending)
        ),
        "projOwner",
        "projType",
        "projStatus"
    )
)

 

Let's see if that hits the target.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

5 REPLIES 5
Super User III
Super User III

@tagustin2020 

Please consider changing your Formula to the following:

With({galProjects:
    AddColumns(
        Filter('Project List',
            TxtPLSearchBox.Text in 'Project Number' ||
            TxtPLSearchBox.Text in 'Project Name'
        ),
        "projOwner",     
            With({lName: Split('Project Owner'.DisplayName, " ")},
                First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "projType",'Project Type'.Value,
        "projStatus",Status.Value
    )
    },
    
    DropColumns(
        SortByColumns(
           Filter(galProjects,
               !(yourCheckBox.Value && (projStatus = "Closed"))
           ),
           Coalesce(locSortColumn, "PROJECT_NAME"),
           If(locSortAscending, Descending, Ascending)
        ),
        "projOwner",
        "projType",
        "projStatus"
    )
)

Replace the yourCheckBox in the formula above to the name of your checkbox on the screen.

 

This should give you what you need.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

@RandyHayes 

 

Hi Randy,

 

We are close, but what I am trying to achieve is that only Active items are shown by default. The current behavior is that all items, Active and Closed, are displayed in the gallery and the Closed ones are hidden only when the box is checked. I would like the opposite where the closed items are initially filtered out of view and checking the box displays them. What adjustments should I make?

 

Thank you,

Teresa

Super User III
Super User III

@tagustin2020 

OR!!

See the modification below.

 

With({galProjects:
    AddColumns(
        Filter('Project List',
            TxtPLSearchBox.Text in 'Project Number' ||
            TxtPLSearchBox.Text in 'Project Name'
        ),
        "projOwner",     
            With({lName: Split('Project Owner'.DisplayName, " ")},
                First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "projType",'Project Type'.Value,
        "projStatus",Status.Value
    )
    },
    
    DropColumns(
        SortByColumns(
           Filter(galProjects,
               yourCheckBox.Value || (projStatus <> "Closed")
           ),
           Coalesce(locSortColumn, "PROJECT_NAME"),
           If(locSortAscending, Descending, Ascending)
        ),
        "projOwner",
        "projType",
        "projStatus"
    )
)

 

Let's see if that hits the target.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

Post Prodigy
Post Prodigy

@RandyHayes 

 

Thanks Randy. That worked perfectly! I'll keep the other version of the formula as well in case I need the inverse. Have a nice weekend!

 

Teresa

 

Super User III
Super User III

@tagustin2020 

Glad to help.

Have a nice weekend too.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (31,034)