Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

leave request calculation

(0) ShareShare
ReportReport
Posted on by

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 

MarwaAlhajri_0-1716359087558.png

With(
    {
        // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(DataCardValue17.SelectedDate - DataCardValue16.SelectedDate + 1),
            DataCardValue16.SelectedDate + Value - 1
        )
    },
    If(
        And(
            IsBlank(DataCardValue16.SelectedDate),
            IsBlank(DataCardValue17.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        0,
        // show only dates Sunday to Thursday and exclude holidays
        CountIf(
            varDateRange,
            And(
                Weekday(Value) in [1, 2, 3, 4, 5],
                Not(Value in 'Public holidays'.Date)
            )
        )
    )
)
I want to calculate the leave type like this he will select the leave type for the drop-down box which is datacardvalue22 then it will be subtracted based on leaves applied by the user 
 
 
sample code below 
Set(varAnnualLeave,(LookUp('Leave Balance','Email'.Value=User().FullName).Annual));
Set(VarFlexiLeave,(LookUp('Leave Balance','Email'.Value=User().FullName).Flexi));
Set(VarsickLeave,(LookUp('Leave Balance','Email'.Value=User().FullName).sick));
 
used lookup to view each employee's leave balance
MarwaAlhajri_1-1716359581710.png

 


If(DataCardValue13.Selected.Value="Annual Leave")
 varAnnualLeave-datacard.text
else if
flexi
varFlexiLeave-Datacard22.text
  • MarwaAlhajri Profile Picture
    MarwaAlhajri on at
    Re: leave request calculation

    I have tried your code but this error popped up 

    MarwaAlhajri_0-1716372414491.png

     

  • MarwaAlhajri Profile Picture
    MarwaAlhajri on at
    Re: leave request calculation

    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 

  • mmbr1606 Profile Picture
    mmbr1606 10,429 on at
    Re: leave request calculation

    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?

  • MarwaAlhajri Profile Picture
    MarwaAlhajri on at
    Re: leave request calculation

    Sir, I am receiving this error 

    MarwaAlhajri_0-1716362852726.png

     

    MarwaAlhajri_1-1716362896794.png

     

    MarwaAlhajri_2-1716362928573.png

    also I am adding submit from at the end is that ok 

  • mmbr1606 Profile Picture
    mmbr1606 10,429 on at
    Re: leave request calculation

    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

  • zmansuri Profile Picture
    zmansuri 6,048 on at
    Re: leave request calculation
    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)
     )
     )}
     )
     )
     )
    );
    
    

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,434

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,722

Leaderboard