Vadim Tabakman

The question of Empty Date fields in SharePoint and how to use them in Nintex Workflow seems to come up often.  It's something I probably should have posted about before, but it kept slipping my mind.

Today, I got asked by a user if i could write something about it, since it has the potential of generating headaches for many people.

Problem

You have a SharePoint list or library and it contains a DateTime field that is allowed to be empty.  When items get created, sometimes users fill in the date field and other times they don't.

There are now 3 (thanks Kevin) scenarios that come in to play where a Nintex Workflow has to interact with the empty date field.

1. You want to use a Set a Condition action or a Run If action and perform some business logic if the date field is empty or if it's not.

2. You want to query a list to find items with Empty date (or non empty dates) using a Query List action.

3. Can I update a DateTime field that has a date in it, and empty it out?

Solutions

When using a Set a Condition action or a Run If action and selecting a SharePoint DateTime field, there is no condition clause that checks if the field is null or empty. 

We need to find a way to compare a field to something that would give us the answer we want.  Which is, YES if the datefield is empty and NO if it isn't.

The solution that I believe works well, is the following:

1. Create a DateTime variable

2. Add a Convert Value action to your workflow

3. Configure this action with the Input being - 1/1/0001

4. The output being your DateTime variable.

Convert Value Action

You now have a DateTime variable that you can use in an action like Run If and check if it "equals" the DateTime field in your list item.

In my tests, I had a field called "TestDate".

The Run If configuration is quite simple:

Run If

For the second issue, when you want find all the items with empty date fields, it's actually a little easier.

You don't need the variable we created.  Since we are using a Query List action, you can simply add a filter to say - I only want items where my DateTime field is null.

Query List

I'm sure there are other ways of achieving this result, but this is one that works for me and gets me the data I need.

If you're interested in the CAML query that is generated behind the scenes, it looks like this:

<Query>
  <Lists>
    <List ID="{D0F6F54B-F814-4F5E-B081-479C9C4FDD4E}" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
    <IsNull>
      <FieldRef Name="TestDate"  />
    </IsNull>
  </Where>
</Query>

Thanks to my good friend Kevin, now that you have used the Convert Value to create a DateTime variable that can be treated as an empty date, you can use it to update a DateTime field in your list, using an Update Item action.

Update Item

If you have other ideas for this or have come across an issue with empty datetime fields that the above suggestions don't help, shoot me an email - Contact Me

Downloads

Nintex Workflow 2010 : v2.3.8.0

Nintex Worfkflow : Download Workflow

SharePoint List Template: Download List Template

Posted by Vadim Tabakman Saturday, April 6, 2013 12:23:00 AM Categories: Convert Value Empty Date Nintex Nintex Forms Query List Run If SharePoint
Copyright Vadim Tabakman
Rate this Content 1 Votes

Comments

Saturday, April 6, 2013 7:49:01 AM
Kevin

re: Nintex Workflow - Empty Date

Hi Vadim,

I hadn't thought of setting the variable to the empty value, but rather what I have always done is used the build a string action and stored the date value as a string and called it TestDateText.  Then used the set condition or run if action to run if the TestDateText variable is empty.

Can you use the empty date value that you've created to update a list item to empty out the date value?

Great post!

Saturday, April 6, 2013 8:18:32 AM

re: Nintex Workflow - Empty Date

Hi Kevin,

yes, you definately can use the dateEmpty variable in an Update Item action to empty out a DateTime field in your SharePoint List.

cheers,

Vadim

Monday, April 8, 2013 9:04:50 AM
R. Stephen Gracey

re: Nintex Workflow - Empty Date

Thank you, Vadim. It's working for me, and I'm going to make sure that there's a link to your solution back in that thread. You've perhaps already done that, but nevertheless...


Stephen

Monday, April 8, 2013 9:27:24 AM

re: Nintex Workflow - Empty Date

Hi Stephen,

Glad this helped you Smile.  Hopefully it helps others.

cheers,

Vadim

Monday, April 8, 2013 2:21:51 PM
R. Stephen Gracey

re: Nintex Workflow - Empty Date

Actually, it WASN'T working for me. There's a critical part that needs to be added to your post:

You have to use "Calculate Date" action to load the item's date field into a date variable in order for an empty field to look like "1/1/0001," and thereby matching.

If you use the emptyDate to compare against the item's date field, it won't work because the field is actually empty.

And if you use the "Query List" action to load the field's contents into a date field, you get 1/1/1900.


So you MUST use "Calculate Date" to get a variable against which you can compare your specially-formulated emptyDate variable.

Please confirm.

Stephen

 

Monday, April 8, 2013 2:44:29 PM

re: Nintex Workflow - Empty Date

Hi Stephen,

I didn't need to use a Calculate a Date action at all.  I've updated this post to include a SharePoint 2010 List Template and a Nintex Workflow that tested out some of the functionality.

I hope it helps.

cheers,

Vadim

Friday, August 23, 2013 10:20:22 AM
Bob Eldredge

re: Nintex Workflow - Empty Date

HOORAY AGAIN VADIM!

Thursday, January 16, 2014 10:30:04 AM

re: Nintex Workflow - Empty Date

Thanks Vadim for the trick on how to clear a date field.  I was surprised at hard it was to find a solution for this and that it wasn't readily available with the out of the box Nintex functions.  One thing I have found is that although updating single records (i.e. using the "Update item" function) with the emptyDate field works great, it doesn't seem to work if I try update a collection if items using the "Update multiple items".  It doesn't return any errors, it simply just doesn't clear out the date fields and leaves them as is.  Any solution or comment on this discovery?

Thanks,

Jonathan

Thursday, January 16, 2014 10:51:11 AM

re: Nintex Workflow - Empty Date

Just a follow up that I forgot to mention that as a work around for this I use a collection variable to collect all the list item ID's for the list and then use the "for each" function to loop through each item ID individually and use the "Update item" function to clear out the date on each record.  This is not ideal as it is much slower than using the "Update multiple items" function but as I said, that function is not working as expected for clearing out the date field values.

Thanks,

Jonathan

Thursday, March 20, 2014 11:10:35 AM
Cameron
Gravatar

re: Nintex Workflow - Empty Date

Vadim,

Thank you for posting this elegant solution. Nintex's official forums had some rather convoluted explanations that assumed the end user (me) could fill in gaps on my own. I appreciate you walking me through the solution. So far this has worked but I will be sure to comment back if I encounter an error. :)

Thanks,

Cameron

Thursday, March 20, 2014 11:54:02 AM

re: Nintex Workflow - Empty Date

Hi Cameron,

I'm glad this could help you.  Let me know if there's anything else you're not sure about and I can see if I can put something together.

cheers,

Vadim

Wednesday, June 4, 2014 1:18:16 PM
Christoph Muelder
Gravatar

re: Nintex Workflow - Empty Date

Hi Vadim,

I try to use the DateEmpty variable to set a date field to empty within a create list item action. It does not work. I get "element column requires a different information type" (translated). Any idea?

Thanks for help.

Regards

Christoph

Friday, June 6, 2014 7:33:40 AM

re: Nintex Workflow - Empty Date

Hi Christoph,

what version of Nintex Workflow are you using?

Vadim

Monday, June 23, 2014 10:09:21 AM
Karen Wilson
Gravatar

re: Nintex Workflow - Empty Date

Hi Vadim,

I get the same as Christoph.  I am using nintex workflow 2010 Version: 2.3.9.0

Thanks,

Karen...

Monday, June 23, 2014 11:00:25 AM

re: Nintex Workflow - Empty Date

Hi Karen,

did you try the workflow and list template on this post and see if it works there?

I just tried it with 2.3.10.0 and it still seems to work fine for me.

cheers,

Vadim

Tuesday, June 24, 2014 4:20:57 AM
Karen Wilson
Gravatar

re: Nintex Workflow - Empty Date

Hi Vadim,

I worked out the issue.  The update functionality is in a seperate webservice.  I pass the date into this from different locations.  Sometimes i have to pass the null date - converted like above from 1/1/0001.  However when this passes into the update webservice, the date gets modified to 1/1/1900.  And this is what is attempting to be put into the list field.  Why would teh webservice call be changing the date?  Is there a wrokaround for this?

Tuesday, June 24, 2014 4:22:01 AM
Karen Wilson
Gravatar

re: Nintex Workflow - Empty Date

sorry, seperate worflow that i call using a webservice

Tuesday, June 24, 2014 7:07:09 AM

re: Nintex Workflow - Empty Date

Hi Karen,

if you're passing it to a web service, then I'm afraid I don't know why that is happening.

It would be the web service that is changing it, and not the Call Web Service action.

cheers,

Vadim

Thursday, July 10, 2014 3:38:22 AM
Manikandan.net
Gravatar

re: Nintex Workflow - Empty Date

It works for comparison. 

I am not using update item. I wanted to create list item.

As it works for comparison, i have created nasty nested if conditions to NOT to supply values for date time field while creating new item. But still it is 1/1/1900 after creation.... :(

Is there a solution creating item with empty date values? I really dont to call update item, after creation.

Regards,
Mani 

Wednesday, August 13, 2014 9:55:36 AM
Paul Diller
Gravatar

re: Nintex Workflow - Empty Date

I was also able to just set dateEmpty by just using the default value.  Did not need to use the convert action.

Sunday, August 17, 2014 8:45:35 AM

re: Nintex Workflow - Empty Date

Cool Paul.  Thanks for letting me know.

cheers,

Vadim

Monday, September 22, 2014 11:05:48 AM
Jim Andrew
Gravatar

re: Nintex Workflow - Empty Date

This looks like a great solution to help resolve an issue that I am having.  Here is my scenario:

First of all using Nintex Workflow 2007

1. An intake form is created
2. We want to add a date into the Assigned Date value
3. I want to automatically set the Assigned Date value only if it is blank

When I am running the Run if they are not matching.  Any thoughts?

Tuesday, May 12, 2015 2:24:31 AM
Dagmar
Gravatar

re: Nintex Workflow - Empty Date

This doesn't work in the newest Nintex Version 3.1.2

I Need to clear 39 date fields after the workflow run to give them the possibility to fill out and start again.

the only Thing that happens is, that SharePoint set all date to 01.01.1900

Tuesday, December 22, 2015 10:51:16 PM
Shelly
Gravatar

re: Nintex Workflow - Empty Date

What about empty number variable? is there a way to check that?

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register

Statistics

  • Entries (279)
  • Comments (1769)

Categories