cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EDubb
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
grantjenkins
Community Champion
Community Champion

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.

grantjenkins_0-1668904514526.png

 

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.

grantjenkins_1-1668904586639.png

 

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

grantjenkins_2-1668904636345.png

 

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

grantjenkins_3-1668904665235.png

 

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.

grantjenkins_4-1668904789890.png

 

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.

grantjenkins_5-1668904949703.png

 

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

grantjenkins_6-1668905091090.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

4 REPLIES 4
grantjenkins
Community Champion
Community Champion

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.



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
EDubb
Frequent Visitor

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
Community Champion
Community Champion

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.

grantjenkins_0-1668904514526.png

 

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.

grantjenkins_1-1668904586639.png

 

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

grantjenkins_2-1668904636345.png

 

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

grantjenkins_3-1668904665235.png

 

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.

grantjenkins_4-1668904789890.png

 

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.

grantjenkins_5-1668904949703.png

 

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

grantjenkins_6-1668905091090.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.
EDubb
Frequent Visitor

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!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,030)