@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_resulte@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

@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!

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

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_resultr@result_name='select_task_text01']/task_text/text_sequence" />
      <user_def1 xpath_node="//task_text_hierarchy_select_results@result_name='select_task_text01']/task_text/task/request/req_group" />
      <update />
    </task_text>
  </update_task_text>
</perform_batch>

Thanks for your help!