cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
gwpeters
Nintex Newbie

Date filter in query list action not working

Jump to solution

I am doing a list query and using the filter within that action to narrow down my results. I am trying to filter out anything with a date that has not occurred yet. So I use the filter and select my date field I want to look at then I have tried to use but i don't get any results when I try using greater than or less than, but i do when I use equals or not equals. Are you not able to use greater than or less than with a Date Field? If so what is another way I can filter these to get all events that have happened in the past?  

Gets no results

0 Kudos
Reply
9 Replies
Nintex Employee
Nintex Employee

Re: Date filter in query list action not working

Jump to solution

What "Date and Time Format" setting does the End Time column have? Try using the "Log in workflow history" action to write the End Time and Current Date values to the Workflow History.  Then review and compare the values to make sure they are in the same format to compare.

This post on date and time strings might also be helpful: https://community.nintex.com/community/build-your-own/blog/2015/08/11/date-and-time-format-strings-q...

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: Date filter in query list action not working

Jump to solution

When I log the Dates this is what comes back.

Looks like the filter should work with those parameters but it still returns no results.  Past Event Date = End Date 

0 Kudos
Reply
Automation Master
Automation Master

Re: Date filter in query list action not working

Jump to solution

do you experience the problem just with 'run now' test or at runtime as well?

with run  now - have you replaced variable references within CAML with a sample/real values?

 the most reliable way how to work with dates in CAML is to supply date as a ISO formatted string:

YYYY-MM-DDTHH:MISmiley FrustratedS

supplying date field/variables may or may not work, depends on overall environment settings.

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: Date filter in query list action not working

Jump to solution

I am trying it with the "Run Now" feature. When I use run now and use equals or does not equals parameter I get results returned so why would that not be the same as less than or greater than. I'm just really confused by this and why it does not work from all the posts I have read about people doing this it just works. 

0 Kudos
Reply
Automation Master
Automation Master

Re: Date filter in query list action not working

Jump to solution

may I see how does filter/where part of your CAML look like?

I'd once again stressed out my former question

with run  now - have you replaced variable references within CAML with a sample/real values?

When I use run now and use equals or does not equals parameter I get results returned so why would that not be the same as less than or greater than

if you didn't replace variable references with sample values you get 'unexpected' results

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: Date filter in query list action not working

Jump to solution

Here is the CAML query when I use "Less than" and get no results

<Query>
<Lists>
<List ID="{CACD266D-35EA-4E83-8282-7910F2A7C2D6}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Lt>
<FieldRef Name="End_x0020_Time" />
<Value Type="DateTime">{Common:CurrentDate}</Value>
</Lt>
</Where>
</Query>

Here is the query when i use "not equal to"  and get lots of results 

<Query>
<Lists>
<List ID="{CACD266D-35EA-4E83-8282-7910F2A7C2D6}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Neq>
<FieldRef Name="End_x0020_Time" />
<Value Type="DateTime">{Common:CurrentDate}</Value>
</Neq>
</Where>
</Query>

I don't know much about how CAML works so i could very well be missing something but from all the posts I have read this should just work the way I am trying to do it. 

0 Kudos
Reply
Nintex Employee
Nintex Employee

Re: Date filter in query list action not working

Jump to solution

Here's what I have for my setup so that it returns values.

List Setup

I created a list column named EndDateTime with settings of "Date and Time" for type, Date and Time Format = "Date & Time", and Display Format = Standard.  See image below.

Nintex Workflow - Past Date Validation 1

Workflow Setup

Added a Query List action and configured it with the settings in the screenshot below.  Make sure to use a Collection workflow variable for the results.

Nintex Workflow - Past Date Validation 2

Run Now and CAML query

The Run Now function will not return any values if you leave it as is.  The reason is the CAML query is using the workflow reference ({Common:CurrentDate}) in the query for the current date.  For it to work, you would need replace that workflow reference with the date/time string properly formatted (2019-01-18T12:00:00).  Below is the CAML query that worked for me.

<Query>
  <Lists>
    <List ID="{AAAAAAAA-7562-4D3B-84F2-5D9A0377105A}" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
    <Lt>
      <FieldRef Name="EndDateTime"  />
      <Value Type="DateTime">2019-01-18T12:00:00</Value>
    </Lt>
  </Where>
</Query>

Nintex Workflow - Past Date Validation 3

0 Kudos
Reply
Automation Master
Automation Master

Re: Date filter in query list action not working

Jump to solution

this is what I'm talking about

with Run now test you have to replace variable reference with a real value. otherwise the string referring to a variable is taken literally and so comparing it to a date doesn't make too much sense.

to compare dates, the most reliable way is to supply them as a ISO formatted string: eg. 2019-01-21T15:50:00Z

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: Date filter in query list action not working

Jump to solution

It looks like this was working I just did not have the correct values entered for the run now test with the guidance from Marian and Eric i have this working the way that I want. Thanks for your time and patience explaining how CAML works and how to use it to test. 

Reply