Filtering Ticket by status

(0) Share

Report

Posted on by CU14101426-0

Good day, Fam.
 
may you please assist with this code I want to display Number of tickets based on the status. I'm going to show this on User's screen, but it should only show by the logged in user. For instance if the user logged 5 tickets, 1 in New status, 2 in Progress and 2 in escalated it should show on the label I used this code but it complains about delegation issue and it always returns 0 even if i have records in my list. Thank you in advance. 
 
Coalesce(
    CountRows(
        Filter(
            HelpDesk,
            TicketStatus.Value = "Escalated" &&
            Lower(Requestor.Email) = Lower(User().Email)
        )
    ),
    0
)

Categories:

Using Apps Using Formulas

I have the same question (0)

All responses (17)

Answers (0)

Suggested answer

venturemavenwill 1,151 Super User 2025 Season 2 on at

Like (0)

Report
Filtering Ticket by status

Is Requestor a lookup column to the Users table? If so, then you don't need to use Lower on both sides as both attributes are assigned automatically using Entra.

This simplified version should theoretically work.

Coalesce( CountRows( Filter( HelpDesk, TicketStatus.Value = "Escalated" && Requestor.Email = User().Email ) ), 0 )

Let me know if this helps.

Was this reply helpful? Yes No
LowCodeJim 301 Moderator on at

Like (1)

Report

Filtering Ticket by status

Hi @CU14101426-0,

As previously mentioned the Lower function is not needed to compare the user email assuming the column being compared is a person column in SharePoint (which it looks like it is). The delegation warning is due to using the CountRows() function as this is not delegable for SharePoint in Power Apps.

If you are sure the filter will always return less than the delegation limit you have set (500 by default) then it shouldn't be an issue as the filter is delegable. You can also look at using flow to get this number if you are hitting this limit.

Was this reply helpful? Yes No
developerAJ 4,406 on at

Like (1)

Report

Filtering Ticket by status

I would suggest debugging the issue by hardcoding the email and try with multiple user emails

in this case filter happens first than count rows so you would get records no issue but you can only show 2000 max if it is more you need to show like 2000+

you can try using With

With(
{
withRecords: Filter(
HelpDesk,
TicketStatus.Value = "Escalated" ,
Requestor.Email = User().Email
)
},
Coalesce(CountRows(withRecords), 0)
)

Was this reply helpful? Yes No
CU14101426-0 16 on at

Like (0)

Report

Filtering Ticket by status

@LowCodeJim, @venturemavenwill . I have a TicketStatus( Choice) column in my list. I use this code when I submit ticket:

If(

Form1.Valid,

SubmitForm(Form1);

Refresh(HelpDesk);

)

This one is on my Onsuccess of the form that I use to submit Tickets (

Set(varLastRecord, Form1.LastSubmit);

If(

varRole = "Admin",

Navigate(AdminScreen, ScreenTransition.Fade),

Navigate(NormalUser, ScreenTransition.Fade)

);

Refresh(HelpDesk);

Patch(

HelpDesk,

LookUp(HelpDesk, ID = varLastRecord.ID),

{

TicketID: "TID" & Text(varLastRecord.ID, "0000")

}

);

Notify(

"Thank you " & User().FullName &

" for submitting your ticket. Your Ticket ID is " &

"TID" & Text(varLastRecord.ID, "0000"),

NotificationType.Success,

7000

);

ResetForm(Form1);

)

Was this reply helpful? Yes No
Suggested answer

venturemavenwill 1,151 Super User 2025 Season 2 on at

Like (0)

Report

Filtering Ticket by status

@CU14101426-0 I am a little confused. The code you just wrote has nothing to do with counting how many tickets are in the status.

What are you trying to achieve?

By the way, you can perhaps simplify your query also with the CountIf() function.

Was this reply helpful? Yes No
WarrenBelz 151,957 Most Valuable Professional on at

Like (0)

Report
Filtering Ticket by status

@CU14101426-0,

Firstly @developerAJ is on the correct track (so please mark accordingly) - your issue is that CountRows is not Delegable, so you need to "prefilter" a data set to then run CountRows locally. A small point - you do not need to use Coalesce if you are using CountRows - if there are no records, it will return zero. I will add a minor suggestion if you really need to use Lower (which is also not Delegable)
With( { _Data: Filter( HelpDesk, TicketStatus.Value = "Escalated" ) }, CountIf( _Data, Lower(Requestor.Email) = Lower(User().Email) ) )

Was this reply helpful? Yes No
CU14101426-0 16 on at

Like (0)

Report

Filtering Ticket by status

@developerAJ I used that one, no delegation issues, but it still displays 0.

Was this reply helpful? Yes No
LowCodeJim 301 Moderator on at

Like (0)

Report

Filtering Ticket by status

@CU14101426-0

How big is your list of data? As even using With you can still hit issues with delegation. Using With stops Power Apps telling you about delegation issues but as you can see below if the limit is reduced right down to 1, only 1 record is returned when using a count function due to it not being delegable. If you use a gallery for the delegable filter and then count the gallery items you can see it is correct and shows 2.

As long as your initial filter does not return more than the delegation limit (can be increased up to 2000) then the countrows/countif will be correct. But just be aware with can just hide the delegation issue sometimes.

Was this reply helpful? Yes No
WarrenBelz 151,957 Most Valuable Professional on at

Like (0)

Report

Filtering Ticket by status

@LowCodeJim,

I have a blog on the With function that may explain it a bit better. Assuming that "Escalated" tickets are less than 500 (can be increased to 2,000), then what I posted should work.

Was this reply helpful? Yes No
CU14101426-0 16 on at

Like (0)

Report

Filtering Ticket by status

Thank you so much for your answers guys really appreciate it, I'm still new to PowerApps. I even used the code from @WarrenBelz but still it still shows blank. on my other App I used this one:

"⤴️Escalated: " & CountRows(Filter(IT_Tickets, Status.Value = "Escalated")) is working but it has delegation issues , thats what I'm trying to avoid.

Was this reply helpful? Yes No