Date field throwing error if blank - Power Automate

[amiles]

Hello 

I have a Flow that creates a SharePoint list item after a MS Form is submitted. The field is an "end date". If the end date is blank, I receive an error that "Input parameter 'item/EndDate' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'."

Any suggestions as to how I can create an expression that will ignore the field if it is left blank? I have tried to change the list item field to single line text, but that still doesn't seem to work.

Thanks so much,

Abbey

[v-LilyW-msft · Accepted solution]

Hi @amiles 

Instead of using the date directly, you should use an expression.If 'End Date' is empty, update 'null' value into the column (to keep it empty); if it's not empty, update the actual value.

Expression:

if(equals(outputs('Get_response_details')?['body/ra66dc3ce64ec4c63af091d4ced99b95c'],''),null,outputs('Get_response_details')?['body/ra66dc3ce64ec4c63af091d4ced99b95c'])

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amiles1]

@v-LilyW-msft  - thank you so much for your quick response! When entering that expression, I receive an error per below:

Am I missing something?

Take care,

Abbey

[v-LilyW-msft]

Hi @amiles1 

From your screenshot, there is a missing ‘(’ between if and equal, Please add '(' and try again.

Best Regards

[amiles1]

thank you again for the quick reply. I have updated the expression and I still receive this error:

Any additional troubleshooting advice?

Thanks,

Abbey

[v-LilyW-msft]

Hi @amiles1 

I don't see any problem in your formula.

Please add a Compose action and select End Date in it before Create Item action.

Then copy the Expression and try again:

if(equals(outputs('Compose'),''),null,outputs('Compose'))

Best Regards.

[MrOrioye]

Thanks Thanks Thanks !!! I had the same problem. This resolved it like a charm. 

[BrunoQR]

Hi

I used the expression and it worked pretty well!

However, when I enter a date in the form, the date isn't entered into the respective SharePoint list column, i.e. the form submission is added to the SharePoint list, though the date is left blank. Any help would be appreciated! 

[tinkerbellpixie]

@v-LilyW-msft Do you mind explaining what each section of the code is supposed to do? I have a similar issue as the OP so I'm trying to replicate your solution. Specially, I don't want what to put for 

('Get_response_details')?[body/ra66dc3ce64ec4c63af091d4ced99b95c'],' ') 

if(equals(outputs('Get_response_details')?['body/ra66dc3ce64ec4c63af091d4ced99b95c'],''),null,outputs('Get_response_details')?['body/ra66dc3ce64ec4c63af091d4ced99b95c'])

 Thank you so much for your time! 

[CHQLDHealth]

i know this is an older topic but seems to be still current. I am bringing in Excel data to Power Automate as "List Rows Present in a table" , my date field "Date results recommended" is set as a date field. I am trying to "Create item" in sharepoint list but continue to get the same error:

OpenApiOperationParameterTypeConversionFailed

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DateofRecommendations' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'.

For this field I have tried:

creating an Compose step where the value is the date field from the data source and then:

if(equals(outputs('Compose'),''),null,outputs('Compose')) in the create item value for that colum.

I continue to get the same error! Could I please have some help on this. Thanks.

my flow looks like this:

[JeffHallett]

So I ran into this exact problem and came across this thread, tried it all, and got the same error.  I almost wonder if Microsoft changed a data conversion behind the scenes so a NULL is getting reconverted to an empty string (""). The solution that has worked for me is to put the Compose ahead of a Condition.  I evaluate the length of the datetime field from the Compose - if zero I have a Create Item that does not try to set the time column in my List and if not zero, the Create Item does try to set the time column.  It's very repetitive, but it works reliably.

[nicolashone]

So helpful thank you.

[jyounce]

I believe you are correct.  Using Null in fx to clear out a date type field works fine in one flow, but exact same list, exact same field, different flow and it doesn't work - gives me the runtime error.  I am actually trying to make the date null if it has a date in it.  So no solution yet.

[harnishdl]

I had the same issue trying to use the suggested Compose function. I resolved this issue by using a nested if statement as follows: 

if(equals(items('Apply_to_each')?['Quote Last Update Date'],''),null,if(equals(items('Apply_to_each')?['Quote Last Update Date'],null),null,items('Apply_to_each')?['Quote Last Update Date']))

If the date is blank it sets it to null, then it does a second check to see if it is null which leaves it as null.

[dgPowerApps]

I can put null as the value for the date field directly, always, and it comes back with the following error:

OpenApiOperationParameterTypeConversionFailed

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/CLEAN_Date_Completed' is required to be of type 'String/date-time'. The runtime value '""' to be converted doesn't have the expected format 'String/date-time'.

My SharePoint list field is formated as the following:

I am stuck =(

[CHQLDHealth]

thanks for that JeffHallett, I will give this a try also as my problem still exists. Another thing that works id changing the date/Time destination field in SP List to Text, but that just means filtering options for that column are no longer possible, but it will accept the blank entry. 

Thanks for the comments.

[herschel4Iowa]

Agree, I followed this post and 3 others and it still is giving me this same error.  I am about to just add another condition AFTER the create item if End Date is not null, update item with End Date.  I am also using the new Power Automate, so maybe I should revert to the old and save...

[coolboy45]

I think you are right! I have the same problem. If the result of the expression is null, it will be converted somehow to a blank string like """". The solution is add a if condition followed by two actions, one with and one without changing the date field. This works. But it is stupid.

[HansenvomDeich]

I have the Same issue now ... dont know why , only in Update an Item Action, in Create Item it is working, whats your solution?

EDIT:

Its a bug in the New Designer.
I have deleted all my Date Fields > Save > create new in Old Designer > Flow is running

... Awesome ... 2 hours failure search and big Grey field in the Hairstyle, for nothing.

[dgPowerApps]

Oh wow! you found the solution ...too late for me as I entered in a dummy date of 1900 when origin migrated data was null, and handled after the fact. If I face this again, I'll retry in classic.