web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Patch a list's column ...
Power Apps
Answered

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

(0) ShareShare
ReportReport
Posted on by syhrh 997 Super User 2024 Season 1

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

anonymous21_1-1660623632279.png

this is a Sharepoint integrated form in Power Apps.

 

anonymous21_2-1660623697323.png

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.

anonymous21_3-1660623937017.png   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.

anonymous21_4-1660624025827.png

How to do that?

 

Thank you.

 

Categories:
Building Power Apps
I have the same question (0)
  • Gochix Profile Picture
    Gochix 1,935 Moderator on at

    Hi @syhrh ,

     

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

     

  • syhrh Profile Picture
    syhrh 997 Super User 2024 Season 1 on at

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

  • Verified answer
    BCBuizer Profile Picture
    BCBuizer 22,577 Super User 2026 Season 1 on at

    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 Profile Picture
    syhrh 997 Super User 2024 Season 1 on at

    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 Profile Picture
    BCBuizer 22,577 Super User 2026 Season 1 on at

    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 Profile Picture
    syhrh 997 Super User 2024 Season 1 on at

    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 Profile Picture
    BCBuizer 22,577 Super User 2026 Season 1 on at

    @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 Profile Picture
    syhrh 997 Super User 2024 Season 1 on at

    @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.

     

  • Verified answer
    BCBuizer Profile Picture
    BCBuizer 22,577 Super User 2026 Season 1 on at

    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
)},

     

  • syhrh Profile Picture
    syhrh 997 Super User 2024 Season 1 on at

    Hi @BCBuizer, it works. Thank you!

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

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the February Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 549 Most Valuable Professional

#2
Kalathiya Profile Picture

Kalathiya 225 Super User 2026 Season 1

#3
Haque Profile Picture

Haque 224

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans