FILTER ALL TASK STATUS WITHIN AN IF

[ntt56k4]

Hi Folks, 

I have the following code below to filter off from my gallery task status that is connected to a Task Status sharepoint list. Currently, it only display the selected item but not all tickets. I tried to add the ALL TICKETS option in the gallery and used the if within an if statement but is not working. Any suggestions?  

If(isAdmin,SortByColumns(Search(Filter(HelpDesk, GalleryTaskStatus.Selected.Value in TaskStatus.Value),searchtextboxmyticketscreen_2.Text,"Title","Description"),"Created",If(ascendingOrder=true,Ascending,Descending)),Sort(Filter(AllTickets,'Created By'.Email = MyProfile.Mail || 'Created By'.Claims = MyProfile.UserPrincipalName),Created,Descending))

[RandyHayes]

@ntt56k4 

The Formula I provided should give you what you want.  You should try to get If statements out of the Items properties when it means that you are creating two separate filters.  It is not an error, it is just that it means you have TWO (or more) places where you need to maintain criteria.

The only real difference between what I provided in my Formula any yours (beside combining into one) was the filter criteria on the Claims property of the user...I am not sure why you would have that!?

This line in my formula:

 (GalleryTaskStatus.Selected.Value = "ALL TICKETS" || TaskStatus.Value = GalleryTaskStatus.Selected.Value) &&

is what you need to implement the dropdown for the status.

What is not working with that?

[RandyHayes]

@ntt56k4 

LOL...always feel free to post back in the original thread with a "where the heck are you???"  I've been a little slow this week to keep on top of responses.

And... @WarrenBelz and @Drrickryp you all are welcome to join in on the party too.  I feel like I popped in and everyone left 😂  

[ntt56k4 · Accepted solution]

so I entered the following code as suggested 

If(isAdmin,SortByColumns(Search(Filter(HelpDesk, (GalleryTaskStatus.Selected.Value = "ALL TICKETS" || TaskStatus.Value = GalleryTaskStatus.Selected.Value) && GalleryTaskStatus.Selected.Value in TaskStatus.Value),searchtextboxmyticketscreen_2.Text,"Title","Description"),"Created",If(ascendingOrder=true,Ascending,Descending)),Sort(Filter(HelpDesk,'Created By'.Email = MyProfile.Mail || 'Created By'.Claims = MyProfile.UserPrincipalName),Created,Descending))

Unfortunately, is not working as the gallery is not showing up. It doesn't seem to like the equal sign. Not sure if it matters but I am not trying to filter off a drop down menu rather I am trying to filter off from a gallery that in Items ["ALL TICKETS", "OPEN", "PENDING", "CLOSED"]. 

This code line works for me 

GalleryTaskStatus.Selected.Value in TaskStatus.Value but is not liking the equal sign as suggest above. 

[ntt56k4]

also there is no value ALL TICKETS in my filter gallery that is from the TaskStatus Column in SharePoint so instead of using Choices in the Items for the Gallery, I just hard coded it ["All Tickets", "Open", "Pending", "Closed"]. So If i used the following code GalleryTaskStatus.Selected.Value in TaskStatus.Value, it will filter my based on the value choices but is not showing all the tickets when I selected All Tickets. Again, for some reasons it doesn't like the equal sign. 

[WarrenBelz]

Don't worry @RandyHayes ,

Might just be your aftershave . . 

[RandyHayes]

@ntt56k4 

The suggested Formula was:

With({_userEmail: MyProfile.Mail},
    SortByColumns(
        Search(
            Filter(
                HelpDesk, 
                (GalleryTaskStatus.Selected.Value = "All Tickets" || TaskStatus.Value = GalleryTaskStatus.Selected.Value) &&
                (isAdmin || 'Created By'.Email = _userEmail)
            ),
            searchtextboxmyticketscreen_2.Text, "Title","Description"
        ),
       "Created",
       If(ascendingOrder,Ascending,Descending)
    )
)

I think you are misunderstanding the purpose of the "All Tickets" in this formula.

How a filter statement works... Filter takes two arguments, the datasource and the criteria.  The criteria MUST evaluate to TRUE for the record to be included.

In the formula, the line 

GalleryTaskStatus.Selected.Value = "All Tickets" || TaskStatus.Value = GalleryTaskStatus.Selected.Value

Will evaluate to TRUE if the selection you have made is equal "All Tickets" False if not. BUT, that is OR'ed with the actual criteria against the column TaskStatus.Value.  So, with a logical OR, if either is True then the result will be true.  So, if the selection is All Tickets, then it doesn't make a difference what the taskstatus is and ALL will be returned.  If it is not All Tickets, then it is up to the comparison with the column to equate to true.

This is how you include "All" in your filters.  

You might also be interested in watching how to do this in a video I did specifically for adding "All" to your filters.  There I go over the logic of the criteria and the evaluation to true or false in more detail.

It really doesn't matter that you have a Gallery for these items over a dropdown.  It still equates to a record with a value column.  The only thing that is significant is the letter case.  In one example you used Proper case and in another you used Uppercase...please adjust the formula provided above accordingly to the exact case in your Gallery.

[RandyHayes]

@WarrenBelz 

Or maybe my lack of aftershave!! 😂

[Drrickryp]

Careful @WarrenBelz , I'm going to claim copyright infringement!

[WarrenBelz]

@Drrickryp ,

It was actually dedicated to you as you were on the thread . . .