cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CS90
Regular Visitor

Using Power Automate to lookup a value in a SharePoint list, compare it to an Excel list and return a name from the excel file into the Sharepoint List

Hello, 

 

I'm fairly new to Power Automate, I'm able to create approval flows and basic list updates from Forms etc but im a bit stuck on the steps to create a flow which does the following:

 

1. Data is captured into a Sharepoint List from a Microsoft Form submission (already created a flow that works for this).

2. I'd like to update the SharePoint list values against unique reference numbers (URN) and return a specific name associated from the excel file. 

 

Any help would be much appreciated! 

2 REPLIES 2
MarvinBangert
Super User
Super User

Hey @CS90 

so to understand you correct: When a new item is created in a SharePoint list (by another flow from MS Forms), you want to update this item using a second flow. This flow will check in an Excel-List in a column "unique reference numbers (URN)" for a specific value, and then updates the same item in SharePoint with some values from this Excel, right?

A couple of things to consider:

1. I would recommend instead of using two flows to update your first flow. This will make it easier to manage the whole process and make it easier to maintenance.

2. Nevertheless how you build your flows, you would need to do the following:

  • Excel action: "List rows present in a table"
    • If you are familiar with ODATA filter query, you could try to filter this Excel list by the URN you are looking for, please also check out this for tips: Power Automate list rows present in a table filter query - EnjoySharePoint
    • If you are not feeling comfortable using the ODATA filter, you would need to use an action "Filter array" after your Excel action to filter your Excel list by the URN you are looking for
      If you are using the "Filter array" you should also consider to use the "Parse JSON" action. The "Content" is your dynamic value "Body" from "Filter array" and "Schema" you can create when you just test run your flow once, going into the "Filter array" action, copying the "body", edit your flow again and click on "Create from example" within the "Parse JSON" action. Paste your copyied code into this window and click on "OK", then you should see a schema.
  • Afterwards you can add an SharePoint action "Update item" and add the dynamic values you want to change. Adding some dynamic values to this action will probably add an "Apply to each" around your SharePoint action. Please make sure you are using the filtered items (like if you are using an filter array and parse JSON, you need to select the dynamic value from the "parse JSON" section).

 

If you are using two flows, please make sure you are using the "When an item is created" and not "When an item is created or updated", otherwise you will run into a loop, because the flow is always starting itself again. Thats another reason to consider using one flow instead of two.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

CS90
Regular Visitor

Hi @MarvinBangert , 

 

Thanks for your response and guidance. To clarify im going to add some images to illustrate what im trying to do. 

I agree, merging the two flows into one is what I want the end result to be so long as the run time is not excessive. 

 

I think your understanding for my help request is right 🙂 

 

Test.jpg Thanks again for your support!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,366)