Updating leave balance on sharepoint not being updated when request is approved

[Universal_Power]

I'm working on the leave balance template and I have changed the data source to sharepoint. Everything works as expected except when a leave request is approved, the balance does not update to reflect the change, for example I made a request for vacation and had it approved. It is supposed to update the "vacation used" portion when the request is approved.If i enter it manually from the list the change is reflected on the app.

This is the code used on the approver button 

Concurrent(
    Set(_submittingRequest, true),
    //when approving leave, add the respective days used to the respective used days field
    Patch(Balance, LookUp(Balance, BalanceID =  _requesterBalanceRecord.BalanceID),
        If(Upper(GalleryRequests.Selected.LeaveType) = Upper("Vacation"), {VacationUsed: _requesterBalanceRecord.VacationUsed + _requestedDays},
        Upper(GalleryRequests.Selected.LeaveType) = Upper("Sick leave"), {SickUsed: _requesterBalanceRecord.SickUsed + _requestedDays},
        Upper(GalleryRequests.Selected.LeaveType) = Upper("Floating holiday"), {PersonalUsed: _requesterBalanceRecord.PersonalUsed + _requestedDays},
        Upper(GalleryRequests.Selected.LeaveType) = Upper("Jury duty"), {JuryDutyUsed: _requesterBalanceRecord.JuryDutyUsed + _requestedDays},
        Upper(GalleryRequests.Selected.LeaveType) = Upper("Bereavement"), {BereavementUsed: _requesterBalanceRecord.BereavementUsed + _requestedDays}
        )
    ),
    Set(_managerApprove, true));
Patch(Leave, LookUp(Leave, LeaveID = GalleryRequests.Selected.LeaveID), {Status: "Approved"});
Set(_submittingRequest, false);
Navigate(ManagerChangeRequestScreen, BorderStyle.None);
      

I made a previous topic on this but I'm not sure I was very clear about the issue I'm having. Previous topic  
I'm new here to the forum so I'm not certain how I would go about deleting my old post or updating it, just wanted to clarify the issue.

I attempted adding an altered version of the code: But it did not change. All permissions on sharepoint are set so that a user can edit the form when created by another user so it should be updated. Is there something I'm missing ?

Concurrent(
    Set(_submittingRequest, true),
    // when approving leave, add the respective days used to the respective used days field
    Patch(
        Balance,
        LookUp(Balance, BalanceID = _requesterBalanceRecord.BalanceID),
        {
            VacationUsed: If(
                Upper(GalleryRequests.Selected.LeaveType) = Upper("Vacation"),
                _requesterBalanceRecord.VacationUsed + _requestedDays,
                _requesterBalanceRecord.VacationUsed
            ),
            SickUsed: If(
                Upper(GalleryRequests.Selected.LeaveType) = Upper("Sick leave"),
                _requesterBalanceRecord.SickUsed + _requestedDays,
                _requesterBalanceRecord.SickUsed
            ),
            PersonalUsed: If(
                Upper(GalleryRequests.Selected.LeaveType) = Upper("Floating holiday"),
                _requesterBalanceRecord.PersonalUsed + _requestedDays,
                _requesterBalanceRecord.PersonalUsed
            ),
            JuryDutyUsed: If(
                Upper(GalleryRequests.Selected.LeaveType) = Upper("Jury duty"),
                _requesterBalanceRecord.JuryDutyUsed + _requestedDays,
                _requesterBalanceRecord.JuryDutyUsed
            ),
            BereavementUsed: If(
                Upper(GalleryRequests.Selected.LeaveType) = Upper("Bereavement"),
                _requesterBalanceRecord.BereavementUsed + _requestedDays,
                _requesterBalanceRecord.BereavementUsed
            )
        }
    ),
    Set(_managerApprove, true)
);

Patch(Leave, LookUp(Leave, LeaveID = GalleryRequests.Selected.LeaveID), {Status: "Approved"})
Set(_submittingRequest, false);
Navigate(ManagerChangeRequestScreen, BorderStyle.None);

[WarrenBelz]

Hi @Universal_Power ,

Try this format

Set(_submittingRequest, true);
With(
   {
      _Data:
      LookUp(
         Balance, 
         BalanceID = _requesterBalanceRecord.BalanceID
      )
   },
   Patch(
      Balance,
      {ID: _Data.ID},
      Switch(
         Upper(GalleryRequests.Selected.LeaveType),
         "VACATION",
         {VacationUsed: _Data.VacationUsed + _requestedDays},
         "SICK LEAVE",
         {SickUsed: _Data.SickUsed + _requestedDays},
         "FLOATING HOLIDAY",
         {PersonalUsed: _Data.PersonalUsed + _requestedDays},
         "JURY DUTY",
         {JuryUsed: _Data.JuryUsed + _requestedDays},
         "BEREAVEMENT",
         {BereavementUsed: _Data.BereavementUsed + _requestedDays}
      )
   )
);
Set(_managerApprove, true)
Patch(
   Leave, 
   LookUp(
      Leave, 
      LeaveID = GalleryRequests.Selected.LeaveID
   ), 
   {Status: "Approved"}
)
Set(_submittingRequest, false);
Navigate(
   ManagerChangeRequestScreen, 
   BorderStyle.None
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Universal_Power]

@WarrenBelz Thanks, I tried it and it updated, but it didn't create a record specific to me (the user).The yellow is the record it created and the blue is where it was supposed to go, to a specific user (the one who sent the request)

[WarrenBelz · Accepted solution]

Hi @Universal_Power ,

It will update whatever record you used in your Lookup

LookUp(
   Balance, 
   BalanceID = _requesterBalanceRecord.BalanceID
)

so which field in Balance matches the field in the gallery to match ?

[Universal_Power]

@WarrenBelz  So I'm fairly new to power apps so bare with me a bit, The BalanceID field in my Balance sheet is automatically pulled from office365 users(which is matched in the gallery), so my BalanceID is my email and the date it was created which was auto generated upon logging into the leave app. What I'm seeing is that it's not pulling my specific balanceID but rather creating a blank balance ID and it's creating a "new" instance of it without the o365 data in the balance sheet.

[WarrenBelz]

@Universal_Power

OK - I will ask the question another way - which field here is a unique record identifier and is present in (I assume a Record Variable) RequestorBalanceRecord

[Universal_Power]

@WarrenBelz Okay I checked the global variables for RequesterBalanceRecord and In home screen on visible there's a lookup function similar to the one you wrote out where it pulls the record from the o365 profile, to me it looks like it's setting the Requesterbalancerecord variable using the _myprofile information ? Or could the unique identifier be employeeEmail?  Below is a snippet of it;

Concurrent(
    Set(_navMenuSelect, "My Leave Requests"),
    //if not manager view, refresh my balance collection when on this screen
    If(!_managerView, Set(_requesterBalanceRecord, LookUp(Balance, Year = Text(Year(Now())) && EmployeeEmail = _myProfile.UserPrincipalName));
        ClearCollect(RequesterBalanceCollection, 
        { type: "Vacation", balance: Value(_requesterBalanceRecord.Vacation) - Value(_requesterBalanceRecord.VacationUsed)},
        {type:"Sick Leave", balance:Value(_requesterBalanceRecord.Sick)-Value(_requesterBalanceRecord.SickUsed)},
        {type:"Personal", balance:Value(_requesterBalanceRecord.Personal)-Value(_requesterBalanceRecord.PersonalUsed)},
        {type:"Jury Duty", balance:Value(_requesterBalanceRecord.JuryDuty)-Value(_requesterBalanceRecord.JuryDutyUsed)},
        {type:"Bereavement", balance:Value(_requesterBalanceRecord.Bereavement)-Value(_requesterBalanceRecord.BereavementUsed)},
        {type:"Paternity", balance:Value(_requesterBalanceRecord.Paternity)-Value(_requesterBalanceRecord.PaternityUsed)},
        {type:"Bereavement", balance:Value(_requesterBalanceRecord.Study)-Value(_requesterBalanceRecord.StudyUsed)}
    ,
    Set(_showDetails, false)
    )));

To answer your question directly there is no field on the sharepoint list that directly correlates to requesterbalancerecord. Also all other uses of the global variable requesterbalancerecord only displays there is no "set" function.

[WarrenBelz]

@Universal_Power ,

The underlying issue is that you cannot update a record in a data source without referring to a field it it to identify which record to update. If it is not a unique identifier (there are other records with the same value in the field), you may update the wrong record, but you can update them all if you want to.

So in short, is the employee email this relevant field and if so, is this value contained in varRequestorRecord or alternatively is there a list somewhere that contains both the email address and also the corresponding BalanceID for that employee ?

If there is no field (as you noted) that correlates, then there is no way to identify which record to update and you need to revise your data structure so that this exists.

[Universal_Power]

@WarrenBelz  Sorry for the delayed response, I've been off for a little. I took the time today to try to understand what's going on. I know now that the patch function is updating the balance data source based on the identifier _Data which is the variable that was set using balanceID=_requesterBalanceRecord.BalanceID. 

With that being said, to answer your question, the employee email is not the relevant field, there is infact a unique BalanceID field within the data source. I believe that the requesterBalanceRecord is where the issue lies. Is it that this variable was not correctly defined ? 

For reference to all of this, I am using this from github where this person created the sharepoint connection to the leave app. https://github.com/powerappstim/leaverequest 

[WarrenBelz]

@Universal_Power ,

That is the first thing that was posted and you said it was not correct. I also cannot see BalanceID in the screenshot of your list below

One of these fields (I suspect EmployeeEmail or EmployeeName ) needs to be used to identify the correct record to Patch (unless there are other fields in the list you have not included above), so you simply need to retrieve that somewhere from the data you are using.