Condition - error that result is of type null

[adevereson]

I am setting up a flow triggered by a new survey submitted in Survey123, which adds a row to an Excel online document. There are multiple sheets in the Excel, one for each repeat in the survey. In each submission, only one of the repeats will be filled in, so the other repeats will be null. 

I set it up without any conditions, and got this error: The execution of template action 'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@triggerOutputs()?['body/feature/repeats/Eng_Sanction_Amounts_Yes_start']' is of type 'Null'. The result must be a valid array.

So, I added a condition to each branch to check whether there were null values, and if there were to do nothing, but if there were not null values to add a row to the sheet. 

However, I'm still getting the same error message when I run the flow. In the example survey submission I've used, the error is coming up on the branch for a repeat that has not been filled in (i.e. is null). 

How can I resolve this so that rows are added when there are values in a repeat, and nothing happens when there are no values? 

I have included a picture of the flow, and of the condition set up for the part of the flow that's showing the error (the 'Eng_Sanction_Amount_Answers' is the text field within the repeat)

[annetoal]

Just a guess without seeing the code, but it sounds as if the condition action is throwing the error. If you put the error-trapping code after the action, that would be too late to catch the null.

So, put a Filter action in before the condition. If that does not work, consider using the Coalesce expression in an apply to each loop before the condition. You might find this blog entry interesting:

Coalesce blog 

Anne

[adevereson]

Thank you - I tried replacing the condition with filter array before the apply to each, as if the error is in apply to each I hoped this would resolve it. Now the error is 'The 'from' property value in the 'query' action inputs is of type 'Null'. The value must be an array.', this is for the element that is null in the test survey submitted - picture below. 

This is the filter array I set up: 

[annetoal]

Apologies for not having any experience using survey output in Power Automate, but this error suggests that the output from the survey is not an array. That means you have two choices: 1. turn the output into an array (too complicated), or 2. use another method to trap out the null. Would it work for you to turn the null into something else. Again, the coalesce function might be a help here.

[adevereson]

No worries. I'm a bit of a novice so I saw the coalesce function in a few threads but am not sure where I should put this - within the 'apply to each' itself like this? 

coalesce(triggerBody()?[‘feature’]?[‘repeats’]?[‘Eng_Credit_conditions_repeat_start’])

[annetoal]

First things first--are you outputting an array from the survey?

[adevereson]

I think so, as I think that's the nature of the repeats from Survey123. In the survey, a separate data table is created for a repeat which contains all the questions within that particular repeat. So under one submission and one question, there are multiple rows of data. So I want each one to be added as a new row into the Excel document. 

[annetoal]

Could you please share the raw output (masking any confidential data) from one of your error runs in Power Automate?

[adevereson · Accepted solution]

I managed to find a solution so sharing here in case anyone else needs it. I initialized the data for one repeat as an array, then filtered it to remove null values, and then within an 'apply to each' (applied to the body of the filtered array), added a row into a table. I think the error came from having 'apply to each' before the array had been filtered, and while everything I could find suggested that Survey123 repeats are already arrays, I had to initialize it as an array for the flow to work.