Forms and Sharepoint - Update item based on user profile

[Anonymous]

Hi,

In my organisation we want to send out a weekly Form to inquire about the availability of our staff for the coming week. 

Because this from will be repeatedly weekly I want to update items, rather than creating new ones. I've set up a flow to create a new item if a user hasn't filled in the form yet or to update the item it if the user has. The identifier I'm using is the respondents name (see flow below).

Currently my flow will not create a new item, but it will still run just fine. IDK if it will update an item, haven't tested that yet but probably doesn't work either. I suspect it has something to do with the sharepoint 'consultant' and office 'display name' variables not matching up.

Cheers,

Robin

[efialttes]

@Anonymous 

For troubleshooting purposes, I would suggest to add two Compose action block, the first one after Get User Profile, showing the "Display name"  value and the second one before the Condition action block, showing the current item's "Consultant" value. Once you execute the flow, you can compare the format of both fields by inspecting the output of "Compose" action blocks added.

Hope this helps

[Brad_Groux]

You can't use Consultant in the condition, because the Create item step has no comparison for it, because it doesn't exist in the list you're referencing within the condition.

You need to utilize something from either the Forms or User profile steps for the logic to work.  From the look of your flow, Consultant would be the logical comparison item and it would be a null expression comparison.

With that said, you likely need to rethink the overall design of the flow too. I suggest parralel branches, one for create, and one for update. The blog post Add parallel branches in flows explains it, or here is an example:

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hey, I can't check the formatting of the compose block for the 'consultant' value, since it keeps nesting itself in an apply to each.

[Anonymous]

Thanks for the reply Brad.

I've structured the flow into two branches now. Excuse me for being a layman here, but I can't follow the rest of your explanation.

The value 'consultant' gets populated from the 'display name' associated with the 'responder email'. From my understanding I will always need the value 'consultant' since that is my identifier in the list. Then the identifier from Forms will be 'display name'. If those two names line up I want to update the item. 

Sorry for making you walk me to the door!

[Brad_Groux]

---

@Anonymous wrote:

Hey, I can't check the formatting of the compose block for the 'consultant' value, since it keeps nesting itself in an apply to each.

---

Try deleting the entire step, including the apply to each and starting that branch again. Once it is converted to an apply to each, it can't be undone. 

---

@Anonymous wrote:

Thanks for the reply Brad.

I've structured the flow into two branches now. Excuse me for being a layman here, but I can't follow the rest of your explanation.

The value 'consultant' gets populated from the 'display name' associated with the 'responder email'. From my understanding I will always need the value 'consultant' since that is my identifier in the list. Then the identifier from Forms will be 'display name'. If those two names line up I want to update the item. 

Sorry for making you walk me to the door!

---

You don't need the unique List identifier in the condition for the create conditional branch, because it would always be no. You can utilize several different definitions or expressions within the condition. 

Looking back at your Flow design, I think you're just going to be in a logic conundrum. I don't see why you need the first apply to each loop. The Get response, Get user, and Get items actions should be stand alone steps, and not in a loop. 

I'd try that, with your original update/create condition like it was, and if that doesn't work I'd then run parallel branches for update and create.  

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-yamao-msft · Accepted solution]

Hi @Anonymous ,

Do you want to update an item it already exists, otherwise, create a new one when a new response is submitted?

You may need to create an array variable to check if the new response already exists or not. Please take the following flow configuration for a reference.

Note: in my scenario, I am using Email. You could just change it to Display name as needed.

Best regards,

Mabel

[Anonymous]

Hi Mabel,

Yes that is exactly what I intend to do. Unfortunately I haven't got time to test your idea untill next week. When I do i'll be sure to let you know how it goes.

In the meanwhile, would you mind explaining to me what the array variable does?

[v-yamao-msft]

Hi @Anonymous ,

Connector Variables has the following actions:

Using Initialize variable, we can declare a variable, specify its type and initial value which can be dynamic or hard coded. Action Set variable assigns a value to an existing variable that you’ve initialized previously. Append to string or Append to array variable actions will create a collection of items.

For your scenario, the action Append to array is used to create a collection of existing items, then use a Condition to check whether the new response exists in the collection or not.

Please check more details about the variables at this blog:

https://veenstra.me.uk/2018/03/19/microsoft-flow-the-hidden-gems-are-you-aware-of-all-of-these/

Best regards,

Mabel

[Anonymous]

Hi Mabel,

Just tested it and it works like a charm - thanks heaps!

Robini

[Anonymous]

Hi Mabel,

There is still one issue with the flow. For each item where the emails don't match-up, a new item is being created in the sharepoint list - leading to a lot of duplication.

I've set-up the flow exactly like the one you set up. The problem with it is that for each time the append to array variable is applied, the condition is either true or false - resulting in creating a new item or updating the one that matches. 

I want the array variable to check the list of all the items for 'email' before an item is created or upduated. If in the whole list the item is found then that one is updated, if not a new item is created. Currently it doesn't check the list before getting into the actions, it checks and applies to each item individually (see pics below).

Hope you're able to help me out here.

Cheers,

Robin

FYI