Unanswered

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

(0) Share

Report

Posted on by Community Power Pla...

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!

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

efialttes 14,756 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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!

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
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

Was this reply helpful? Yes No
Dominique Aboudaram 20 on at

Like (2)

Report

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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report

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-256-row-limit-any/td-p/116355#M5357

Hope this helps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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"}}}}

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report

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!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (1)

Report

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

Was this reply helpful? Yes No