Need assistance with formula to display result from calculated SharePoint column.

[TJCappy]

Hello All, 

I have created a home page or dashboard for employees to see where they stand with their tickets.

The current dashboard information is visible only to the staff who created them (we have another dashboard for admin that shows info for all staff.

The lists we are dealing with are well over the 2000 delegation limit.

Currently I am able to provide to staff the number of tickets for each status using the following formula

Set(varStatus,LookUp(Choices([@TransferRequests].Status),Value="In Progress")); 

The other two statuses are New Ticket & Completed and the sharepoint column is a choice column

I have now been asked to also show the number of tickets that are flagged for follow-up by the staff that created the ticket.

The follow-up flags work in a way that;

when the admin staff indicate with a Yes/No toggle that something is for follow-up a second yes/no toggle becomes visible on the input screen with a default value of NO.

When the staff that input the ticket changes the toggle on their screen to a yes (done) then the original admin flag changes colour but still stays active for the admin, to allow the admin to now follow-up on the original task.

To determine the number of flags that have been acted upon and the number that is still pending, a calculated column was created  in SharePoint that will add "yes" if it is still waiting for follow-up and no otherwise.

When trying to establish the number of items in the calculated column, in order to overcome the delegation limits, I have tried the following formulae and none have given me a result

Set(varStatus,LookUp(TransferRequests,Flagged,Value="yes"))       this formula says it can't find the Flagged column

Set(varStatus,LookUp(TransferRequests,Flagged="yes",Total))        this one says "the function LookUp has some invalid arguments. AND Name isn't Valid. "Total' isn't recognized.

Set(varStatus,LookUp(TransferRequests,Flagged="yes")) this one gives me a delegation warning and no solution

Can anyone help me find a way of determining the number of flagged tickets. for each staff.

Thank you for all your assistance

TJ Cappy.

[WarrenBelz · Accepted solution]

Hi @TJCappy ,

SharePoint calculated columns simply do not "play well" with Power Apps and should not be used - you need to do the same calculation in Power Apps. You may also not solve your Delegation issue without possibly populating a coumn wih the result of the calculation.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps