Question

FSM6u24: How to Hierarchy select with Variable Date Constraint?

  • 26 April 2024
  • 4 replies
  • 22 views
S
Rank
Badge +1

All,

 

Is there a way to Hierarchy select with a variable date? 

Static dates work fine, but I cannot figure out how to put a variable date in a hierarchy select

I tried a variety of expressions & variables without luck, such as: 

  • Today()
  • @expression[Today()]
  • @expression[Now()]
  • another dttm column (such as task.plan_end_dttm)

 


This works as intended, with a static date value:

<hierarchy_select>
  <attrs>
    <attr>task.task_id</attr>
  </attrs>
  <primary_table>task</primary_table>
  <from>
    <table>task</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>task.plan_start_dttm</left_operand>
        <operator>gt</operator>
        <right_operand>4/26/2024</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>

 

This is what I’m trying to accomplish, which I cannot get to work

Sample 1 (variable)

    <hierarchy_select>
        <attrs>
            <attr>task.task_id</attr>
        </attrs>
        <primary_table>task</primary_table>
        <from>
            <table>task</table>
        </from>
        <where>
            <data_constraint>
                <constraint>
                    <left_operand>task.plan_start_dttm</left_operand>
                    <operator>gt</operator>
                    <right_operand>@expression[Today()]</right_operand>
                </constraint>
            </data_constraint>
        </where>
    </hierarchy_select>

 sample 2 (from dttm field)

<hierarchy_select>
        <attrs>
            <attr>task.task_id</attr>
        </attrs>
        <primary_table>task</primary_table>
        <from>
            <table>task</table>
        </from>
        <where>
            <data_constraint>
                <constraint>
                    <left_operand>task.plan_start_dttm</left_operand>
                    <operator>gt</operator>
                    <right_operand>task.plan_end_dttm</right_operand>
                </constraint>
            </data_constraint>
        </where>
</hierarchy_select>

 

The error returned is: 

<metrix_response>
  <result type="Exception">
    <error>
      <system_error>
        <severity>ERROR</severity>
        <message>The following system level exception was generated by the application: String was not recognized as a valid DateTime.
Contact your system administrator for assistance. The server log or event log may contain more details about the error.</message>
      </system_error>
    </error>
  </result>
</metrix_response>

 

Appreciate any feedback 

4 replies

M
Rank
Userlevel 2
Badge +6

Hi @STRGoedhart 

is this for use in an xml business rule?

 

Thank You,

Morris

S
Rank
Badge +1

Hi @STRGoedhart 

is this for use in an xml business rule?

 

Thank You,

Morris

 

@Morris, This will be for a Scheduled Process

M
Rank
Userlevel 2
Badge +6

@STRGoedhart 

when you are testing are you testing via the xml poster or are you testing via schedule process? At face value it should work from a BR or schedule process, but if you try to test the expression in the poster it will fail.

 

Morris

SAMLK
Rank
Userlevel 4
Badge +12

Hi @STRGoedhart ,

Sample 1 : 

Expression annotations typically do not function as expected, particularly when validating datetime (dttm) fields. This is because the expression itself behaves as a string in the XML message, rather than the desired functionality. The XML message requires a dttm value as the right operand according to your example. As @Morris mentioned, the expression annotation directly works when it comes to a XML BR but it will not work if you are going to place that on a scheduled process. Therefore, it is necessary to modify the XML as demonstrated below.

<perform_batch>
  <perform_evaluate_expression>
    <parameters>
      <table_name>task</table_name>
      <keys>
        <key_item>
          <column_name>task_id</column_name>
          <column_value>245</column_value>
        </key_item>
      </keys>
      <expression>today()</expression>
    </parameters>
  </perform_evaluate_expression>
  <hierarchy_select>
    <attrs>
      <attr>task.task_id</attr>
    </attrs>
    <primary_table>task</primary_table>
    <from>
      <table>task</table>
    </from>
    <where>
      <data_constraint>
        <constraint>
          <left_operand>task.plan_start_dttm</left_operand>
          <operator>gt</operator>
          <right_operand xpath_node="//perform_evaluate_expression_result/response" />
        </constraint>
      </data_constraint>
    </where>
  </hierarchy_select>
</perform_batch>

 

Sample 02:

The operand elements can have an attribute named ‘is_column’. This is a true/false flag that defaults to false. When true, the inner text of the element needs to be [table.name]. Otherwise, it is a string value. Since you are passing a table column [table.name] value here, you need to modify your xml message as below.

<hierarchy_select>
  <attrs>
    <attr>task.task_id</attr>
  </attrs>
  <primary_table>task</primary_table>
  <from>
    <table>task</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>task.plan_start_dttm</left_operand>
        <operator>gt</operator>
        <right_operand is_column="true">task.plan_end_dttm</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>

 

https://www.linkedin.com/in/saranga-amaraweera-62418388

Reply


Terms & ConditionsCookie settings