Patch a list's column based on Lookup Column's Value

[syhrh]

Hi, I have this Leave Request list. The LeaveType column is a Lookup column.

this is a Sharepoint integrated form in Power Apps.

When the approver clicks an item in the list, it will display 2 buttons, Approve and Reject. If Approve is clicked, then it will patch/deduct the leave balance of the employee's based on the Leave Type and how many days they apply for leave in the Leave Balance list. The issue is in the Leave Balance list, all of the Leave types are columns.

   the leave type consists of 15 types.

I tried to patch the Leave Balance but it needs a column name, whereas I need to match the chosen Leave Type with the existing column in the Leave Balance list.

How to do that?

Thank you.

[Gochix]

Hi @syhrh ,

Can't you modify your App so you have 1 column where "Leave Type" title will be held?

[syhrh]

@Gochix Modify my App or my List? If it's List, then I have a Leave Type list that contains LeaveType lookup column. 

[BCBuizer · Accepted solution]

Hi @syhrh ,

There is no way to reference the column to be patched, so you have to map it manually. My first try would look like the below:

With({_UpdateItem: LookUp('Leave Balance', 'Employee Name' = EmployeeNameDCV && Year = YearDCV)},
	Switch(LeaveTypeDCV.Selected.Value,
		"Leavetype1", UpdateContext({_NewBalance: _UpdateItem.Leavetype1 - TotalDaysAppliedDCV}; Patch('Leave Balance', _UpdateItem, {Leavetype1: _NewBalance}),
		"Leavetype2", UpdateContext({_NewBalance: _UpdateItem.Leavetype2 - TotalDaysAppliedDCV}; Patch('Leave Balance', _UpdateItem, {Leavetype2: _NewBalance}),
		"Leavetype3", UpdateContext({_NewBalance: _UpdateItem.Leavetype3 - TotalDaysAppliedDCV}; Patch('Leave Balance', _UpdateItem, {Leavetype3: _NewBalance}),
		etc
	)
)

EmployeeDCV is the DataCardValue that has the employee name, but may be replaced with a reference anything else that has the same information.

YearDCVis the DataCardValue that has the year value, but may be replaced with a reference anything else that has the same information.

LeaveTypeDCV is the DataCardValue that has the leavetype value, but may be replaced with a reference anything else that has the same information.

TotalDayAppliedDCV is the DataCardValue that has the leave length value, but may be replaced with a reference anything else that has the same information.

Unfortunately I don't have the time to test this. If it works at all, it has the downside that whenever changes are made to the types of leave, you'll have to revise the code. 

I highly recommend to 'unpivot' your Leave Balance data source to only have 4 columns: 'Employee Name', Year, 'Leave Type', and 'Leave Amount'. Like that it will be much easier to filter for the right cell to patch and it is more adaptable to future changes.

[syhrh]

Hi @BCBuizer, this list is connected to many other elements that I've done so if I change it to your recommendation, it will take me a lot of time to redo those elements. 

I've done the formula but it doesn't deduct the leave balance. How to deduct the leave request based on the clicked item on the Leave Request list? I created the _UpdateItems2 but don't know where to put it.

Patch('Leave Request',LookUp('Leave Request',ID=SharePointIntegration.SelectedListItemID),{Status:{Value:"Approved"}});
With({_UpdateItem2: LookUp('Leave Request',ID=SharePointIntegration.SelectedListItemID),_UpdateItem: LookUp('Leave Balance', 'Employee Name'.DisplayName = DataCardValue19.Selected.DisplayName And Year = DataCardValue20.Text)},
	
Switch(DataCardValue18.Selected.Value,
		"Advanced Leave", UpdateContext({_NewBalance: _UpdateItem.'Advanced Leave' - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {'Advanced Leave': _NewBalance}),
		"Annual Leave", UpdateContext({_NewBalance: _UpdateItem.AnnualLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {AnnualLeave: _NewBalance}),
		"Calamity Leave", UpdateContext({_NewBalance: _UpdateItem.CalamityLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {CalamityLeave: _NewBalance}),
        "Carry Forward Leave", UpdateContext({_NewBalance: _UpdateItem.CarryForwardLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {CarryForwardLeave: _NewBalance}),
        "Compassionate Leave", UpdateContext({_NewBalance: _UpdateItem.'Compassionate Leave' - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {'Compassionate Leave': _NewBalance}),
        "Emergency Leave", UpdateContext({_NewBalance: _UpdateItem.EmergencyLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {EmergencyLeave: _NewBalance}),
        "Hospitalization Leave", UpdateContext({_NewBalance: _UpdateItem.HospitalizationLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {HospitalizationLeave: _NewBalance}),
        "Marriage Leave", UpdateContext({_NewBalance: _UpdateItem.'Marriage Leave' - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {'Marriage Leave': _NewBalance}),
        "Maternity Leave", UpdateContext({_NewBalance: _UpdateItem.MaternityLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {MaternityLeave: _NewBalance}),
        "Paternity Leave", UpdateContext({_NewBalance: _UpdateItem.PaternityLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {PaternityLeave: _NewBalance}),
        "Quarantine Leave", UpdateContext({_NewBalance: _UpdateItem.QuarantineLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {QuarantineLeave: _NewBalance}),
        "Replacement Leave", UpdateContext({_NewBalance: _UpdateItem.'Replacement Leave' - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {'Replacement Leave': _NewBalance}),
        "Sick Leave", UpdateContext({_NewBalance: _UpdateItem.SickLeave - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {SickLeave: _NewBalance}),
        "Study/Examination Leave", UpdateContext({_NewBalance: _UpdateItem.'Study/ExaminationLeave' - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {'Study/ExaminationLeave': _NewBalance}),
        "WFH", UpdateContext({_NewBalance: _UpdateItem.WFH - DataCardValue15.Text}); 
        Patch('Leave Balance', _UpdateItem, {WFH: _NewBalance})
));

[BCBuizer]

Hi @syhrh ,

Few questions: 

- After a test, can you check if the _NewBalance variable has the correct value or not?

- What is the intended purpose of _UpdateItem2? I don't see any balance field in 'Leave Requests'.

[syhrh]

Hi @BCBuizer,

After a test, can you check if the _NewBalance variable has the correct value or not?

- No, I cannot check it

What is the intended purpose of _UpdateItem2? I don't see any balance field in 'Leave Request'.

- I intend to do the deduction for the clicked item based on the ID. But then, the formula will exclusively deduct the 'Leave Balance' based on the employee name and leave type in the 'Leave Request' right?

[BCBuizer]

@syhrh ,

In case you won't provide feedback on the performance of the suggested code, there's only so much I can do to support in finding a solution. May I suggest to insert a temporary label to display _NewBalance so you can see the value throughout the use of the form?

In case you want to Patch 'Leave Balance' based on Employee ID, rather than DisplayName, you can just change the code in the LookUp for _UpdateItem. As it is now, the _UpdateItem is defined as the first item in the 'Leave Balance' data source that matches the DisplayName and Year values in the Leave Request form, respectively DataCardValue 19 and DataCardValue20.

[syhrh]

@BCBuizer 

Can't I patch based on the ID and match it with the employee name and year? This formula has errors

With({_UpdateItem: LookUp('Leave Request',ID=SharePointIntegration.SelectedListItemID) And 
LookUp('Leave Balance', 'Employee Name'.DisplayName = DataCardValue19.Selected.DisplayName And Year = DataCardValue20.Text)},

I'm not really sure how to do the checking with the label to view the balance as the buttons are visible only when the SharePoint form is in view mode and the SharePoint form in Power Apps default property is on New mode. Can you please elaborate on how to do the checking?

Thank you.

[BCBuizer · Accepted solution]

Hi @syhrh ,

You mean to pull the leave request, using the ID and extract the Name and Year from that?

That would look something like this:

With({_UpdateItem: LookUp('Leave Balance', 
	'Employee Name'.DisplayName = LookUp('Leave Request',ID=SharePointIntegration.SelectedListItemID).'Employee Name.DisplayName And 
	Year = LookUp('Leave Request',ID=SharePointIntegration.SelectedListItemID).Year
)},