I'm fairly new to PowerApps, and I have been using AI to help coach me which has been really great. I'm probably in over my head and I need some help.
I created an app which allows field staff to "check in", and it updates to a Dashboard application for supervisor use. Right now our team has an excel spreadsheet which tracks days off/vacations/SDOs/etc.
I added a screen which is a Vacation/Sick Leave booking tool. Supervisors can select a date range, choose an employee name(from sharepoint list column), select an option (Vacation, Sick Leave), and submit the information. This is populated in a sharepoint list which is used in calendar view as an alternative to the excel sheet.
Everything is working well, except one feature that is crucial for our management processes. We need total hours of vacation time booked on that sharepoint list. I can easily do it manually with a text input field, but I am trying to do it automatically, which requires employee working hours be defined. The team has three "shifts". Monday to Friday, everyone other Friday off. Monday-Thursday are 9 hours a day, Friday on is 8 hours a day. Another worker has a weekend shift, but we can ignore that for now. Shift A and Shift B have alternate Fridays. Holidays/weekends also need to be accounted for.
Here is the code that AI and myself have built. This is a code snipped from the Submit button which sends the information to the sharepoint site. I still get an error "Expected number. We expect a number at this point in the formula".
I've tried multiple approaches but I always end up back here.
// Calculate total vacation hours according to the employee's shift and excluding specific days
Set(
totalVacationHours,
Sum(
ForAll(
Sequence(DateDiff(newStartDate, newEndDate) + 1, 1, 0),
With(
{
currentDate: DateAdd(newStartDate, ThisRecord.Value, Days),
dayOfWeek: Weekday(DateAdd(newStartDate, ThisRecord.Value, Days), StartOfWeek.Monday),
isAlternatingFriday: Mod(DateDiff(Date(2024, 1, 5), DateAdd(newStartDate, ThisRecord.Value, Days), Days) / 7, 2) = 0
},
If(
selectedShift = "A" && (dayOfWeek >= 1 && dayOfWeek <= 5) && !(dayOfWeek = 5 && isAlternatingFriday),
8, // Assume 8 hours for standard days, adjust if needed
0 // No hours for weekends and alternating Fridays
)
)
)
)
);
711
