Create new items in SharePoint list from Excel table doesn't work anymore

[Anonymous]

Hello everyone, 

I need your help! I have a flow that worked for some time and now is not for no apparent reasons, and with no errors...

I have a simple flow that pull data from an Excel file to update a SharePoint list. If the item is already on the SharePoint list, it updates the data, if not it create the new entry. Worked perfectly fined. I'm putting sreenshot of the flow in attachement for the details.

For some time now I realized it did not create the new items anymore. The update is working fine, so I can just create quickly manually the new entry with just the value I'm using for the lookup, and it will update. But I have no error message, nothing.

When looking at the Flow execution steps, it goes correctely to the conditions : if the ID field is Blank, then create, otherwise update. Test : 

empty(body('Get_items')?['value'])

The issue is that it goes through the Create item conditions, execute with no errors (but 0 secondes spent) and continue. It worked before and I'm pretty sure I haven't touched anything since then. Any idea what can be the issue? I tried to delete the condition and recreate it, with no avail. 

Any help is welcome, let me know if you need more information. Thank you!

[efialttes]

Hi!
Let me see I understood the problem... the condition evaluation still works fine? I mean, if there is already an item in sharepoint, it takes the false branch, and if there is not item yet in sharepoint, it takes the true branch?

Some more questions... how many rows in your excel? If more than 256 you need to activate pagination. And... how many items in your sSP list? If more than 100, you need to increase Top Count, if more than 5,000 you need to activate pagination
Hope this helps

[Anonymous]

Hi @efialttes thanks for the reply!

- You understood correctly. The condition is working fine, it goes to the correct branch. IF blank (item not existing yet) it goes to the "true" branch. It goes without an error but don't create the item anymore.

- There is 157 rows in the Excel file as of now. I'm planning to clean up data regularely to keep it less than 160 rows. 100 should be a max at terms, but thanks for the hint, I'll check this pagination concept I'm not familliar with.

- The number of items in SharePoint should be the same (I'll add later a condition to delete the item if not present anymore in the Excel file). So the issue may be the Top Count? I'll have a look at that as well, not know exactly what is it. There is no way the list is going over 5k rows.

Thanks, I'll keep you posted if it works!

[efialttes]

Hi again!

---

---

So if more than 100 items in your sharepoint list, please apply the steps explained in this great post:
https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

You can easily verify if you are reaching the default limit, just add a dummy Compose action block after 'Get items', and assign it the following expression

length(body('Get_items')?['value'])

If you are not yet familiar with adding expressions please follow the steps detailed here:

https://flow.microsoft.com/es-es/blog/use-expressions-in-actions/

Now, everytime you execute your flow the dummy Compose will print the nr of items present in the output

Actually, you can apply a very simple trick to verify if 'Create item' is working, but for some reason your Sharepoint view is not showing it in the browser, that is...

Just after 'create item', add a new 'Get items', then add another dummy compose action block. I assume Flow editor will rename new 'Get items' as 'Get items 2', if so the expressionfor this second dummy Compose should be

length(body('Get_items_2')?['value'])

Hope this helps

[MissDom]

Did you try simply to reselect the Excel file and table in "list rows present in a table" ?

The table reference is hard coded and I have noticed in the past that if I create an identical file with identical table name, than I am not able to query it

hope this helps

[efialttes]

Hi!

Forgot to explain how to activate pagination guen using 'List rows present in a table', please check solution here:

https://powerusers.microsoft.com/t5/Connecting-To-Data/Excel-Business-List-rows-present-in-a-table-2...

Hope this helps

[Anonymous]

Thank you both for your ideas (this time I have hit the Like button!)

Unfortunately those solutions didn't work. I upped the Top Count limit, activated the pagination, but the same results.

I'm not sure it is related to the Get Items or the List table, because it collect the data in Excel correctely, and get the info from SharePoint correctly as well, because the update is working well. I don't understand why the creation part was working and not anymore.

Here is the code for the whole "Create Item", maybe it helped (I tried to anonymize some part of the code, please let me know if I let something in it that I shouldn't have!)

{"id":"bd57a9d1-8e95-xxxxxxx","brandColor":"#036C70","connectionReferences":{"shared_excelonlinebusiness_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-043bae83-xxxxxxx"}},"shared_sharepointonline_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-4409ad72-2a57-xxxxxxxxxxx"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-d71e3022-71f4-xxxxxxxxxxxxxx"}}},"connectorDisplayName":"SharePoint","icon":"https://connectoricons-prod.azureedge.net/sharepointonline/icon_1.0.1329.1953.png","isTrigger":false,"operationName":"Create_item","operationDefinition":{"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['shared_sharepointonline_1']['connectionId']"}},"method":"post","body":{"Title":"@items('Apply_to_each')?['Project ID']","Project_x0020_Name":"@items('Apply_to_each')?['Project Name']","Project_x0020_Description":"@items('Apply_to_each')?['Project Description']","Programme":"@items('Apply_to_each')?['Programme']","Project_x0020_Status":"@items('Apply_to_each')?['Project Status']","Start":"@items('Apply_to_each')?['Start']","Technical_x0020_Go_x0020_Live":"@items('Apply_to_each')?['Technical Go Live']","Business_x0020_Go_x0020_Live":"@items('Apply_to_each')?['Business Go Live']","Planned_x0020_Closure":"@items('Apply_to_each')?['Planned Closure']","Progress":"@items('Apply_to_each')?['Progress']","Phase":"@items('Apply_to_each')?['Phase']","PM":"@items('Apply_to_each')?['PM']","Lead_x0020_Architect":"@items('Apply_to_each')?['Lead Architect']","OTP_x0020_Number":"@items('Apply_to_each')?['OTP Number']","Budget_x0020_Cost":"@items('Apply_to_each')?['Budget Cost']","Supplier":"@items('Apply_to_each')?['Service Provider']","SharepointSite":"@items('Apply_to_each')?['Sharepoint Project Site']","BRM":"@items('Apply_to_each')?['BRM']","PeerReviewer":"@items('Apply_to_each')?['Peer Reviewer']","ServiceManager":"@items('Apply_to_each')?['Service Manager']","ProjCreatedDate":"@items('Apply_to_each')?['Created']","IAM":"@items('Apply_to_each')?['IAM?']","Dynamics":"@items('Apply_to_each')?['Dynamics?']","OData__x004f_365":"@items('Apply_to_each')?['O365?']","Service_x0020_Now":"@items('Apply_to_each')?['ServiceNow?']","SAP":"@items('Apply_to_each')?['SAP?']","Other":"@items('Apply_to_each')?['Other?']","ProjectDepartment":"@items('Apply_to_each')?['Department']","BudgetOwner":"@items('Apply_to_each')?['Budget owner']","LastSteerCo":"@items('Apply_to_each')?['Last SteerCo']","NextSteerCo":"@items('Apply_to_each')?['Next SteerCo']"},"path":"/datasets/@{encodeURIComponent(encodeURIComponent('https://xxxxxxx'))}/tables/@{encodeURIComponent(encodeURIComponent('2f7b98dc-da35-4f55-9cdc-2c62fbbeba07'))}/items","authentication":"@parameters('$authentication')"},"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"PostItem"}}}}

[efialttes]

Hi again

Can you share a screenshot from your 'Create item' design? I think it's gonna be easier to inspect it, and the first screenshot you share seems not to show the whole action block.

Also, since you inspected flow execution, did you verify nr of times inner 'Apply to each' iterates? Can you share a screenshot from this also?

One final suggestion: why don't you add an additional 'Get items' at the bottom of your flow, and then count current nr of items? So maybe you are using some sort of filter view on Sharepoint and you do not see new items anymore on your browser

THanx!

[Anonymous]

Soooo, more details. Thanks for the Compose tip, it's a great help for debugging.

Here is the detail of the Create Item bloc. It's much longer but after that it's just more field to populate. As it doesn't even go to that step I doubt it's relevant.

Create Item bloc

Running the flow with the compose, here is the output with a "false" results condition (going to Update Item branch) and the a "true" results condition going to the Create Item branch.

False : updating OK

True : stop after the apply to each

As you can see, this is an "Apply to each 3" as I already tried to remove the branch and create it again, to no avail.

Any way to see more in detail what is happening in this apply to each? Related to the "input 0" out of the first Compose? 

[Anonymous]

In addition, to confirm the filters or the view on the sharepoint list, I'm just doing a query in Excel to control the data (and to compare with my source Excel file).

Something just come to mind... I don't have a delete function yet, so I have entries in my list that are not anymore in my source Excel file. Would that create an issue in the current scenario? I don't see why, but exploring all options...

[efialttes · Accepted solution]

Wait, wait, wait... you need to move 'Create item' out of 'Apply to each 3' and remove 'Apply to each 3'. I mean, if 'Get items' output is empty (true branch), 'Apply to each 3' iterates over... 0 elements, so action blocks inside it are never executed.
Hope this helps

[Anonymous]

It make total sense! It's more clear thanks to the compose. I'll try it.

But did I need to indicate the create item to use the "Value" from the original Get Items? What is the best way to do it? I think it is why I put the Apply to each originally.

[Anonymous]

It worked, thanks a lot! I marked it as solved.

The debugging trick with Compose is something that will help me a lot down the line, thousand thanks. Now I'll start working on the delete function to keep the list clean... more fun ahead!

Greetings @efialttes