Help with PA Script to update a SharePoint list value, only if ID matches

[EDubb]

Hello PA Friends,

I am trying to automate the management of a single SharePoint list that contains a roster of employees as staff on-board and off-board. I have a Status column in that list, as well as an Employee ID.

I have a PA script that triggers when a New Employee MS Form is submitted; that sends an email to HR and it adds a new record to the SharePoint list, to include the Employee ID, and sets the Status as "Active."

I have another PA script that triggers when a Separating Employee MS Form is submitted; which includes collecting the Employee ID; that sends a notification to HR. I am looking for a way to have this script look up the Employee ID in the SharePoint List Roster and update the Status to "Former" for that employee.

Any help would be greatly appreciated as I have been struggling with this for a few weeks.

Thanks!

EDubb

[grantjenkins]

I would probably use Get items with a filter on Employee ID is equal to the Employee ID in the MS Form. This should return a single item but will still be an array containing the single item.

Next, I would have a Condition to check that the length of Get items is equal to 1 and if true, proceed to the next step which is:

Use Get item using the ID of the first item in Get items. Then use Update item to update the item - Status = 'Former".

The expression to get the length of the items returned would look something like:

length(outputs('Get_items')?['body/value'])

And the expression to get the ID of the first item would look something like:

first(outputs('Get_items')?['body/value'])?['ID']

I can mock up a quick example for you if you like.

[EDubb]

Hi grantjenkins! Thank you so much for your reply. Very helpful. Unfortunately, I am more of a visual learner, so if you have a few minutes to do a mock-up, that would be great. I can send some screenshots of what I did if that would be helpful.

[grantjenkins · Accepted solution]

I'm assuming you have the flow working to add the item into your list already.

Below is a flow that will get the Employee ID from the second Microsoft Form and update the relevant Employee record in the list. For this example, I've got a simple list that contains two columns. The Title column (that I've renamed as Employee ID), and Status which is a Choice column which is set to Active by default.

I then have a simple Microsoft Form that just contains the Employee ID to be filled in. This would be your Separating Employee MS Form.

Below is the full flow. I'll go into each of the actions.

When a new response is submitted and Get response details just get the Form responses which would be the same as your other flow.

Get items will retrieve all items that match the Employee ID entered in the Microsoft Form. We use the Filter Query as shown in the screenshot below. Note that I'm using Title since this is the internal name of the column that contains the Employee ID. I've also set Top Count to 1 but should only return a single result anyway.

When we add an Update item and add ID from Get items, it will put it into an Apply to each. Even though we are returning as single item from Get items, Power Automate will still return it inside an array. In our case the Apply to each will only run a single time for our single item so all good.

In Update item we pass in the ID and Title from Get items, then set the Status to Former.

If we entered EMP000456 into the Microsoft Form the result in our list would look like the following.

[EDubb]

Thank you, grantjenkins!!  My error in my original flow was in the get items action. I did some re-naming of the SharePoint list columns to match your screen shots. Worked perfectly. Thanks for your help on getting me underway on my new PA journey!