cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cflbasser12
New Member

Enter null/blank value into a Date column of a SharePoint list

I have a Form with a few optional date fields. When the form is submitted, it kicks off a Power Automate which takes the form submission, and creates a new record in a SharePoint list. When the optional date fields are not entered, the flow fails with the following error:

 

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

 

I tried the solution presented here, with no luck. I've tried setting variables with various combinations of null and "" with no luck.

 

In my current setup, I hard coded a date of 1/1/1900, which allows the flow to work. But also inserts a bogus date into the List.

 

Any ideas on how to make this work?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
cflbasser12
New Member

I figured out a solution... For future reference…

 

1. Create a variable called ‘Null’ and set it to a string value of ‘Null’
2. In the flow, create a condition that checks the Form value – If blank, set a variable ‘DateVariable’ equal to a string value of ‘Null’; If not blank, set ‘DateVariable’ equal to the Form’s date value
3. Use the following equation on the Create SharePoint Item for the date field:

if(equals(variables(‘DateVariable’),variables(‘Null’)),null,variables(‘DateVariable’))

 

View solution in original post

8 REPLIES 8
cflbasser12
New Member

I figured out a solution... For future reference…

 

1. Create a variable called ‘Null’ and set it to a string value of ‘Null’
2. In the flow, create a condition that checks the Form value – If blank, set a variable ‘DateVariable’ equal to a string value of ‘Null’; If not blank, set ‘DateVariable’ equal to the Form’s date value
3. Use the following equation on the Create SharePoint Item for the date field:

if(equals(variables(‘DateVariable’),variables(‘Null’)),null,variables(‘DateVariable’))

 
rohsh354
Community Support
Community Support

Thank you for sharing the solution. I am sure this can help others facing this issue.

 

 

SC31
Frequent Visitor

This is exactly what I have been trying to achieve.

  1. Forms: when a new response is submitted (choose list etc.)
  2. Initialize variable: Null, String, Null
  3. Then I believe I'm meant to have:
    Set variable: Null (name from dropdown) but not sure what to put for value as it won't allow "null" <-- need assistance here please
  4. Condition <-- here the dynamic content for my date variable is not appearing as an option. Assuming here this should be the name of the form field that contains the date response?
  5. Apply to each: matched all the form fields to the list but for the date one added the if statement from the solution replacing 'datevariable' with the dynamic content of my form and 'Null' with the variable Null

@cflbasser12 Would you mind posting a screenshot of the working flow please?

 

Not sure what went wrong, but I was beating my head on the wall trying to figure out why that statement didnt work.  Well, I did a copy paste.  Single quotes were a different font for some strange reason...

 

Code block keeps it pure.

 

if(equals(variables('ShipDate'),variables('Null')),null,variables('ShipDate'))

 

@cflbasser12 

 

I am having same difficulty SC31 is having. 

 

Can you please posting a screenshot of the flow?

 

Thanks

You are a sanity saver! I have been going round and round with this problem. Thank you!

VarshneyBishu
New Member

@cflbasser12 When i am using the solution provided , it is passing the same value stored in the variable for the last item. so it is updating the same value of last item in excel to the different items created in sharepoint list, any idea what's going wrong with the variable.

I have figured it out what i was doing wrong, thanks

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (4,273)