Flow fails to Create SharePoint list item if data type date is blanks

[BFlow]

We have a fairly basic Flow which is getting MS Form responses and creating SharePoint list items. The Form has branching it in which results in some questions never being asked/answered. When the Flow creates the item, its failing due to a blank not being accepted in the date columns. Example of error:

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

Any thoughts would be very much appreciated.

[manuelstgomes]

Hi @BFlow 

The issue here is that, even if you define in the Form a Date, Power Automate will always receive a string value. The same for all other field types. You need to convert it to the expected value in your SharePoint list.

I go into more detail here:

https://manueltgomes.com/microsoft/forms/format-submitted-data/

Can you please check if and let me know if you have any questions?

Cheers
Manuel

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[tom_riha · Accepted solution]

Hello @BFlow ,

instead of using the date directly, you should use an expression. You should check if the date contains any value or if it's empty. If it's empty, update 'null' value into the column (to keep it empty); if it's not empty, update the actual value. Currently it's trying to update "" into the date column if the Forms field is empty which is not a valid input.

Power Automate has if(...) expression you can use, it expects 3 parameters:

if(condition, ifTrueValue, ifFalseValue)

Using an actual example from my flow the full expression would look like this:

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

condition: equals(outputs('Get_response_details')?['body/r7bf27a2bed93456ca71c844977a3fd4b'],'')
ifTrueValue: update null into the date column
ifFalseValue: update the actual date from the form

The value outputs(...)?[...] is the dynamic content representing the Forms field.

[BFlow]

Worked perfectly! Thank you tom.

[arinborg]

Hi @tom_riha ,

I have a similar issue as @BFlow . I tried to under the expression given by you but I couldn't get through to it.

Kindly help please.

This is what the questions look like:

  The branching is with Q26.

Whenever I choose no, There is an error similar to the one above except it for Numbers instead of dates.

This is what my flow looks like.

 

Please please help. 😕

[tom_riha]

Hello @arinborg ,

what exactly says the error message?

[arinborg]

Hi @tom_riha 

This is the error that pops up
The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Havethingsimprovedsincethelastsu' is required to be of type 'Number/double'. The runtime value '""' to be converted doesn't have the expected format 'Number/double'.

I think its because it doesnt accept a blank value if someone were to say no instead of yes.

[arinborg]

Hi @tom_riha ,

Apologies for being impatient, This project is due next week and this is the only roadblock left for it. 😅

Thank you again!

[arinborg]

Hi @tom_riha ,

I got this solved, Thanks to your above expression, I tried to understand it myself. 

It's done, Phew

Thanks alot!

[Scunners]

I have an excel sheet and it imports 30% of the list fine. The rest will not import due to string/date error and yet there are No visible differences in row entries. My gut feeling it is an issue between American and U.K. Date formats. The sharepoint list is region locale U.K. The date format I need to use is dd/MM/yyyy my spreadsheet is in this format and I use an expression to get the correct date format. I am sure it is because 13 Oct in U.K. Is 13/10 but in US 10/13 and the months are mis mapped as there isn't a month 13. Can you help? 

[vincetaylor]

@tom_riha 
In your example, What does this value represent " r7bf27a2bed93456ca71c844977a3fd4b"

And how do I obtain this value?

[tom_riha]

Hello @vincetaylor ,

that's the ID of the Forms question, it's the dynamic content representing the question.

[MickBurton]

This is invaluable thankyou.

[GDSI]

Thumb down, as you don't even discuss handling null date values in your blog, which is the problem that this post is addressing.

[GDSI]

For the newbs that might read this 100 years from now...

Only this part is needed:

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

Don't try to add the "condition:" portion that is in the example code. It isn't code, it was just a summation of what's happening. 

Second, you MUST put this directly in the Update Item action. You can't try to make a variable, and save the value to the variable and then use the variable in the Update Item. It doesn't work that way, unfortunately.

[AnnetteM]

Thank you for this post!!!! Short and sweet and the notion that it is placed directly in the update instead of assigning to a variable is KEY!!!