Nintex Workflow – Empty Date

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.

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:

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.

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.

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