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]

@ntt56k4 

Is there any particular reason you would have two collections of the same information?

If, for some reason, you are collecting all the rows of HelpDesk from your datasource, then the formula below should be providing you what you need, without a duplicate collection.

With({_userEmail: Lower(User().Email)},
    SortByColumns(
        Search(
            AddColumns(
                Filter(HelpDesk,
                    isAdmin || _userEmail in 'Created By'.Email
                ),
                "_assignedToEmail", AssignedTo.Email,
                "_ID", Text(ID)
            ),
            searchtextboxmyticketscreen_1.Text,
            "Title", "Description", "Created", "_AssignedToEmail", "_ID"
        ),
        "Created",
        If(ascendingOrder,Ascending,Descending)
    )
)

In this  formula we first filter the HelpDesk (unless there is a reason for it being in a collection, I'd consider just using the datasource instead) by the criteria isAdmin || _userEmail in 'Created By'.Email.   So what this does is, if the user is an admin, then the criteria for all records will be true and thus have all records.  If the user is not an admin, then it will return the records that the user owns.  Then all of that is passed to the AddColumns so that it can be searched.

Now, if your plans include relying on the Record of your Gallery (i.e.  yourGallery.Selected), then you should drop the added columns so that the record is consistent with the underlying datasource record schema.  You can use this formula:

With({_userEmail: Lower(User().Email)},
    DropColumns(
        SortByColumns(
            Search(
                AddColumns(
                    Filter(HelpDesk,
                        isAdmin || _userEmail in 'Created By'.Email
                    ),
                    "_assignedToEmail", AssignedTo.Email,
                    "_ID", Text(ID)
                ),
                searchtextboxmyticketscreen_1.Text,
                "Title", "Description", "Created", "_AssignedToEmail", "_ID"
            ),
            "Created",
            If(ascendingOrder,Ascending,Descending)
        ),
        "_AssignedToEmail", "_ID"
    )
)

[ntt56k4]

Thanks Randy. Both of them seem to be working with no error message, only delegation warning. However, my items in the gallery not showing up and the entire gallery is disappeared when I am in a preview mode. Not sure what why. 

[ntt56k4]

oh I forgot to answer your question related to collections. I only have one collection for the helpdesk sharepoint list. I don't intend in using mixed collection and helpdesk sharepoint list, but because I have tried so many formulas to find the solution so I just randomly tried different datasource to see if it works but it doesn't make any difference. 

[ntt56k4]

here are the screenshots. Both codes seem to work but the gallery itself is not showing up in preview mode for some reason. However, if I used my original code below, it will work but I can only search title and description. 

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

[ntt56k4]

sorry I guess either search or filter will work for me but I really need to be able to search taskstatus as it critical to see which ticket is open, in progress or completed. Either search or filter will work for me. Thanks again for your help. 

[RandyHayes]

@ntt56k4 

Sorry for the delay...

I picked this back up in this thread.