Capturing staff capacities for the week.
Staff member opens the microsoft form and just enters a number between 0 and 100 of how much time they think they'll have available for the following week.
This then updates a sharepoint list and displays the data as a Quick Chart on our sharepoint page.
Something happens through the week and a staff member wants to edit their capacity.
They resubmit a form and it overwrites the previous entry.
I've come really close to solving this one thanks to this: https://powerusers.microsoft.com/t5/Building-Flows/Update-existing-or-create-a-new-SharePoint-item-v...
In my case, I have staff submitting a form and in the past I have used the "Get user profile" and from that "Given Name" and had this as the Title column or ID column in my Sharepoint list, and then the data from their response in the next column.
I need the ability to detect previous items already in the sharepoint list and update according to name, and if it doesn't detect a name already in the list, then create a new item.
I've tried the above but get stuck at the user ID bit... any suggestions?
If you are using the Given Name as the value for title in SharePoint, then you should be able to do a Get Items in the Flow with an OData filter for Title eq 'Given Name'. Then check to see if that returns any records. If it does then you can use the First ID from that get items as the item you want to update. If no records were returned then add a new record. Something like this
Thanks @Pstork1 ,
Still seem to be having an issue though... I've run the test and it gets stuck on the spinny wheel of death.
Here's a copy of my current flow.
Can you send the screenshot of the Flow in several sections? The resolution on the screenshot you embedded is just to low to be readable. Also, when you look at the failed Flow runs where is it timing out? What action.
Oh, so sorry. Here are individual screen captures.
And I don't even get the chance to see where the failures are when I run the test. I hit test, submit a form and it just stays on a blank screen. No run reports, no errors. Nothing. Just blank with a spinning circle.
The logic is correct. If when your Flow run, the screen is blank, you can exist the Flow to My Flow list, click the Flow, then click the run history. Or you could test Flow using data from previous runs.
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Several specific issues I see.
1) Can you show the advanced settings for the Get items? I assume you are filtering at this point for the specific user who created the Form response.
2) I don't see the point of creating the array of titles. If your get items is filtering then you should get either one or no items back, not multiples.
3) Why are you comparing length() to something from the Forms? The idea would be to try to retrieve the specific list item where Title equals the name of the submitter of the Form. If found, length() > 0, then update a record, if not create a new one. I don't see what this comparison accomplishes.
4) Why in condition 2 are you comparing Link to Created by? Since you are creating your records via FLOW the created by will be the account running the Flow, so there will never be a match unless you report your availability.
The flow was turned off, hence the blank screen. Since turning back on, I was able to run the flow which has now helped to identify where the error is. Thank you! 🙂
So here's where the error is:
This is why in my sample I based my comparison against the email address rather than the given name. It looks from the error like someone has a special character in their name which is interferring with the JSON.
Yes, there is one staff memeber that has a hyphenated surname. Would this cause the interference? If so, would I get around this by using the email address comparison?
It wasn't a hypen I saw. It looked like someone had an apostrophe in their surname. Single quotes are used to set things off in JSON so to pass them as a value they need to be escaped, a single quote passed to JSON should be ''. If you have a person whose name is something like O'Brien that will throw off the JSON. Emails don't allow the use of ' in an email address.
Thanks for this solution. I've been able to get the method working using the 'Title' in the destination SharePoint List. Would this work for other SharePoint List columns also? I'm trying to use the email address of the form respondent at the key value, but this is not saved in the Title column. Having some trouble getting it working. It is returning the error:
"The query is not valid.\r\nclientRequestId: 1a822f1a-147d-4ed6-b6a5-11f34eedd14e\r\nserviceRequestId: 1a822f1a-147d-4ed6-b6a5-11f34eedd14e"
Any pointers greatly appreciated. Thanks, Jason.
It should work with any column where you've saved the value as long as the column isn't a complex column like a person/group column. Those store objects, not text so its more involved when trying to do a query against them.
Check out the News & Announcements to learn more.
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.