cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tonysommer Helper II
Helper II

Sort, search, filter a gallery and use if statements

I have a gallery with help desk tickets in it. The gallery will be viewed differently if the user is an admin or not an admin. If the user is logged in as an admin, they will see all tickets from everybody, be able to search through all tickets using a search box but also be able to filter the gallery based on All, Open, In Progress or Closed tickets.
 
If the user logs in as NOT an admin, they should only see their tickets, still be able to search for only their tickets using the same search box, and also be able to filter the gallery based on THEIR tickets with a status of All, Open, In Progress or Closed.


What I have currently is:

 

If(
    !IsBlank(IssueTrackerSearchBox.Text),
    Search(
        IssueTracker,
        IssueTrackerSearchBox.Text,
        "Title"
    ),
    If(
        !isAdmin,
        Sort(
            Filter(
                IssueTracker,
                CreatedBy.DisplayName = User().FullName
            ),
            ID,
            Descending
        ),
        Sort(
            If(
                TabMenu = "All",
                Filter(
                    IssueTracker,
                    !IsBlank(Status.Value)
                ),
                TabMenu = "Open",
                Filter(
                    IssueTracker,
                    Status.Value = "Open"
                ),
                TabMenu = "In Progress",
                Filter(
                    IssueTracker,
                    Status.Value = "In Progress"
                ),
                TabMenu = "Closed",
                Filter(
                    IssueTracker,
                    Status.Value = "Closed"
                ),
                TabMenu = "Open" & "In Progress",
                Filter(
                    IssueTracker,
                    Status.Value <> "Closed"
                )
            ),
            ID,
            Descending
        )
    )
)

 

 

 

That is however allowing a non-admin to be able to search all tickets instead of just their own. And they are not able to use the filter choices.
&nbsp;
Any suggestions?

2 ACCEPTED SOLUTIONS

Accepted Solutions
WarrenBelz Community Champion
Community Champion

Re: Sort, search, filter a gallery and use if statements

Hi @tonysommer ,

Firstly, it would assist to paste code of this size using "Format Text" first and then in a code box </> icon above.

Your problem lies in that if the first test is true (something in the search box), the filters further down will not happen (they are on the "else" of the "if")

If(
   !IsBlank(IssueTrackerSearchBox.Text),
   Search(
      IssueTracker,
      IssueTrackerSearchBox.Text,
      "Title"
   ),
   If(
      !isAdmin,
      Sort(
         Filter(
            IssueTracker,
            CreatedBy.DisplayName = User().FullName
         ),
         ID,
         Descending
      ), 
      Sort(
         If(
            TabMenu = "All",
            Filter(
               IssueTracker,
               !IsBlank(Status.Value)
            ),
            TabMenu = "Open",
            Filter(
               IssueTracker,
               Status.Value = "Open"
           ),
            TabMenu = "In Progress",
            Filter(
               IssueTracker,
               Status.Value = "In Progress"
            ),
            TabMenu = "Closed",
            Filter(
               IssueTracker,
               Status.Value = "Closed"
            ),
            TabMenu = "Open" &amp "In Progress",   //this bit did not paste properly
            Filter(
               IssueTracker,
               Status.Value &lt;&gt; "Closed"     //or this
            )
         ),
         ID,
         Descending
      )
   )
)

You will need to incorporate your Search in both - I cannot test this so please try something like this - I will leave you with the rest - they are the same idea.

If(
   !isAdmin,
   Sort(
      Filter(
         IssueTracker,
          If(
             !IsBlank(IssueTrackerSearchBox.Text),
             CreatedBy.DisplayName = User().FullName &&
             IssueTrackerSearchBox.Text in Title,
             CreatedBy.DisplayName = User().FullName
         )
      ),
      ID,
      Descending
   ), 
   Sort(
      If(
         TabMenu = "All",
         Filter(
            IssueTracker,
            If(
                !IsBlank(IssueTrackerSearchBox.Text),
                IssueTrackerSearchBox.Text in Title &&              
                !IsBlank(Status.Value),
                !IsBlank(Status.Value)
            )
         ),                      
         TabMenu = "Open",
         Filter(
            IssueTracker,
             If(
                !IsBlank(IssueTrackerSearchBox.Text),
                IssueTrackerSearchBox.Text in Title &&
                Status.Value = "Open",
                Status.Value = "Open"
             )
         ),                      //copy this syntax for the rest of them
         TabMenu = "In Progress",
         Filter(
            IssueTracker,
            Status.Value = "In Progress"
         ),
         TabMenu = "Closed",
         Filter(
            IssueTracker,
            Status.Value = "Closed"
         ),
         TabMenu = "Open" &amp "In Progress",   //this bit did not paste properly
         Filter(
            IssueTracker,
            Status.Value &lt;&gt; "Closed"     //or this
         )
      ),
      ID,
      Descending
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

tonysommer Helper II
Helper II

Re: Sort, search, filter a gallery and use if statements

I was able to figure out. The code is below, most importantly I kept trying to change the filtering below a condition that was already true while not an admin. In other words I was already not an admin and was trying to edit filters as if I wasn't. I mirrored the If/Then syntax for the admin side and brought the logic to the not admin side, and the code is below for anybody interested.

 

Going to accept @WarrenBelz original reply as the solution because that did point me in the right direction and made me realize things were already true while I was editing other parts of the code as if the conditions still applied. Also gave me some IF/Then logic to apply right after the Sort for the non admin.

 

Thank you everybody for the help!

 

 

 

If(
    !isAdmin,
    Sort(
        If(
            TabMenu = "Open",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Open"
                )
            ),
            TabMenu = "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "In Progress",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "In Progress"
                )
            ),
            TabMenu = "Open" & "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open" & "In Progress",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Open" & "In Progress"
                )
            ),
            TabMenu = "Closed",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Closed",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Closed"
                )
            ),
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    CreatedBy.DisplayName = User().FullName && TicketGallerySearchBox.Text in Title,
                    CreatedBy.DisplayName = User().FullName
                )
            )
        ),
        ID,
        Descending
    ),
    Sort(
        If(
            TabMenu = "All",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && TicketGallerySearchBox.Text in Title,
                    CreatedBy.DisplayName = User().FullName,
                    !IsBlank(Status.Value),
                    !IsBlank(Status.Value)
                )
            ),
            TabMenu = "Open",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open",
                    Status.Value = "Open"
                )
            ),
            TabMenu = "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "In Progress",
                    Status.Value = "In Progress"
                )
            ),
            TabMenu = "Closed",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Closed",
                    Status.Value = "Closed"
                )
            ),
            TabMenu = "Open" & "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value <> "Closed",
                    Status.Value <> "Closed"
                )
            )
        ),
        ID,
        Descending
    )
)

 

 

View solution in original post

8 REPLIES 8
WarrenBelz Community Champion
Community Champion

Re: Sort, search, filter a gallery and use if statements

Hi @tonysommer ,

Firstly, it would assist to paste code of this size using "Format Text" first and then in a code box </> icon above.

Your problem lies in that if the first test is true (something in the search box), the filters further down will not happen (they are on the "else" of the "if")

If(
   !IsBlank(IssueTrackerSearchBox.Text),
   Search(
      IssueTracker,
      IssueTrackerSearchBox.Text,
      "Title"
   ),
   If(
      !isAdmin,
      Sort(
         Filter(
            IssueTracker,
            CreatedBy.DisplayName = User().FullName
         ),
         ID,
         Descending
      ), 
      Sort(
         If(
            TabMenu = "All",
            Filter(
               IssueTracker,
               !IsBlank(Status.Value)
            ),
            TabMenu = "Open",
            Filter(
               IssueTracker,
               Status.Value = "Open"
           ),
            TabMenu = "In Progress",
            Filter(
               IssueTracker,
               Status.Value = "In Progress"
            ),
            TabMenu = "Closed",
            Filter(
               IssueTracker,
               Status.Value = "Closed"
            ),
            TabMenu = "Open" &amp "In Progress",   //this bit did not paste properly
            Filter(
               IssueTracker,
               Status.Value &lt;&gt; "Closed"     //or this
            )
         ),
         ID,
         Descending
      )
   )
)

You will need to incorporate your Search in both - I cannot test this so please try something like this - I will leave you with the rest - they are the same idea.

If(
   !isAdmin,
   Sort(
      Filter(
         IssueTracker,
          If(
             !IsBlank(IssueTrackerSearchBox.Text),
             CreatedBy.DisplayName = User().FullName &&
             IssueTrackerSearchBox.Text in Title,
             CreatedBy.DisplayName = User().FullName
         )
      ),
      ID,
      Descending
   ), 
   Sort(
      If(
         TabMenu = "All",
         Filter(
            IssueTracker,
            If(
                !IsBlank(IssueTrackerSearchBox.Text),
                IssueTrackerSearchBox.Text in Title &&              
                !IsBlank(Status.Value),
                !IsBlank(Status.Value)
            )
         ),                      
         TabMenu = "Open",
         Filter(
            IssueTracker,
             If(
                !IsBlank(IssueTrackerSearchBox.Text),
                IssueTrackerSearchBox.Text in Title &&
                Status.Value = "Open",
                Status.Value = "Open"
             )
         ),                      //copy this syntax for the rest of them
         TabMenu = "In Progress",
         Filter(
            IssueTracker,
            Status.Value = "In Progress"
         ),
         TabMenu = "Closed",
         Filter(
            IssueTracker,
            Status.Value = "Closed"
         ),
         TabMenu = "Open" &amp "In Progress",   //this bit did not paste properly
         Filter(
            IssueTracker,
            Status.Value &lt;&gt; "Closed"     //or this
         )
      ),
      ID,
      Descending
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

tonysommer Helper II
Helper II

Re: Sort, search, filter a gallery and use if statements

Thank you for the help so far. The search functionality I was looking for is working perfectly. However the non-admin user is still unable to filter the gallery of their tickets based on button selections of All, Open, In Progress or Closed.

 

What I have now is as follows:

 

If(
   !isAdmin,
   Sort(
      Filter(
         IssueTracker,
          If(
             !IsBlank(TicketGallerySearchBox.Text),
             CreatedBy.DisplayName = User().FullName &&
             TicketGallerySearchBox.Text in Title,
             CreatedBy.DisplayName = User().FullName
         )
      ),
      ID,
      Descending
   ), 
   Sort(
      If(
         TabMenu = "All",
         Filter(
            IssueTracker,
            If(
                !IsBlank(TicketGallerySearchBox.Text),
                TicketGallerySearchBox.Text in Title && TicketGallerySearchBox.Text in Title,
             CreatedBy.DisplayName = User().FullName,             
                !IsBlank(Status.Value),
                !IsBlank(Status.Value)
            )
         ),                      
         TabMenu = "Open",
         Filter(
            IssueTracker,
             If(
                !IsBlank(TicketGallerySearchBox.Text),
                TicketGallerySearchBox.Text in Title &&
                Status.Value = "Open",
                Status.Value = "Open"
             )
         ),                      //copy this syntax for the rest of them
         TabMenu = "In Progress",
         Filter(
            IssueTracker,
             If(
                !IsBlank(TicketGallerySearchBox.Text),
                TicketGallerySearchBox.Text in Title &&
                Status.Value = "In Progress",
                Status.Value = "In Progress"
             )
         ),  
         TabMenu = "Closed",
         Filter(
            IssueTracker,
             If(
                !IsBlank(TicketGallerySearchBox.Text),
                TicketGallerySearchBox.Text in Title &&
                Status.Value = "Closed",
                Status.Value = "Closed"
             )
         ),   
         TabMenu = "Open"&"In Progress",
         Filter(
            IssueTracker,
             If(
                !IsBlank(TicketGallerySearchBox.Text),
                TicketGallerySearchBox.Text in Title &&
                Status.Value <>"Closed",
                Status.Value <>"Closed"
             )
         )),  
      ID,
      Descending
   )
)

 

 

The admin is able to see all tickets, search by title and use the buttons to filter the gallery. However the non admin, while able to see and search just their tickets, is still unable to use the filter button to filter their tickets with a status of All, Open, In Progress, Closed. I am just not sure where to put the code to allow for a non admin to filter the gallery based on their selection of the filter buttons.

 

Any more help is really appreciated. Sorry about the formatting of the first post.

Super User
Super User

Re: Sort, search, filter a gallery and use if statements

Hi @tonysommer 

Just a thought but why not prefilter the list in the OnVisible property of the screen based on Admin status vs. individual users, say using User().FullName or User().Email and set up a global variable that is an alias of the datasource. Something like 

 

Set(ds, Filter(IssueTracker, CreatedBy.DisplayName = User().FullName)

 

Then you could use that variable as the items property of the gallery.  For the Admin, you would Set(ds, IssueTracker)

tonysommer Helper II
Helper II

Re: Sort, search, filter a gallery and use if statements

Sure I could do that, but I am still just not sure where to put the code to allow for both admin and non admin to be able to use the filter buttons. Right now per the code pasted above from me, only the admin is able to filter the gallery based on button click.

Super User
Super User

Re: Sort, search, filter a gallery and use if statements

@tonysommer 

Quick question, what connector are you using., Excel on line, Common Data Service, SharePoint? 

tonysommer Helper II
Helper II

Re: Sort, search, filter a gallery and use if statements

Using a SharePoint list called IssueTracker.

tonysommer Helper II
Helper II

Re: Sort, search, filter a gallery and use if statements

I was able to figure out. The code is below, most importantly I kept trying to change the filtering below a condition that was already true while not an admin. In other words I was already not an admin and was trying to edit filters as if I wasn't. I mirrored the If/Then syntax for the admin side and brought the logic to the not admin side, and the code is below for anybody interested.

 

Going to accept @WarrenBelz original reply as the solution because that did point me in the right direction and made me realize things were already true while I was editing other parts of the code as if the conditions still applied. Also gave me some IF/Then logic to apply right after the Sort for the non admin.

 

Thank you everybody for the help!

 

 

 

If(
    !isAdmin,
    Sort(
        If(
            TabMenu = "Open",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Open"
                )
            ),
            TabMenu = "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "In Progress",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "In Progress"
                )
            ),
            TabMenu = "Open" & "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open" & "In Progress",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Open" & "In Progress"
                )
            ),
            TabMenu = "Closed",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Closed",
                    CreatedBy.DisplayName = User().FullName && Status.Value = "Closed"
                )
            ),
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    CreatedBy.DisplayName = User().FullName && TicketGallerySearchBox.Text in Title,
                    CreatedBy.DisplayName = User().FullName
                )
            )
        ),
        ID,
        Descending
    ),
    Sort(
        If(
            TabMenu = "All",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && TicketGallerySearchBox.Text in Title,
                    CreatedBy.DisplayName = User().FullName,
                    !IsBlank(Status.Value),
                    !IsBlank(Status.Value)
                )
            ),
            TabMenu = "Open",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Open",
                    Status.Value = "Open"
                )
            ),
            TabMenu = "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "In Progress",
                    Status.Value = "In Progress"
                )
            ),
            TabMenu = "Closed",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value = "Closed",
                    Status.Value = "Closed"
                )
            ),
            TabMenu = "Open" & "In Progress",
            Filter(
                IssueTracker,
                If(
                    !IsBlank(TicketGallerySearchBox.Text),
                    TicketGallerySearchBox.Text in Title && Status.Value <> "Closed",
                    Status.Value <> "Closed"
                )
            )
        ),
        ID,
        Descending
    )
)

 

 

View solution in original post

WarrenBelz Community Champion
Community Champion

Re: Sort, search, filter a gallery and use if statements

Thanks @tonysommer ,

I suspect I am on the other side of the world to you (Australia), so I have not been able to respond over the last 10 hours or so.

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,480)