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'))

 

JVieira
Regular Visitor

@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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,161)