Calculating rows in sharepoint list (variable set)

[Filistyn]

Hey! I have a sharepoint list:

And I need a code, that calculates people in my department.

For example, I am a Manager from Finance / IT, I log in into the app and OnStart, there is a variable, and the value of that variable is how many people are in my department including me. So if there are 5 people in the SP list, which has department value "Finance / IT", the var value will be 5.

Could someone help?

[WarrenBelz]

Hi @Filistyn ,

If you have under 500 items in that list (and do not want a Delegation warning)

With(
   {wList:Employees},
   CountRows(
      Filter(
         wList,
         NameOfEmployee = User().FullName
      )
   )
)

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.

[Irfan · Accepted solution]

Hi @Filistyn 

To begin with, I would suggest to make an email address as a unique identifier amongst your records. You can have 2 people with same name but they will always have different email addresses. I have added an extra column to your list of columns which stores each users email address.

On your app OnStart
Set(LoggedinUserEmail, User().Email);
This will store the email address of the user who has logged in the variable LoggedinUserEmail.

Next, you need to lookup which department you belong to. Again, I am presuming you belong to only one department. Below code will store your department in the LoggedinUserDept variable
Set(LoggedinUserDept,LookUp(Employees,Email=LoggedinUserEmail));

Finally, you can create a collection which will filter the records from your datasource.

Set(FilteredData,Filter(Employees,Department=LoggedinUserDept.Department))

The above collection will give you list of the records which matches your department including yours. You can than use this data as required in your app.

Thanks.

-Irfan

[Filistyn]

Hello! That won't calculate number of employees in my department.

[Irfan · Accepted solution]

PS: 

CountRows(FilteredData) will give you the total no of rows in the filtered collection.

-Irfan

[Filistyn]

Thank you for your answer! I already had an variable, which values was logged in user's department, so it works like this: 

Set(
    FilteredData,
    Filter(
        Employees,
        Department = varMyDept
    )
);
Set(
    varPocet,
    CountRows(FilteredData)
)

Thanks for the solution

[WarrenBelz · Accepted solution]

@Filistyn ,

You are correct - must not have had my brain engaged properly when I activated the keyboard - I will have another go

With(
   {
      wDept:
      With(
         {wList:Employees},
         LookUp(
            wList,
            NameOfEmployee = User().FullName
         ).Department
      )
   },
   CountRows(
      Filter(
         Employees,
         Department = wDept
      )
   )
)

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.