Forum Discussion

Ben_Pecot's avatar
Ben_Pecot
Ideator I
4 years ago

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

I want to only have it pull a specific relationship (Part AML) if a custom date field "Valid To" is greater than the current date.  I am able to create the Where that compares it against a specific date, but this query definition is pulled by another process automatically and I don't want to have to update the Query Definition every day with the current date for whoever ends up running it that day.

I currently have:

[Valid to] > '2021-11-29T00:00:00'

I want something like (this doesn't work)

[Valid to] > current_date()

Query Builder
Query Definitions
SQL

5 Replies

  • AngelaIp's avatar
    AngelaIp
    Ideator I
    4 years ago

    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

    • Ben_Pecot's avatar
      Ben_Pecot
      Ideator I
      4 years ago

      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.

      • Ben_Pecot's avatar
        Ben_Pecot
        Ideator I
        4 years ago

        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.

  • AngelaIp's avatar
    AngelaIp
    Ideator I
    4 years ago

    Hi Ben,

    thanks for sharing your test results!

    After checking the sample I am not sure if search_container.js is actually used by the QueryBuilder at all. If you set a debugger breakpoint, will it be triggered by the Query?

    In worst case we have to customize some file in this folder:  ...\Client\Modules\aras.innovator.QueryBuilder

    If you have a subscription maybe ask Aras for this feature. It would definitely be a useful enhancement!

  • Ben_Pecot's avatar
    Ben_Pecot
    Ideator I
    3 years ago

    I was not able to find a more elegant dynamic solution so instead I have a method that updates the filter on the query definition and added that method to the scheduler.