Probably the best explanation of this issue I have seen on RUclips, and you have explained how the connector actually work compared to what many users assume. Excellent! Thank you John.
I gave this a go, and it works. Thanks. I have to wait between 4 to 6 minutes for the update but I have added an extra cell where the user specified whether the form is in draft / pending or ready to submit. It only add the data to the columns when it is marked as submitted so it is unlikely that anyone will edit it for quite a while.
Your flow delays the first update by 2 minutes. I implemented a similar update process where delay is a variable. Skip delay if delay = 0 then increment delay. The good part is that it leaves the door open for more advanced delay mechanism (geometric, exponential...). Also getting the user that locks the file is great, sometimes ppl forgot they got the file open. A notification can help.
Thank you John. Just to share, I solved this by using the Sharepoint "Check Out" and "Check In" functions. This way I´m "closing" the file and let it ready to be updated by someone else or available to delete it. At least it worked for me.
Excel files are locked for at least 6 minutes when uploaded or modified via Power Automate. Just add a 6 minute delay to solve the issue. Hope this helps!.
Hi and thanks for a simple, elegant solution - simpler than many others I've seen. Just one question: Given that the AND() statement returns a Boolean true/false already, why is it nested within an IF() statement.
Hi John, thanks so much for making this! I am trying to follow along but have am reading multiple rows in my excel file as opposed to 1 (using "List rows present in a table" instead of "Get row"). The issue I'm having is that it keeps wrapping my variables in a for loop since I have multiple rows instead of one and it doesn't get all row values for a column, just loops through all values and grabs the last one. Any suggestions as to how to get around this issue?
OK, so it "sounds" to me that you are grabbing 1 Excel file that contains an itinerary of data. For example like you would see for 1 invoice with that has multiple costs, or 1 quotation with multiple items in the form. Is this right? If so, this is more complicated but ultimately you need two lists with a "one-to-many" style relationship. List 1 will be for the main Form data (such as the invoice itself) and list 2 will containt all the items stored in the form (The invoice itinerary). Both lists will need the Invoice number or some unique value. Your Power Automate will then be updating two SharePoint lists with data within you Excel form. If I am on the right track, PLEASE let me know, I would be happy to do this as a video for you. If I have misunderstood your needs, ALSO please let me know so I can help you as best I can.
Hi John, Works like a charme…. until the error message with the 400 error is in another language. In dutch it says ‘vergrendeld’ instead of ‘locked’. Is there a way to make this solution multi language?
The language responds to your acount regional and language settings. Click the settings gear cog in the top-right corner, click View Account and go to the Settings and Privacy. If these language options are in the correct language, someone has messed with the tenancy default language settings in Admin. This can be a nightmare to correct, but it's for the Admin to fix.
The "Modified By" column contains lots of info about the current or last user account that accessed the content. You can add an email action to email someone and place the Modified By Displayname, and Modified By Email properties into the message.
@@JohnDayQA thank you! I adopted your flow to my reviewing and approval flow. Just have a question - where should I insert Teams message to the user to close the file? Would be very helpful
It’s a shame you don’t seem to post any more John, content like yours is rare and incredibly useful, thank you.
Probably the best explanation of this issue I have seen on RUclips, and you have explained how the connector actually work compared to what many users assume. Excellent!
Thank you John.
I gave this a go, and it works. Thanks. I have to wait between 4 to 6 minutes for the update but I have added an extra cell where the user specified whether the form is in draft / pending or ready to submit. It only add the data to the columns when it is marked as submitted so it is unlikely that anyone will edit it for quite a while.
Good sir, you may have just saved my life and my job! I'm going to comment back in a moment - this appears to have WORKED SUPERBLY!
That's great. I'm glad I could help.
Was stuck for a few days and thanks a lot for your solution. Please continue with your videos..
Thank you. This video completely fixed my issue, my files' properties are now updating.
Thank you very much for the helpful tutorial! It solved an Sharepoint file move issue I’ve had for a long time.
Great tip John. I had the same problem with MS Word Document in a SharePoint Library and your tip worked a treat.
Your flow delays the first update by 2 minutes.
I implemented a similar update process where delay is a variable. Skip delay if delay = 0 then increment delay.
The good part is that it leaves the door open for more advanced delay mechanism (geometric, exponential...).
Also getting the user that locks the file is great, sometimes ppl forgot they got the file open. A notification can help.
Thank you John. Just to share, I solved this by using the Sharepoint "Check Out" and "Check In" functions. This way I´m "closing" the file and let it ready to be updated by someone else or available to delete it. At least it worked for me.
Does this work is the file is opened by another user ?
I was trying to do the same thing, but it doesn't work.
If the file is open, the "Check Out" fails because the document is locked.
Thanks! You're a LEGEND!
I'm very grateful with you. I have this Error and you help me to fix. Thank you very much, go ahead....
Thanks, John for the great tips. Do until is the only approach or there are other options too?
Great content, exactly what I was looking for and works. You earn a subscriber! 😀
WOW, thank you!
Excel files are locked for at least 6 minutes when uploaded or modified via Power Automate. Just add a 6 minute delay to solve the issue. Hope this helps!.
Just EXCELLENT !! Thank You 👍
Glad you liked it!
Hi and thanks for a simple, elegant solution - simpler than many others I've seen.
Just one question:
Given that the AND() statement returns a Boolean true/false already, why is it nested within an IF() statement.
OMG, It worked, thanks a lot!
Hi John, thanks so much for making this! I am trying to follow along but have am reading multiple rows in my excel file as opposed to 1 (using "List rows present in a table" instead of "Get row"). The issue I'm having is that it keeps wrapping my variables in a for loop since I have multiple rows instead of one and it doesn't get all row values for a column, just loops through all values and grabs the last one. Any suggestions as to how to get around this issue?
OK, so it "sounds" to me that you are grabbing 1 Excel file that contains an itinerary of data. For example like you would see for 1 invoice with that has multiple costs, or 1 quotation with multiple items in the form. Is this right?
If so, this is more complicated but ultimately you need two lists with a "one-to-many" style relationship. List 1 will be for the main Form data (such as the invoice itself) and list 2 will containt all the items stored in the form (The invoice itinerary). Both lists will need the Invoice number or some unique value. Your Power Automate will then be updating two SharePoint lists with data within you Excel form.
If I am on the right track, PLEASE let me know, I would be happy to do this as a video for you. If I have misunderstood your needs, ALSO please let me know so I can help you as best I can.
WoooW! Thanks! Very good
Perfect !
Hi John,
Works like a charme…. until the error message with the 400 error is in another language. In dutch it says ‘vergrendeld’ instead of ‘locked’.
Is there a way to make this solution multi language?
The language responds to your acount regional and language settings. Click the settings gear cog in the top-right corner, click View Account and go to the Settings and Privacy.
If these language options are in the correct language, someone has messed with the tenancy default language settings in Admin. This can be a nightmare to correct, but it's for the Admin to fix.
Done the same but the do until loop stopps after that
How can we notify the user that the file is locked and by whom so the user can tell the other user to close the file?
The "Modified By" column contains lots of info about the current or last user account that accessed the content. You can add an email action to email someone and place the Modified By Displayname, and Modified By Email properties into the message.
@@JohnDayQA thank you! I adopted your flow to my reviewing and approval flow. Just have a question - where should I insert Teams message to the user to close the file? Would be very helpful