Flow Forms to List, Update Item works, but Create Item Not

[sasesi190101]

Hi

I have a weird issue, the same flow works for updating, but not on the creating item part. Flow presented below:

Basic start:

After this, there are no differences:

If yes: So if the ID in the Form matches with the title on MS List, update works as expected.

If no: But if there is no such item in the list, I would like to have a new item. The logic works if I build another basic flow creating item, but not on this. Also I have tried to change the "value" to "Response ID" but no luck.

The flow runs without errors, but no new items are created to the MS SharePoint List.

Thank you if you can help me! I have read many articles, but couldn't get those kind of flows working at all.

[wskinnermctc · Accepted solution]

You don't need to have another Apply to Each to create an item. You don't want to create a new item for each current sharepoint list item that doesn't match the condition. Also the condition going through each item in the list to see if it matches uses up a lot of calls if the list is large.

You want to trigger when a form is created, Get List Items filtered by the matching form column, check if any results were returned, if so update the items from SharePoint, if not create a new item.

Filter SP Get Items Column by Response ValueIf No Item in SP then Create New Item or Update Matching Items

Let me know if this works for you

[sasesi190101]

Wow thanks @wskinnermctc !

This works, thank you so much, even though I do not fully get the logic 😄

Have a nice week 🙂

[wskinnermctc]

Glad it works for you.

The flow I showed is designed to either add a new item to a list if it doesn't exist already, or update ALL rows that match the filter. Most lists would have a 1 to 1 match where there's only 1 row per person in the list; therefore, only 1 row would need to be updated if an employee already existed. However, if you had a list with multiple rows per employee, it would update all of the rows.

To give an example that isn't exactly practical, but explains what is going on:

Let's say you had a SharePoint list of employees. The list has EmployeeName, EmployeeID, and contact info like PhoneNumber and OfficeNumber. There should only be 1 row per employee.

You could have a MS Forms that could be submitted whenever an employee changes their phone number or office location. And in the MS Form you put the EmployeeID, EmployeeName, PhoneNumber, OfficeNumber.

When the form is submitted the power automate is triggered for that submission item. You get the EmployeeID from that submission item and use it in the filter for the SharePoint Get Items. You're saying, "Give me all of the items in the SharePoint list that match this EmployeeID."

So the Get Items action has an output of rows in the list. If the employee already existed on the list, then the Get Items will have a result filled with that row's information. If the employee did not exist on the list (no EmployeeID matched the filter), the Get Items result will be blank or empty.

The next step is a condition that checks the Get Items result. It is checking the step as a whole, not necessarily individual items. The condition is asking, "Did the Get Items retrieve anything at all?" If it is empty, that means nothing matched the EmployeeID and the Get Items is blank. If the Get Items is not empty, then that means the EmployeeID did match something and the Get Items now contains that row.

After the condition, it is saying "If the Get Items was empty, then add a new employee to the list." But "If the Get Items was not empty, I want to update each of the results of the Get Items with the information from the MS Form response."

Since there is only 1 row per employee, the Apply to Each will only run for the 1 result that is in the Get Items.

However, lets say there was an error when the list was created and the employee John Jacobs was added to the SharePoint list 4 separate times. So the list contains 4 identical rows of John Jacobs.

If you submitted the MS Form to update John Jacobs contact information it would trigger the flow like normal, and in the Get Items step the filter of EmployeeID would return 4 rows of results. The condition checks if the Get Items was empty, which it is not, so it goes to the Update Item step. The condition doesn't care if it has 1 row or 50 rows, it only wants to know if Get Items has anything.
The apply to each will now run 4 times updating each row that was in the Get Items results.

Maybe the key to understanding is the Get Items action. If you have a list of 400 items and you use the Get Items without any filters, then the Get Items will have 400 results. If you used an apply to each on this Get Items then it would run 400 times.

However, if you have the same list with 400 items and use the Get Items with a filter and it has 4 rows that match the filter, then the Get Items will have 4 results. If you used an Apply to Each on this Get Items then it would run 4 times.

The same would happen if the 400 item list was filtered and only returned 1 row.

Power Automate automatically uses an Apply To Each whenever it is referencing a Get Items, because it doesn't know if you are going to return 1 row or 400.

Hope this helps!

[CROKat]

Hi, I am trying to build a similar flow with no luck. I'm confused about the filter query under Get Items. I would like the SharePoint List to update or create a new item based on a form submission, if the request ID the user enters is the same as an existing item it will update; if not, it will create a new item.

I set it up the same way as yours, including the condition expression, etc.

The column I want to search for is called "Request ID". I keep getting error messages saying that the expression is not valid and the query failed.

[wskinnermctc]

Hi @CROKat , I believe your column name in the Filter Query is incorrect. And it also depends on if your sharepoint list is storing the "Request ID" as a number or a string.

For the column name, you need to use the InternalName of the column which you can look up more about in a post I made here.

If your "Request ID" column is stored as a Text/String then you do have the ' single quotes around the "Request ID".

If your "Request ID" column is stored as a Integer/Number then you do not have single quotes around the "Request ID".

If your "Request ID" column is stored as a Integer/Number then you will most likely have to change the form response from a string to a number.

@CROKat you could create a new forum post asking if you have any further problems.