Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to check if SharePoint item already exists in Power Automate

Posted on May 2, 2021May 2, 2021 by Tom

“I’d like to check with Power Automate if employee entry already exists in a Sharepoint list I’ve created; if not, create an item, otherwise update it.”


When you store data in a SharePoint list, e.g. input from MS Forms, you might not want to add a new item for each entry. You might want to create a new item if it’s a new entry, but update the item if it already exists. So, how do you check if an item already exists?

Get the item(s)

There’re two steps to check if SharePoint item already exists. Firstly, you must ‘Get items’ from the SharePoint list that correspond to the potentially new entry. It’s the same approach as when you build a lookup to SharePoint list. You’ll need one column that’s unique, a column whose data are in the list and also in the new entry. It can be an ID that’s provided on the input, user email address, or any other unique identifier.

Once you know which column to use, you can use it in the ‘Get items’ action in the Filter Query. Get only the items where the unique column is equal to the new entry. For example, a single line of text column called ‘Email’ that contains ‘Responder’s Email’ from MS Forms.

Email eq '[Responder's Email dynamic content]'
Power Automate lookup SharePoint items

Note: Always use the eq (equals) operator, you want to get only the item with the same value. And don’t forget to use the column internal name.

Check if the ‘Get items’ output is empty

The second step is to check if the ‘Get items’ action returned any item. After applying the Filter Query it’ll return an item only if it already exists. If it didn’t find any item, the output will be an empty array. That means you’ll need to add a ‘Condition’ to check if the output is empty. You can use the length(…) expression here to check if the output array length is 0 (empty) or more than 0 (not empty) items.

length(outputs('Get_items')?['body/value']) is equal to 0

If the length(…) is 0, the item doesn’t exist yet, you can create it. If it’s not 0, the item already exists and you can use the ID from ‘Get items’ to update it.

Power Automate check if SharePoint item exists

Don’t worry about the ‘Apply to each’ around ‘Update item’. If you used a column with unique values in the Filter Query, it’ll return just a single item.

Summary

The approach to check with Power Automate if a SharePoint item exists is the same as checking if a file already exists. There’re always two steps, do a ‘lookup’ to the list and check if anything was returned. If not, the item doesn’t exist and you can create it. Otherwise you’ve got the item in the ‘Get items’ output, including the ID to use for the update.

If you’d like to search by other column than single line of text, take a look on the Filtering section or subscribe to the newsletter below to get a SharePoint Filter Query cheat sheet.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

14 thoughts on “How to check if SharePoint item already exists in Power Automate”

  1. KAYUMBA Thierry says:
    July 23, 2021 at 2:34 am

    I have a SharePoint (Multiple choice) and can choose multiple answers
    when I am checking the columns if empty with Fx (expression) with length outputs equals to 0 is not giving output, please advise

    Reply
    1. Tom says:
      July 24, 2021 at 9:32 am

      Hello Thierry,
      if it’s not giving output then the field is probably empty, there’s no output to give.

      Reply
  2. Tim says:
    September 5, 2021 at 3:50 am

    Hi Tom,
    It looks like expresion

    length(outputs(‘Get_items’)?[‘body/value’])

    requires its value to be a string or an array. I have received an error upon executing condition:

    The template language function ‘length’ expects its parameter to be an array or a string. The provided value is of type ‘Null’.

    I have modified expression to include a “string” function and it was ok.

    Also, I tried to follow your guidance, but can’t get rid of “apply to each” loops everywhere”
    It forces it at “get items with filter query” step, (for each row)
    It forces it at “create item if length=0” step (for each row)
    Am I missing something?

    Many thanks in advance

    Reply
    1. Tom says:
      September 5, 2021 at 5:22 pm

      Hello Tim,
      ‘Apply to each’ is added automatically every time you use dynamic content that’s coming from an array. If you use the output from ‘List rows…’ in a Filter Query, it’ll automatically add ‘Apply to each’ to repeat the action for each row. The same if you use the output from ‘List rows…’ in a ‘Create item’ action.
      It’s not necessarily a problem, if you use filters and the actions return just a single item it’ll loop only through that one item. But it shouldn’t be multiple ‘Apply to each’ through the same array, that could mean that you’re trying to access outputs from various actions, not only from the original ones (that caused the original ‘Apply to each’, and in which you should stay during the flow).

      Reply
  3. John Brines says:
    May 10, 2022 at 5:29 pm

    Hi There,

    Is this possible to call from a powerapp. I have a sign-in powerapp that updates a sign-in sharepoint list. We want to check a Site Induction list for the Full Name and Company Name to see if it exists and if it does it adds the date to the Sign-In list.

    John.

    Reply
    1. Tom says:
      May 15, 2022 at 5:24 pm

      Hello John,
      you can use the same approach from Power Apps. Send the values as the input parameters from Power Apps and then use them to check the SharePoint list.

      Reply
  4. Georgia says:
    September 5, 2022 at 6:30 am

    I have created this flow, however when I get to If Yes and Create Item it is automatically adding Apply to each. This means my test shows the flow was successful but it is not creating a new object in my list

    Reply
    1. Tom says:
      September 18, 2022 at 6:53 pm

      Hello Georgia,
      one of the fields in your SP list is probably multiple selection field – it add ‘Apply to each’ to process each selection, but if there’s nothing to select there’s no loop = nothing is created. You’ll have to somehow preprocess the multiple selection field as explained here: https://tomriha.com/stop-power-automate-flow-from-adding-apply-to-each-automatically/

      Reply
  5. Jono says:
    September 20, 2022 at 9:27 am

    Hi Tom,

    i’ve created the condition: length(outputs(‘Get_items’)?[‘body/value’][0][‘ID’])

    But as ‘ID’ is an integer I am getting a failed response, how do I do a check for a number column

    Thanks

    Reply
    1. Tom says:
      September 27, 2022 at 3:33 pm

      Hello Jono,
      the length() expression is applied on the whole output of the ‘Get items’ here to see if it returned any items or not. If you want to search for items with a specific number you have to use that filter as the Filter Query in the ‘Get items’.

      Reply
  6. Mridul says:
    March 29, 2023 at 4:03 pm

    Hi Tom, thank you for this, however I am unable to see the option in filter query for the form under dynamic content.

    Reply
    1. Tom says:
      April 9, 2023 at 1:10 pm

      Hello Mridul,
      the dynamic content in the ‘Filter query’ always depends on the previous actions, let it be ‘Get response details’ or ‘Get items’ or any other action that’ll give you the unique identifier for the item.

      Reply
  7. Mridul says:
    March 30, 2023 at 2:20 pm

    Hi Tom, I am able to create the flow, however when I run instead of updating the existing value it keeps on creating a new entry. Does the column type also matters? Here my email is of person or group type

    Reply
    1. Tom says:
      April 9, 2023 at 1:13 pm

      Hello Mridul,
      the column type matters as there can be different syntax based on the column, person or group is explained here: https://tomriha.com/power-automate-filter-based-on-person-or-group-column/

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with 1000s of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes