“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]'
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 0If 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.
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.
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
Hello Thierry,
if it’s not giving output then the field is probably empty, there’s no output to give.
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
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).
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.
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.
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
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/
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
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’.
Hi Tom, thank you for this, however I am unable to see the option in filter query for the form under dynamic content.
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.
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
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/
Hi, I am using this method and it works most of the time. But there are rare edge cases where the Get Items query is returning null when the Item Is on the list which means it ends up getting added multiple times.
In troubleshooting I test again and this time it does return the items. Any ideas? As I said, this works most of the time and it is just edge cases. Think it may have something to do with Caching because I found the response has this tag: “x-ms-apihub-cached-response”:”true””
I am calling the API about 100+ times because the list has that many items to check if it needs to add so I am not sure if it gets tired and just grabs a cache of the query instead of actually checking and finding the item is actually there? I am just brainstorming a cause
Hello Daniel,
if it’s that many ‘Get items’ calls I’d try to optimise it a bit. Instead of ‘Get items’ for each of them you can get all the items in the list and then use ‘Filter array’ to search for that specific item. That way it’d just 1 call to SharePoint and the rest would take place directly in the flow, maybe that’ll help.
Hello Tom,
I am trying to use a condition to check if an array from get items returns an empty array or not. Get items is filtered by email .The true statement has to create a new user when the array is empty, but my flow runs successfully but doesn’t create an output. I also have a problem with apply to each automatically appearing in the condition statements
Hello James,
it’s hard to tell without seeing the whole flow run history, but if it goes into a different branch then the ‘Condition’ is probably wrong. For the apply to each loops check this article: https://tomriha.com/stop-power-automate-flow-from-adding-apply-to-each-automatically/