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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,180)