Unanswered

Dates and hours

(0) Share

Report

Posted on by PedroFernandes

122

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

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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:
To limit an employee on the hours they book off, query their ShiftType and use that to modify your app's logic
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?

Was this reply helpful? Yes No
PedroFernandes 122 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No