Current user total hours

[Prutten]

Hi,

I am working on a TimeReport app and trying to filter and sum all the hours an employee have worked during current month. I have a sharepoint list as source with a person, date and number column(see attached picture) and I need help to figure out how to solve this. 

Best regards

Prutten

[mdevaney · Accepted solution]

@Prutten 
You are going to have issues with delegation because DATES cannot be delegated.  Instead you should convert your dates into integer numbers.  Then you can apply the formula below to SUM the WorkTime by month for an Employee.

Sum(Filter(your_sharepoint_list, Employee="test1 test2", Date<=20200401, Date>=20200430), WorkTime)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[eka24]

Try:

AddColumns(

GroupBy(YourList,"Employee","Date","SumTime"),

"Totals", Sum(SumTime,WorkTime))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[mdevaney]

@eka24 

FYI, GROUPBY is not delegation-friendly.  The app will have issues once the SP list exceed 2,000 records.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-yutliu-msft]

Hi @Prutten ,

Do you sum all the working time of one employee in current month?

Could you tell me the columns' data type in sharepoint list?

I assume that:

Date is datetime type, WorkTime is number type, Employee is multiple person type.

If so, I've made a similar test for your reference:

1)insert a combo box to choose employee

set the combo box's Items:

Choices(test.Employee)

2)insert a label to display the selected user's current month's sum of worktime

set the label's Text:

Sum(
     Filter(test,ComboBox1.Selected in Employee,
                 Month(Now())=Month(Date)
            ),
WorkTime)

Best regards,

[mdevaney]

@v-yutliu-msft 

The Month formula cannot be delegated either which will break the app in the long-run.  The OP needs to convert any dates to number value and Filter that way to avoid delegation as I have shown.

[Prutten]

How do I convert a date value to integer?

Thanks all for your help with my problem. I really appreciate it!

[v-yutliu-msft]

Hi @Prutten ,

Could you tell me how many records in your sharepoint list?

If the records are less than 2000, you do not need to worry about delegation limit.

The formula that I posted is ok:

1)insert a combo box to choose employee

set the combo box's Items:

Choices(test.Employee)

2)insert a label to display the selected user's current month's sum of worktime

set the label's Text:

Sum(
     Filter(test,ComboBox1.Selected in Employee,
                 Month(Now())=Month(Date)
            ),
WorkTime)

You just need to change the delegation limit to 2000.

If the record number is larger then 2000, since Sum function is not delegate function, just changing data type is not enough.

I suggest you use collection to avoid this problem.

In collection, there's no delegation limit.

You also do not need to change the data type.

You just need to firstly save data to collection then filter and calculate in this collection.

Just like this:

1)set the app's OnStart:

ClearCollect(collection1,test)

2)insert a combo box to choose employee

set the combo box's Items:

Choices(test.Employee)

3)insert a label to display the selected user's current month's sum of worktime

set the label's Text:

Sum(
     Filter(collection1,ComboBox1.Selected in Employee,
                 Month(Now())=Month(Date)
            ),
WorkTime)

Best regards,

[mdevaney]

@Prutten 
Suggest you watch this video by @RezaDorrani to understand why you should store the date as an integer and how to build it that way.  Its how I learned to avoid the delegation for dates.

The approach described by @v-yutliu-msft will only work up to 2,000 rows.  If you use that suggestion be prepared to re-write your app when you exceed the row limit. 

Link to Reza's Dates Delegation video:
https://www.youtube.com/watch?v=eCMuXPI1Qok&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD&index=2

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Prutten]

Hi, 

Thanks for the youtube tutorial. I understand now why I should use number instead of date field when I filtering. But I have one problem with the number field. I am trying to update the number field with yyyymmdd but the number appears like "20 205 25" instead of 20200525.  How to format the field without whitespace?

see attached pictures