cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PedroFernandes
Helper II
Helper II

Dates and hours

Hi

I'm building an app in which the user can request vacation leaves: he must be able do request full days, half a day, or just some hours.
The user will also have a display that indicates how many days and hours of leave the user has left.

At the data table, I'm thinking about creating a "From" and a "To" column, for the start and end dates, respectively, with the number of hours.

I need help with two points:

1 - How do I limit the hour selection to a certain number of hours? For example, if the user works from 9 to 5 and wants to take half a day, he should only be able to take the morning or the afternoon off, and if he wants to take some hours off, I need to limit the selection to working hours.

2 - How do I make a countdown of the days and hours of leave the user has left? I'm thinking about creating a field with a starting value (let's say 25 days), that's being updated everytime a request is approved. The formula I have in mind for full days should be a count of the number of days between "From" and "To" columns. For the hours, I'm thinking on having an "hours" panel next to "days" (on the total leave time left), with an initial value of "23" (hours): when it reaches <"00" it starts on "23" again, and it's subtracted a day on the "days" counter.

Is there an easier way to do this? I feel like I'm complicating this exercise...

3 REPLIES 3
BCLS776
Community Champion
Community Champion

I'll offer a suggestion on data structure: don't cram all of this into one table. How about two tables with columns as shown below?:

 

Employees - contains details about the staff themselves

EmployeeName

ApproverName

EligibleLeave (total amount of leave this employee can have)

ShiftType (day, afternoon, evening)

 

LeaveRequests - contains info on requested leaves

EmployeeName

LeaveFromDate

LeaveToDate

LeaveAmount

LeaveStatus (requested, approved, denied, ineligible, etc.)

 

With those two tables you can do your queries:

  1. To limit an employee on the hours they book off, query their ShiftType and use that to modify your app's logic
  2. To give running totals of leave time available, use a filter to bring up any approved leaves the employee has already taken and total those LeaveAmounts up. Subtract that total from the employee's EligibleLeave.

Does that help?

Hi, I will follow your advice. It really seems more efficient to distribute all this info into two separate tables.
But for the employess I must use the data from a Users356 and the sole approver shall be the manager of that group, so how do I get around with the "Employees" table? Any way the "EmployeeName" field can be automatically updated if a member is inserted or taken out the group? (as opposed to updating that field manually?).

Also, I'm still confused on how can I store the hourly leaves values and subtract them from the "EligibleLeave" amount.

 

Thank you for your help.

Here is some more to consider as you build your data and app:


@PedroFernandes wrote:


But for the employess I must use the data from a Users356 and the sole approver shall be the manager of that group, so how do I get around with the "Employees" table? Any way the "EmployeeName" field can be automatically updated if a member is inserted or taken out the group? (as opposed to updating that field manually?).


There are a few different ways to handle this, but one I like to use is to leave all employees in the table and add a status column to indicate if an employee is current or not. The Office 365 connectors give the ability through a flow or an app to query which users are members of a group, so you can automatically update your list periodically or specifically when requested.

 


@PedroFernandes wrote:

Also, I'm still confused on how can I store the hourly leaves values and subtract them from the "EligibleLeave" amount.

Using the method I described earlier, each leave request submitted by an employee creates one record (row) in the LeaveRequests table. Using the Filter() function, we can query that list by employee name to generate a subset of records that contain only leaves submitted by that person. Then, we can use the Sum() function to total up the LeaveAmount column. That total can be subtracted from a LookUp() of the Employees table to find the EligibleLeave allotted to this employee. Once you get your tables set up and columns named, we can help you build the code to do this.

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,407)