cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Filistyn
Helper V
Helper V

Calculating rows in sharepoint list (variable set)

Hey! I have a sharepoint list:

data.png

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?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Irfan
Resolver II
Resolver II

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

 

View solution in original post

Irfan
Resolver II
Resolver II

PS: 

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

 

-Irfan

View solution in original post

@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.

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

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
Resolver II
Resolver II

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

 

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

Irfan
Resolver II
Resolver II

PS: 

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

 

-Irfan

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

@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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,405)