Skip to main content
Solved

FSM6 Business Rules Table Joins

  • November 16, 2023
  • 4 replies
  • 96 views
M
Sidekick (Customer)
Forum|alt.badge.img+9

Hi,

 

Trying to create a business rule that fires from table TASK_TEXT, but need a value from the REQUEST table.  Tried to set up a join in the custom metadata, but wont let me map from TASK_TEXT to REQUEST?  How should i go about this?

Tried to do it another way by setting up a USER_DEF field in Task text to store the REQUEST_ID but cant see how to get it to do that?

 

Any suggestions please?

 

Martin

Best answer by AdrianEgley

@MartinF 

I always find if the joins in the metadata cannot be done, then another solution would be to use a xPath in a business rule.


Example below;

<perform_batch>
<hierarchy_select result_name="select_task_text01">
  <primary_table>task_text</primary_table>
  <attrs>
    <attr>task_text.task_id</attr>
    <attr>request.cust_prob_descr</attr>
  </attrs>
  <from>
    <table>task</table>
    <table>request</table>
  </from>
  <where>
    <join_constraint>
      <constraint>
        <left_operand>task_text.task_id</left_operand>
        <operator>eq</operator>
        <right_operand>task.task_id</right_operand>
      </constraint>
      <constraint>
        <left_operand>task.request_id</left_operand>
        <operator>eq</operator>
        <right_operand>request.request_id</right_operand>
      </constraint>
    </join_constraint>
    <data_constraint>
      <constraint>
        <left_operand>task_text.task_id</left_operand>
        <operator>eq</operator>
        <right_operand>6475107</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>
    <update_request_text>
        <request_text>
            <request_id xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/request_id"/>
            <text_line_code>TECH</text_line_code>
            <text xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/request/cust_prob_descr" xpath_modifier=" is from the Request Table"/>
        
            <insert/>
        </request_text>
    </update_request_text>
</perform_batch>

 

 

Using the record in the task_text to get to task and then request. The update in the batch will then use the data obtained on the hierarchy select to allow you to use information stored on the request in a business rule triggered from the task_text table.

There’s probably a few ways this can be done, and maybe some more graceful than this.

But I hope it helps.

 

Ady

4 replies

AdrianEgley
Hero (Customer)
Forum|alt.badge.img+14
  • AdrianEgley
  • Hero (Customer)
  • Answer
  • November 16, 2023

@MartinF 

I always find if the joins in the metadata cannot be done, then another solution would be to use a xPath in a business rule.


Example below;

<perform_batch>
<hierarchy_select result_name="select_task_text01">
  <primary_table>task_text</primary_table>
  <attrs>
    <attr>task_text.task_id</attr>
    <attr>request.cust_prob_descr</attr>
  </attrs>
  <from>
    <table>task</table>
    <table>request</table>
  </from>
  <where>
    <join_constraint>
      <constraint>
        <left_operand>task_text.task_id</left_operand>
        <operator>eq</operator>
        <right_operand>task.task_id</right_operand>
      </constraint>
      <constraint>
        <left_operand>task.request_id</left_operand>
        <operator>eq</operator>
        <right_operand>request.request_id</right_operand>
      </constraint>
    </join_constraint>
    <data_constraint>
      <constraint>
        <left_operand>task_text.task_id</left_operand>
        <operator>eq</operator>
        <right_operand>6475107</right_operand>
      </constraint>
    </data_constraint>
  </where>
</hierarchy_select>
    <update_request_text>
        <request_text>
            <request_id xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/request_id"/>
            <text_line_code>TECH</text_line_code>
            <text xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/request/cust_prob_descr" xpath_modifier=" is from the Request Table"/>
        
            <insert/>
        </request_text>
    </update_request_text>
</perform_batch>

 

 

Using the record in the task_text to get to task and then request. The update in the batch will then use the data obtained on the hierarchy select to allow you to use information stored on the request in a business rule triggered from the task_text table.

There’s probably a few ways this can be done, and maybe some more graceful than this.

But I hope it helps.

 

Ady

Shneor Cheshin
Superhero (Employee)
Forum|alt.badge.img+28
  • Shneor Cheshin
  • Superhero (Employee)
  • November 18, 2023

@MartinF 

I am not 100% sure what you are trying to achieve.

The request ID is available on the TASK table. If TASK_EXT is your task extension table there should be a relationship between task and task_ext and the request id should be available for you.

Cheers!

M
Sidekick (Customer)
Forum|alt.badge.img+9
  • MartinF
  • Author
  • Sidekick (Customer)
  • November 21, 2023

Hi All,

 

Thanks for the replies, only just been able to get back onto this issue.

 

What i am ulitmately trying to achieve is to fire a BR on TASK_TEXT but pull in field REQUEST.REQ_GROUP onto the BR so that we can send out different notifications based upon the Request Sub Group, so the join to TASK is not enough unfortunately

 

@AdrianEgley thansk for the detailed example, ill try and work through it and apply it to my BR

 

Martin

M
Sidekick (Customer)
Forum|alt.badge.img+9
  • MartinF
  • Author
  • Sidekick (Customer)
  • November 21, 2023

Hi All,

Solution using @AdrianEgley example:

 

<perform_batch>
  <hierarchy_select result_name="select_task_text01">
    <primary_table>task_text</primary_table>
    <attrs>
      <attr>task_text.task_id</attr>
      <attr>request.req_group</attr>
    </attrs>
    <from>
      <table>task</table>
      <table>request</table>
    </from>
    <where>
      <join_constraint>
        <constraint>
          <left_operand>task_text.task_id</left_operand>
          <operator>eq</operator>
          <right_operand>task.task_id</right_operand>
        </constraint>
        <constraint>
          <left_operand>task.request_id</left_operand>
          <operator>eq</operator>
          <right_operand>request.request_id</right_operand>
        </constraint>
      </join_constraint>
      <data_constraint>
        <constraint>
          <left_operand>task_text.task_id</left_operand>
          <operator>eq</operator>
          <right_operand>@task_id</right_operand>
        </constraint>
        <constraint>
          <left_operand>task_text.text_sequence</left_operand>
          <operator>eq</operator>
          <right_operand>@text_sequence</right_operand>
        </constraint>
      </data_constraint>
    </where>
  </hierarchy_select>
  <update_task_text>
    <task_text>
      <task_id xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/task_id" />
      <text_sequence xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/text_sequence" />
      <user_def1 xpath_node="//task_text_hierarchy_select_result[@result_name='select_task_text01']/task_text/task/request/req_group" />
      <update />
    </task_text>
  </update_task_text>
</perform_batch>

 

Thanks for your help!

Terms & ConditionsCookie settingsAccessibility statement