count how many tickets assigned to

[ntt56k4]

Hello all, 

I am trying to figure out the correct formula to count how many tickets in a gallery are assigned to the person that is currently viewing. I have a Collection names AllTickets and a column in SharePoint names AssignedTo. The column in SharePoint is a value of person and not text. I tried many methods below and none of them working giving me a red error message. 

CountRows(Filter(AllTickets.AllItems)=AssignedTo.Email=User().Email))
CountRows(Filter(AllTickets, AssignedTo.Email=MyProfile.Email))
CountRows(Filter(AllTickets.AllItems, AssignedTo.Email=User().Email))
CountRows(Filter(AllTickets.AllItems, AssignedTo.Mail=User().Email))
CountRows(Filter(AllTickets, AssignedTo.Email=MyProfile.Email))
CountRows(Filter(AllTickets,AssignedTo = User().Email))

I was able to use the code below to generate the correct number of unassigned tickets and all tickets, but I can't seem to get the number of tickets assignedto

CountRows(Filter(AllTickets,IsBlank(AssignedTo)))

CountRows(AllTickets)

[RandyHayes · Accepted solution]

@ntt56k4 

Looks like the only one you didn't try was this one:

    CountRows(Filter(AllTickets, AssignedTo.Email=User().Email))

That should give you what you want.

If it doesn't, then there might be a disparity with the letter case of your email being returned from User().Email.  If so, then consider the following instead:

    CountRows(Filter(AllTickets, User().Email in AssignedTo.Email))

The in operator is not case sensitive, so it would filter properly.

I hope this is helpful for you.

[ntt56k4]

Thank you much. The first one returns a zero value but the second one works. Thanks again

[ntt56k4]

Another question for you. I can't seem to use the formula below to filter based on AssignedTo, Created or ID? I used the following and is not working? Do you know why? 

SortByColumns(Search(HelpDesk,searchtextboxmyticketscreen_1.Text,"Title","Description"),"Created",If(ascendingOrder=true,Ascending,Descending))

[ntt56k4]

ooops I meant to say I used the following code 

SortByColumns(Search(HelpDesk,searchtextboxmyticketscreen_1.Text,"Title","Description","Created","AssignedTo","ID"),"Created",If(ascendingOrder=true,Ascending,Descending))

[RandyHayes]

@ntt56k4 

Yeah, your email addresses are case sensitive and User().Email is probably returning an email cased differently than in the AssignedTo.Email.

For your Filtering, the below formula will work for you:

SortByColumns(
    Search(
        AddColumns(HelpDesk,
           "_assignedToEmail", AssignedTo.Email,
           "_ID", Text(ID)
        ),
        searchtextboxmyticketscreen_1.Text,
        "Title", "Description", "Created","_AssignedToEmail", "_ID"
    ),
    "Created",
    If(ascendingOrder,Ascending,Descending)
)

You cannot search on numeric columns...which ID is, so in addition to getting the AssignedTo email, the above also converts the ID to text so you can search it.

[ntt56k4]

Unfortunately, the code is not working. Here is my original code

If(isAdmin, SortByColumns( Search( AddColumns(HelpDesk, "_assignedToEmail", AssignedTo.Email, "_ID", Text(ID) ), searchtextboxmyticketscreen_1.Text, "Title", "Description", "Created","_AssignedToEmail", "_ID" ), "Created", If(ascendingOrder,Ascending,Descending) ),Sort(Filter(AllTickets,'Created By'.Email = MyProfile.Mail || 'Created By'.Claims = MyProfile.UserPrincipalName ),Created,Descending))

The other half of the code that I left is to filter out the display from user vs admin. I replaced your code as below but is not working 

SortByColumns(Search( AddColumns(HelpDesk, "_assignedToEmail", AssignedTo.Email, "_ID", Text(ID) ), searchtextboxmyticketscreen_1.Text, "Title", "Description", "Created","_AssignedToEmail", "_ID" ), "Created", If(ascendingOrder,Ascending,Descending) )

[ntt56k4]

I meant to say I replaced your code for the first half the code as below and is still not working 

If(isAdmin, SortByColumns(Search( AddColumns(HelpDesk, "_assignedToEmail", AssignedTo.Email, "_ID", Text(ID) ), searchtextboxmyticketscreen_1.Text, "Title", "Description", "Created","_AssignedToEmail", "_ID"), "Created", If(ascendingOrder,Ascending,Descending)),Sort(Filter(AllTickets,'Created By'.Email = MyProfile.Mail || 'Created By'.Claims = MyProfile.UserPrincipalName ),Created,Descending))

[RandyHayes]

@ntt56k4 

You are switching datasources in your Formula....you can't do that in PowerApps!  You have HelpDesk and AllTickets in your Formula.  If you need both, I would suggest separating the formula into two and putting one formula on one gallery and the other formula on another gallery.  Then set the Visible property of the Gallery for Admins to isAdmin and !isAdmin on the other gallery.

[ntt56k4]

I am sorry I forgot to tell you that the AllTickets is a collection for HelpDesk SharePoint List like below

Collect(AllTickets,HelpDesk)

I actually have two screens with the search bar so I tried switching datesource to see if it works but is not working so I just forgot to clear that out. I am using my collection for all my datasource.