Flow cannot convert an empty value into a URL column of sharepoint list

[diegoandre10]

Hi all

I have a URL column type in my sharepoint list. which takes the responses from a MSFT Forms.

The Forms has the URL answer as optional, therefore it can be submitted empty.

The flow unfortunately cannot capture the empty value and paste in the SP list, it gives the following error:

The flow has the field like this:

How can I edit the flow without having to change my SP column to text?

Thanks!

[eric-cheng]

Hi @diegoandre10 ,

Please check this earlier post out.  It is for update but should apply for create.

if(not(empty(variables('your variable name'))),variables('your variable name'),null)

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

[diegoandre10]

Hi @eric-cheng 

Thanks for your response. I got an error 😞

I entered your code with my variable like this:

if(not(empty(variables(outputs('Get_response_details')?['body/rd1e00a18e6c44bdab735552fe76bca21']))),variables(outputs('Get_response_details')?['body/rd1e00a18e6c44bdab735552fe76bca21']),null)

Do you see any issue?

Thanks!

[diegoandre10]

It looks blurry the error, it says:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The template action 'Create_item' at line '1' and column '2133' is not valid: "The template function 'outputs' is not expected at this location.".'.'.

Updating action 'Get items' to use OData filter queries can improve the performance of your flow. Learn more: <https://flow.microsoft.com/blog/advanced-flow-of-the-week-filtering-with-odata>

Thanks.

[eric-cheng · Accepted solution]

Hi @diegoandre10 ,

The formula was from the linked articlewhich references a variable.  If you dont have a variable, specify your output in place of variable which I have marked with xxxx below.

if(not(empty( xxxx )), xxxx ,null)

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

[diegoandre10]

Hi @eric-cheng 

Thanks a lot. It worked for nulls. However I still have one problem. The person could enter on the Forms a URL without the proper format, and therefore the flow will fail and the response will not be saved. 

Will it be possible to tell the flow to accept ANY string and not just the URL format or nulls?

Thanks!

[diegoandre10]

Or the other one could be, if it's not in URL format, convert it to null something like that. To "catch" the error and avoid the flow failing. 

[eric-cheng]

Hi @diegoandre10 ,

The ideal way is to use regular expressions but Power Automate does not support this so you will need to look at workarounds.  

1.  Include a AND in the formula to check to see if the string contains http:// or https://.  Sharepoint seems to accept any string that is prefixed.  You will still lose valid URLs submitted by the user if they typed www.google.com etc.  You can manage that in your flow but its getting messy.  Too bad there isnt URL validation in Forms.

2. Build an API/Azure Function to check and format the URL

3. Not use a hyperlink field

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

[diegoandre10]

Agree completely. Too bad Forms does not have the URL format, would make everything easier.

I think I will go to option 3, people can be very creative when it comes to free text and would be very likely to miss some responses.

Appreciate your help @eric-cheng !!

[elaineco1088]

I keep getting this error as well can I see the final code expression?