Form response to Sharepoint to Email, using multi-select fields that might have null values

[bmisanin]

I thought it would be worthwhile to post this up, since I had to compile sources from many other forums, but couldn't quite get to the solution I was looking for. So for anybody else out there that's using a similar scenario, this is for you!

Background: My flow is meant to record MS form responses, create a SharePoint list item, and then email the responses to select individuals. One section of the Form is multi-select, but does NOT require an answer to be selected for the user to move on. Therefore, I had to make sure that my corresponding choice field in SP could accept a null value, and that formatting the email also accepted a null value.  I did quite the workaround.

For each multi-select question on the form, I had to have the flow recognize whether it had actual values, or if the value was null. Since it's composing a string, I couldn't change the null value to "None", so I did a work around to create an array only when there's null values.  Each multi-select gets the following:

1. Compose

This uses the forms response data for the specific multi-select field.

outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6']

2. Compose 2

This looks at the first compose, and verifies if it's empty, then null values, otherwise get the output for the forms data.

if(empty(outputs('Compose')), null, outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6'])

3. Select (this will be inserted into the "create sharepoint item" field that is appropriate.

The select has to verify if the forms response is NOT empty. If it has selected value(s), then JSON the forms response. Otherwise, create an array for the null value so that SP will recognize that it doesn't need to fill the field.

** the Sharepoint column was a CHOICE column, that does NOT require the column to contain info, unique values are NOT required, multiple selections and "fill-in" options were enabled , and no default option is selected.**

From: if(equals(not(empty(outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6'])), true), json(outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6']), createarray(null))
Map: Value | Item()

4. Join (this is used to insert into the email for nice formatting)

Again, the function needed to verify that the forms response was NOT empty, and then to JSON the output of compose 2. If false, then again you need to create an array for the null value. This allows the email format to not be between brackets, and the </br></br> joiner spaces out each individual choice.

From: if(equals(not(empty(outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6'])), true), json(outputs('Compose_2')), createarray(null))
Join with: </br></br>

This is what the "Create SP Item" will look like (again, using the SELECT outputs):

Here's what the email formatting ended up looking like, allowing for empty fields, and spacing out multi-selections.

[Pstork1]

It might have been easier to just put each answer into a Coalesce() function.  Coalesce can be used to evaluate multiple values from left to right and it will return the first Non-Null value.  Something like this.

Coalesce(outputs('Get_response_details')?['body/r64ffa8938e0a4d9088c9dff19955f3f6'],'No Answer Given')

If the answer is null then the placeholder answer of 'No Answer Given' will be used.

[bmisanin]

Coalesce didn't work for me when I tried it initially. I don't remember what the error was, but the flow kept failing. I also preferred the field to be left blank both on the SP list and on the email if no selection was made. Rather than wording.

I can go in and try the coalesce again, to see what the original issue was and report back.

[Pstork1]

Coalesce will work with the target as a blank string too, if you don't want to use a placeholder.  One issue would be if the field IS actually a blank string instead of a null.  But you said the issue was a null field, not a blank one.  Let me know what error you are getting and I'll try to resolve the issue for you.

[bmisanin]

Coalesce worked for multi-selections, and single selections in my choice column, and even for my email (select and join data operations, respectively).  However, the flow failed when no selections were made:

Here's the function I used:

coalesce(json(outputs('Get_response_details')?['body/r074ca83344824548bb3fddf36f19261a']))

Here's the error:

 

[bmisanin · Accepted solution]

I adjusted the function:

coalesce(json(outputs('Get_response_details')?['body/r074ca83344824548bb3fddf36f19261a']), createarray(null))

This solved the null value issue and still works correctly when single or multiple selections are made.