How do I set a where statement on a Query Definition against the current date the query is run?

Hi Ben,

I haven´t needed this use case yet, but it sounds definitely useful. By default Innovator dcannot search for relative dates like "now" or "yesterday".

But this project provided a patch to add this search option with help of parameters you can use inside your search:

https://community.aras.com/i/projects/advanced-search-options---relative-date-searches

I see a high change it can work in a Query Definition, cause basically Query Definition just build an AML query.

Would be happy to hear your test results!!

Angela

Hi Ben,

I haven´t needed this use case yet, but it sounds definitely useful. By default Innovator dcannot search for relative dates like "now" or "yesterday".

But this project provided a patch to add this search option with help of parameters you can use inside your search:

https://community.aras.com/i/projects/advanced-search-options---relative-date-searches

I see a high change it can work in a Query Definition, cause basically Query Definition just build an AML query.

Would be happy to hear your test results!!

Angela

I have had a chance to test this.  It does not appear to work. "String was not recognized as a valid DateTime."

It does not recognize @now as a valid value in the Where Condition Builder so I had to save it like: [Valid To] > '@now'

That translates to this on the qry_QueryItem:

<![CDATA[<condition>
  <gt>
    <property name="cub_valid_to" />
    <constant>@now</constant>
  </gt>
</condition>]]>

I did a test with a valid date in the format of [Valid To] > ' 2022-06-15T14:57:17' and it worked correctly and saves it on the qry_QueryItem the same way with the date as the constant, but it requires the date to be static.

I used AML to inject  the following code onto the qry_QueryItem:

<![CDATA[<condition>
  <gt>
    <property name="cub_valid_to" />
    @now
  </gt>
</condition>]]>

It says that text is not accepted and the only valid options are property, constant, count, max, min, any, and all.  None of those tags work with the dynamic dates that were provided in that fix.