“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).