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

(1) Share

Report

Posted on by diegoandre10

517

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!

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
diegoandre10 517 on at

Like (0)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No
diegoandre10 517 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Verified answer

eric-cheng 5,171 on at

Like (2)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
diegoandre10 517 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
diegoandre10 517 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
eric-cheng 5,171 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
diegoandre10 517 on at

Like (0)

Report
Copy link

Link copied!

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 !!

Was this reply helpful? Yes No
elaineco1088 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No