Update multiple rows on sharepoint from different form responses

[yvettePrice]

I have a flow that get's triggered from a SharePoint list item and send a survey that once it is completed needs to take the form response and update the list item that triggered it. I am finding that when multiple list items get created I get this error 

"Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'The template language function 'first' expects its parameter be an array or a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#first for usage details.'."

 when the form is submitted and the flow attempts to validate that the form ID belongs with the list item. 

In my conditions I have first(body('Select_2'))?['responseId'] for the form ID and first(body('Select'))?['ID'] for the list item ID.

[creativeopinion]

Are you able to expand these two actions in your flow and upload a screenshot of it? I'm trying to wrap my head around how your flow works.

Thanks!

[yvettePrice]

The trigger is when a email and name are added to a SharePoint list item, it needs to send an email to the person to go to a form and complete the survey. Once the survey is complete it needs to get the list item and form details and update the line with the survey results. 

[creativeopinion]

Can you confirm which part of the flow is giving you an error? Is it the at the point where the flow is triggered?

You said that you find that when multiple items are created is when you experience the error. 

Can you also explain the purpose of the Select action? Are you retrieving the ID from the trigger action? or from the Send HTTP request to SP action?

[yvettePrice]

The Condition is what is erroring out. I added 2 list (both ended up failing) items to the SharePoint site and everything worked until the condition. The Select action is to get the ID's created the Trigger ID of the SP line item and the Form Response ID.

[creativeopinion]

If you are trying to return the ID from the When an item is created trigger, the Select action isn't necessary. Use a Compose action instead.

I also need a bit more clarity on what's going on in your two apply to each actions. In both these actions, you are filtering through the same dynamic content (List of Responses). Not sure why you need to do this twice. 

Can you upload a screenshot of your form and explain how the responses tie into your Sharepoint list?

[yvettePrice]

I changed the flow with this: Doing compose instead of select calling the ID in the GetVersions from the compose output and adding that to the Get Item in the apply to each 2. I added 4 list items and completed the survey for 3 and they all came back right, but the 4th never sent an email and the flow has stopped triggering on it's own. At this point I have to use the test to trigger the flow. 

My Survey for the test is simple here are the screenshots:

[yvettePrice]

I'm also getting this error when I triggered the flow with a manual test. 

The list item could not be added or updated because duplicate values were found in the following field(s) in the list: [Email].
clientRequestId: 0e03d8e4-be20-4faa-98cb-553dedff55ee
serviceRequestId: 0e03d8e4-be20-4faa-98cb-553dedff55ee

[creativeopinion]

Can you explain the Send HTTP to SP Request (Get Versions action)? What's the reason for this action? Where are you using the outputs from this action?

Are you including the SP ID in the email that is being sent to the user? I am assuming you aren't. 

For this reason, I think you can use the email address to look up the correct item to update. 

After gaining a better understanding of your workflow, you need to split your flow up into two flows. 

1. When a New Item is Added to SP List
   Use this flow to trigger the email. 
2. When a New Response is Submitted
   Use this flow to trigger the update of your SP Items.

[yvettePrice]

To get the correct line item, before this was added I was getting the last result overwrite everything on the list with that value. (ex. line 1 had value A, then line 2 was added and updated line 1 & 2 with value B).

There isn't a way to get the value from the Get actions in the update item section. Here the SP ID and Name are from the Trigger action.

[creativeopinion]

As I suggested in my prev post. I would strongly recommend that you split the flows up into two. 

One to handle when a new item is added to the list, which triggers the email to complete the form. The other flow would handle all the submissions. I'm assuming that you just want to match the responses the user has submitted with the item in the SP list. 

Are the items being manually added to your SP List or is there an automated process? Only reason I'm asking is that the Filter Query requires the email string to match. This means that the email addresses in your SP list should be in lower case. 

The second flow would look something like this:

This flow will take the email address that is filled in and convert it to lower case. Then the Get items action will pull an item from your SP list that matches the email address returned from the form. Keep in mind, this will only work if 1) your SP list is enforcing unique values on the email column and 2) that the email address field value doesn't contain a typo.

Are you using the Title field to store your email address? or is it a custom column. I'm only asking because you will need the internal column name for the Filter Query. You can locate this at the end of the URL of the column.

[yvettePrice]

I tried this and the update item would just overwrite the old value with the new value of any incoming survey. So it never tied it back to the original line item. 

I also got this error

An action failed. No dependent actions succeeded.

[creativeopinion]

You didn't answer me on how you are tying the form responses to a SP item. I was only assuming you are matching it by the email address. 

What are you using in the Get Items action to look up the matching SP item?

Can you confirm if the Get Items action in your previous flow run located the matching SP item?

[yvettePrice · Accepted solution]

Thank you @creativeopinion for all your help, I did manage to use some of the things I learned from you into my working flow. 

I ended up creating 2 SharePoint Lists 1 to enter and track survey actions and 1 to receive the survey results. 

I then created 2 work flows, a simple when item created send survey link

The other to capture the survey results and update/create items on SharePoint.

[yvettePrice]

I was using the email addresses, because the form was public so I needed a unique field to match back to the triggering item.

[creativeopinion]

If the email addresses are unique you should be able to locate the item in SP using that. Like I said, this is only if the user doesn't include any typos in their email address when entering it into the field. 

Did you confirm if the Get Item action would locate the correct SP Item? This part of your flow is redundant. You are already filtering out the SP List items in the Get Items action.

Instead of looping through the results (since there should only be ONE item returned). You should actually add a Condition action to check if any items have returned. 

[yvettePrice]

I created the condition to make sure that the email in the SharePoint list matches the email from the form so that I can validate the survey sent is matched back to the "responded date" in the survey results. The negative condition doesn't come back, so I will try to change it as you suggested above.