Ideas for flow to update a sharepoint list when item created/modified in other sharepoint list

[dwalker13]

Hello,

I'm building a PTO tracker for my organization and want to leverage Power Automate to help with the PTO calculations.  I have one SharePoint list that is simply a list of all of our associates with their annual PTO grant.  I have another events list in the form of a calendar that will be used to enter PTO dates for each associate.

What I need is a flow that will trigger when PTO is entered(created or modified)for an associate in the calendar list and then update a column in the associates list with the number of days that were entered on the calendar for that associate.  To keep it simple, imagine the associate list has a name column, annual PTO grant column, and a PTO taken column.  The PTO taken column is the one I need the flow to update when the calendar is updated.

I've gotten the basics built for triggering the flow and getting items but where I'm having trouble is getting the flow to recognize how to determine which record in the associate list to update.  For example, if I enter pto on the calendar for Sarah, how do I get the flow to recognize Sarah's record in the associate list and update her PTO taken column?

Also, I assume I will need to use some kind of variable to store the PTO calculation from the calendar.  Not sure exactly how that works either.

One other thing, I'm using a lookup column in the calendar form that is linked to the name column in the associate list to choose which associate we enter PTO for.  I thought that might help the flow recognize which record to look for in the associates table but I haven't had any luck so far.

Any help folks on the forum can provide is greatly appreciated!

[jinivthakkar]

@dwalker13 I was able to do this to some extend.

Components:

1- Employee List - Has emailId's of users

2 - PTO List - Has leave granted and lookup of employee list, added additional column for email Id of user(which will be used to query, some issues in querying lookup column)

3 - Calendar list - Where user can request for leave

Logic :

1 - When new record is created in Calendar list, flow is triggered 

2 - Initialize variable to email ID of the user who created item from step 1 

3 - Get items, filter = EmailID eq 'variablefromStep2'

4 - Update item(in for each loop as above action is get item(s)) updated value from 10 to 1(hard-coded) for poc

You can try this and do calculations as required, in the list where you store leave granted and taken need to have one more column for email ID and make everyone's life easy.

Two options here : 

1- If the system is new - add a flow which will update the email ID of user from the lookup value.

2 - If the system is old and already has lot of data, as one time activity run a scheduler flow which will iterate through each item and update values for each record.

Hope this helps.

Please accept solution, if this helps.