How to count number of overtime Hours each month

[mangjun]

Hello, I'm really quite new to PowerApps and wanted to ask some help from the community.  I have created a very simple overtime application that employee add date of when overtime is taken then calculates start time and end time to get overtime hours.  My challenge is that employee is only limited to take maximum 16hrs of overtime each month.  How can I accomplished this, that...

1. When an employee inputs the date of the overtime is taken, the apps should check the month and check employees all overtime records (on that month) in SP list if he has 6hrs or about to exceed...then a button to submit will be disabled and a label message should inform employee that overtime cannot be taken since he already reached or will exceed the 16 hrs.

Any help is very much appreciated.

[WarrenBelz · Accepted solution]

Hi @mangjun ,

You have not shared any code here, so I can only point you at the functions required. You need to Filter the records to those of the employee for the month and then do a Sum of the overtime field and add the current requested Overtime. If it is more than than 6, disable the button - roughly (on the button DisplayMode)

With(
   {
      wEmpMonth:
      Filter(
         YourSPList,
         YourEmployeeMail = User().Email &&
         Month(YourDateField) = Month(Today() &&
         Year(YourDateField) = Year(Today()
      )
   },
   If(
      Sum(
         wEmpMonth,
         YourOTField
      ) + 
      Value(YourOTBox.Text) > 6,
      Disabled,
      Edit
   )
)
       

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.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @mangjun ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Visit my blog Practical Power Apps

[mangjun]

Thank you @WarrenBelz  for the follow-up.  Actually I am still figuring it out on how to do this in a "Date of Overtime" (Date field) of which as soon as a date is selected, it checks for the accumulated overtime hours on the month selected.  I am using a form to add overtime record. 

[WarrenBelz]

@mangjun ,

You might share the code (in Text) that you are using to do this.

[mangjun]

Just a follow-up...from the image below, I wanted to accomplish to "SUM" the total of only "Approved" overtime... code is in the image caption.  Status field (Highlighted in red) is a single line of text and the OTHours (Highlighted in yellow) is a calculated field that returns a number.

If(Gallery2.AllItems,Status.Value = "Approved", Sum(Gallery2.AllItems, OTHours))

The Item property of the gallery is

Filter(
      'Overtime Sheets',
       'Created By'.DisplayName = Dropdown2.Selected.Result &&
       Month(OTDate) = Month(DatePicker1.SelectedDate) &&
       Year(OTDate) = Year(DatePicker1.SelectedDate))
 

 Appreciate if you can provide me a solution on this issue.  Thanks again.

[WarrenBelz]

Hi @mangjun ,

Try this

Sum(
   Filter(
      Gallery2.AllItems,
      Status = "Approved"
   ),
   OTHours
)

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.

Visit my blog Practical Power Apps

[mangjun]

Thank you @WarrenBelz ,  however if i use the "Status.Value" it gives me an error...but removing the ".Value" from "Status" does not throw an error...but it also does not SUM the OTHours...it just blank.  What can be wrong?

[WarrenBelz]

Hi @mangjun ,

I have fixed the status (just noticed it is a Single Line of Text), however I just did a test here

on an example list I have with a calculated label. The only question I have is whether your label is displaying the raw number or (as I suspect) you have formatted it as Text, in which case you cannot apply Sum() to it. If this is the case, make another label without the formatting, hide it and total that.

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.

Visit my blog Practical Power Apps

[mangjun]

Thank you @WarrenBelz , I'm a bit confused on what you mean by "...make another label without the formatting, hide it and total that".

As I mentioned the field OTHours in SP List is a calculated field having the code

=TEXT((TimeAM1-TimeAM)+(TimePM1-TimePM),"h:mm")

And its "data type returned from this formula"  is "Number".

So, in powerapps, i use "ThisItem.OTHours" in  the "Default" property of the label.

Is there a way to convert the OTHours (if it is text) to unable me to SUM it's value?