Sunday, January 20, 2008

Checking for a NULL Date Field in SharePoint Designer Workflow

I was recently teaching an Advanced SharePoint class in Richardson, TX and a student was expressing his frustration over Microsoft's apparent oversight within the SharePoint Designer workflow wizard. One of the "IF" conditions is a simple testing of the value of a field…"If this equals that." He wanted to test a date field in SharePoint to see if it was empty or not. Problem is, when you select a field that is typed as DATE in SharePoint Designer, there is no test for the empty or null condition. You can see this in the first figure.

It occurred to me that we might be able to change the type of the field to a string and then be able to perform the comparison. I used the "Build Dynamic String" action.

I selected the End Date custom field on my Expense Statements list and stored it in a new variable that was typed as STRING.

The results of this were not exactly what I expected, however I was able to determine, through trial and error (sending the values in an email), that the conversion created a string that was filled with question marks (????). Therefore, if you simply test to see if the new string begins with a ?, you can successfully test for an empty date value.

Both my student and I were happy with the results. There may be other ways to do this (perhaps by testing if the value of the date is equal to the "beginning of time" date), but this certainly seems like an easy and foolproof method.


James Hammonds said...

I ran into a similar issue with date fields and numeric values (the same rules seem to apply for numerics). I ended up creating workflow string variables and setting the values of those string variables to the values (or lack thereof) of my numeric & date fields. Then I could test to see if those fields were empty b/c they were strings.

Anonymous said...

I used a similar solution which I gleaned from somewhere on the 'net. I created a string workflow variable, set it's value to the value of the date field, and then checked the string variable for empty. Worked like a charm.

Now I'd like to find a way to set a non-required date field to empty in a workflow.

Anonymous said...

thanks for this! this worked to help me with a similar situation. Needed to test for Null date in my workflow, had three dates for followup emails, followup 1 is required and followups 2 and 3 are not required. I needed a way to stop the WF from running and your post shed light and helped!

Anonymous said...

Very helpful, thanks! Sharepoint is 40% skill, 60% bodging I find.

B1ju said...


I have a list which has a start date columns. During the ItemDeleting event i need to check whether the start date is empty or not.

I tried checking like

if (contextItem["Start Date"].ToString() != string.Empty)
// current_StartDate = (DateTime)contextItem["Start Date"];

But it doesnt seem to work :(

Could anyone please help me with this?

Eric Sammann said...

Ray Williams, you said that you would like to find a way to set a non-required date field to empty. I have done this by having two different Update List Item actions. The first one must meet certain conditions to determine that the date should not be there, and then the Update List Item action includes the date field, but I do not assign anything to it. The second set of conditions is for when the date column should not be blank. The Update List Item action includes the date field and also includes a value for the date field.

This sure could have been made easier though!

Unknown said...

I've tried the solution in this post - it didn't work for me. But Ray's solution in the comments worked!
Ray Williams, you have NO idea how long it took me to find this solution. Works perfectly! Thank you!

Anonymous said...

Thank you, Eric! Your Update List Item worked perfectly to set my date field to null.

Unknown said...

To test for an empty date, I compared it against itself, ie:

"is equal to"

If the date is 'null' this will return false, as 'null' does not equal 'null'.

Unknown said...

For some reason I wasn't able to get this to work, but I did find you can get the same result by creating a calculated column and setting it to capture your date field as a single line of text, then doing your workflow against that field.

John M. said...

We have an office in Richardson.. what share point training did you get to attend? I could make an excuse to visit our Tx office for just such an event.

Ricky Spears said...

John - We have switched our Dallas area classes to a different facility that is about 12-miles away and closer to Dallas. The class Russell mentioned in this post was "Applying SharePoint 2007 - Advanced Features" which we no longer teach publicly. We do offer a variety of SharePoint classes in our Dallas location each month. You see the upcoming classes on this page:

If you're interested in SharePoint Designer workflows (what this blog post was about), you'll probably be interested in our "InfoPath 2010 and SharePoint Server 2010 No-Code Workflow Deep Dive" class. Here is information on that class:

WillW said...

I used the approach with converting date column value to a workflow variable (string) in SharePoint 2010 workflow. For the empty date column the string is not empty, but "1/01/0001 12:00:00 AM"