Flow failing from Microsoft Forms to Sharepoint List when field is empty

[Anonymous]

I have a Form populating a Sharepoint List. One of the questions is conditional and therefore there can be a blank field. This is causing the flow to field as it is expecting a value in the field. How do I get the flow to work regardless of whether a field is blank or not?

[annajhaveri]

@Anonymous are you using Update Item action which is creating the issue? Is the field set as required field in SharePoint list? 

[Anonymous]

I am using Create Item (see screenshot), the optional field is the Date Work Scheduled To field; and I have checked that the Sharepoint column is not set as required

[Pstork1]

You can use the Coalesce() function in place of the field alone.  The coalesce function takes two or more parameters which it evaluates from left to right until it finds a parameter that isn't null.  So using Coalesce with the field in the first position and a default value in the second it will supply the field if its not null and the second parameter if it is.

coalesce(field name, "test value")

[Anonymous]

So would the test value be something like 0 or n/a? I can add anything in to that to highlight it on my sharepoint list as not having a date value? 

[Anonymous]

When I try the coalesce and add in the field:

coalesce(outputs('Get_response_details')?['body/r14e2b56b70f14438909574fbc1d5fbbb'],"0")

I have used 0 as the test value and I get an 'expression is invalid' message. Help..

[Pstork1]

The test value can be whatever you want, other than null, that can be used in your action.  In this case it would need to be a valid Date value for some default date.  I normally use either a date far in the future or in the past.  But you can't input a null date.

[Pstork1]

Since you are using it to fill in a date field in needs to be a valid date.  Instead of "0' try something like "1900-01-01T00:00:00"

[Anonymous]

Hi, tried the above but still getting an invalid expression message. I wonder if the date field has a format that is causing the issue - how would I check this?

[Anonymous]

If I use '   ' around the date then it accepts the expression as valid, but still get the same error. 

[Pstork1]

Can you provide a screenshot of the error?  And show me where you are using the formula?

[Hardesh15 · Accepted solution]

@Anonymous  If you use coalesce function for blank date check it will not work. Reason- it always return null value in form of string "" which is causing your create item action to fail. Rather than using this use below expression directly in Date field-
if(empty(outputs('Get_response_details')?['body/r8f6326eb3bd9449c8dc8b14222e7155f']),null,outputs('Get_response_details')?['body/r8f6326eb3bd9449c8dc8b14222e7155f'])

It will pass directly null value to Date field.

To test this- Use one compose action and pass Coalesce expression in compose and check raw output it will show you output in double quotes. Which is causing issue in date field.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh

[Anonymous]

Thank you so much! Fixed it 🙂