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

[CS90]

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! 

[MarvinBangert]

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]

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 🙂 

 Thanks again for your support!