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()

Parents Reply Children
  • 0 オフライン in reply to AngelaIp

    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.

  • 0 オフライン in reply to Ben Pecot

    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.