Hi Guys,
I am having difficulty calculating the employee leave balance based on leave type, I have created two tables one containing employee leave information: employee name lookup field, email lookup field start date, end date, and number of days applied, The Leave balance table contains annual leave,flexi leave, employee name and email. the employee will enter the information using the form
and I have already added the code for calculating the number of leaves applied
I have tried your code but this error popped up
Yes, I am trying to save the leave information submitted by the employee, should I use a patch instead? start date, end date number of applied leave
hey @MarwaAlhajri
thanks for trying. why are u using submitform at the end, your code includes patch, which would save to the datasource. or are you also saving other stuff?
Sir, I am receiving this error
also I am adding submit from at the end is that ok
hey @MarwaAlhajri
can u try this:
Set(varAnnualLeave, LookUp('Leave Balance', Email = User().Email).Annual);
Set(varFlexiLeave, LookUp('Leave Balance', Email = User().Email).Flexi);
Set(varSickLeave, LookUp('Leave Balance', Email = User().Email).Sick);
Set(
varDateRange,
ForAll(
Sequence(DataCardValue17.SelectedDate - DataCardValue16.SelectedDate + 1),
DataCardValue16.SelectedDate + Value - 1
)
);
Set(
varDaysApplied,
If(
And(
IsBlank(DataCardValue16.SelectedDate),
IsBlank(DataCardValue17.SelectedDate)
),
0,
CountIf(
varDateRange,
And(
Weekday(Value) in [1, 2, 3, 4, 5],
Not(Value in 'Public holidays'.Date)
)
)
)
);
If(
DataCardValue22.Selected.Value = "Annual Leave",
Set(varAnnualLeave, varAnnualLeave - varDaysApplied),
If(
DataCardValue22.Selected.Value = "Flexi Leave",
Set(varFlexiLeave, varFlexiLeave - varDaysApplied),
If(
DataCardValue22.Selected.Value = "Sick Leave",
Set(varSickLeave, varSickLeave - varDaysApplied)
)
)
);
Patch(
'Leave Balance',
LookUp('Leave Balance', Email = User().Email),
{
Annual: varAnnualLeave,
Flexi: varFlexiLeave,
Sick: varSickLeave
}
);
Let me know if my answer helped solving your issue.
If it did please accept as solution and give it a thumbs up so we can help others in the community.
Greetings
If( DataCardValue22.Selected.Value = "Annual Leave", Patch('Leave Balance', LookUp('Leave Balance', 'Email'.Value = User().Email), {Annual: varAnnualLeave - CountIf( varDateRange, And( Weekday(Value) in [1, 2, 3, 4, 5], Not(Value in 'Public holidays'.Date) ) )} ), If( DataCardValue22.Selected.Value = "Flexi Leave", Patch('Leave Balance', LookUp('Leave Balance', 'Email'.Value = User().Email), {Flexi: varFlexiLeave - CountIf( varDateRange, And( Weekday(Value) in [1, 2, 3, 4, 5], Not(Value in 'Public holidays'.Date) ) )} ), If( DataCardValue22.Selected.Value = "Sick Leave", Patch('Leave Balance', LookUp('Leave Balance', 'Email'.Value = User().Email), {Sick: varSickLeave - CountIf( varDateRange, And( Weekday(Value) in [1, 2, 3, 4, 5], Not(Value in 'Public holidays'.Date) ) )} ) ) ) );