Skip to main content
Solved

Updating a field value to SYSDATE by XML business rule


Forum|alt.badge.img+6

Can someone suggest how can I update a field to current time(SYSDATE) by XML business rule?

Best answer by Saranga Amaraweera

Hi @TaTSouraD,

 

You can write either an expression in the XML message of your XML BR or get the datetime from a standard SQL view associated with custom metadata.

 

Scenario 01 - Using Expression in XML Message

Using an Expression in the XML Message. Here you need to add perfom_evaluate_expression perform message in the xml message within perform_batch message. In the expression you can mention any table since you generally need to get the current time (in my case I have used request table since I’m doing some modification to request screen). Then pass the key items (the primary key(s) ) as the parameters. Finally put the function now() to get get current combined date and time in the expression tag as below.

Then you need to pass that value from the xpath for the relevant field that you’re trying to update. The field should be a datetime field in this case.

<perform_batch>
    <sequential_dependent>
        <perform_evaluate_expression>
            <parameters>
                <table_name>request</table_name>
                <keys>
                    <key_item>
                        <column_name>request_id</column_name>
                        <column_value>@request_id</column_value>
                    </key_item>
                </keys>
               
<expression>NOW()</expression>
            </parameters>
        </perform_evaluate_expression>  

    <update_time_commit>
        <time_commit>
            <tc_id>@tc_id</tc_id>
            <actual_dttm
xpath_node="//perform_evaluate_expression_result/response"/>
            <update />
        </time_commit>
    </update_time_commit>
</sequential_dependent>
</perform_batch> 

 

Scenario 02 - Using Custom SQL View

You can create a custom sql view with any valid sql function like GETDATE() and then define that view as readonly in custom metadata. Finally you can pass the date from that view to relevant field through the xpath after a tabular or hierarchy select with in a perform_batch message.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

3 replies

Saranga Amaraweera
Superhero
Forum|alt.badge.img+22

Hi @TaTSouraD,

 

You can write either an expression in the XML message of your XML BR or get the datetime from a standard SQL view associated with custom metadata.

 

Scenario 01 - Using Expression in XML Message

Using an Expression in the XML Message. Here you need to add perfom_evaluate_expression perform message in the xml message within perform_batch message. In the expression you can mention any table since you generally need to get the current time (in my case I have used request table since I’m doing some modification to request screen). Then pass the key items (the primary key(s) ) as the parameters. Finally put the function now() to get get current combined date and time in the expression tag as below.

Then you need to pass that value from the xpath for the relevant field that you’re trying to update. The field should be a datetime field in this case.

<perform_batch>
    <sequential_dependent>
        <perform_evaluate_expression>
            <parameters>
                <table_name>request</table_name>
                <keys>
                    <key_item>
                        <column_name>request_id</column_name>
                        <column_value>@request_id</column_value>
                    </key_item>
                </keys>
               
<expression>NOW()</expression>
            </parameters>
        </perform_evaluate_expression>  

    <update_time_commit>
        <time_commit>
            <tc_id>@tc_id</tc_id>
            <actual_dttm
xpath_node="//perform_evaluate_expression_result/response"/>
            <update />
        </time_commit>
    </update_time_commit>
</sequential_dependent>
</perform_batch> 

 

Scenario 02 - Using Custom SQL View

You can create a custom sql view with any valid sql function like GETDATE() and then define that view as readonly in custom metadata. Finally you can pass the date from that view to relevant field through the xpath after a tabular or hierarchy select with in a perform_batch message.


Forum|alt.badge.img+6
  • Author
  • Sidekick (Partner)
  • 21 replies
  • April 7, 2021

Hi @Saranga Amaraweera,

 

Thank you. It solves my purpose.


Forum|alt.badge.img+13
  • Hero (Employee)
  • 124 replies
  • April 7, 2021

Hi @TaTSouraD ,

 

In addition, an expression can also be used using the @expression[] substitution. 

for example:

 

<update_request_event>
  <request_event>
    <request_id>@request_id</request_id>
    <sequence />
    <event_type>MANUAL</event_type>
    <user_def_dttm1>@expression[Now()]</user_def_dttm1>
    <insert_update is_initialized="False" />
  </request_event>
</update_request_event>

 

-Ruben


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings