Skip to main content

Hi.

I need to update multiple contract statuses depending on another contract’s status.

The solution im trying to use is having a table I created named c_contract_link where I list contracts that should be cancelled when the primary contract gets cancelled.

Now im trying to mass update on the contract table using the values from the c_contract_link table.

 

 <perform_batch>
  <sequential_dependent>

<mass_update_contract result_name="link_select">
<hierarchy_select>
  <primary_table>c_contract_link</primary_table>
  <attrs>
    <attr>c_contract_link.linked_contract_id</attr>
  </attrs>
  <from>
    <table>contract</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>c_contract_link.contract_id</left_operand>
        <operator>eq</operator>
        <right_operand>@contract_id</right_operand>
      </constraint>
      <constraint>
        <left_operand>c_contract_link.cancel_with_primary</left_operand>
        <operator>eq</operator>
        <right_operand>Y</right_operand>
      </constraint>
    </data_constraint>
    <join_constraint>
      <constraint>
        <left_operand>contract.contract_id</left_operand>
        <operator>equi</operator>
        <right_operand>c_contract_link.contract_id</right_operand>
      </constraint>
    </join_constraint>
  </where>
</hierarchy_select>
  <contract>
    <contract_id xpath_node="//c_contract_link_hierarchy_select_result;@result_name='link_select']/c_contract_link/linked_contract_id" />
    <contract_version>1</contract_version>
    <contract_status>CANCEL</contract_status>
    <user_def5>@user_def5</user_def5>
    <cancelled_dt>@cancelled_dt</cancelled_dt>
    <update is_initialized="True"/>
  </contract>
  <transaction_size>100</transaction_size>
  <synchronous>Y</synchronous>
</mass_update_contract>

  </sequential_dependent>
</perform_batch>

 

 

This gives me an error:

<perform_batch_result>
  <mass_update_contract_result result_name="link_select">
    <result type="Exception">
      <error>
        <application_error>
          <severity>ERROR</severity>
          <message>The rows returned from the query are for the c_contract_link table but the mass update message is for the contract table.  Mass update cannot be performed.</message>
        </application_error>
      </error>
    </result>
  </mass_update_contract_result>
</perform_batch_result>

 

Is it possible to use the results from one table to mass update another table?

Hi ​@Fluffy 

These might help you

 

Mass Update for multiple rows | IFS Community

Hierarchy select with mass update | IFS Community

Conditionally execute perform_batch XML | IFS Community

 

Cheers!

Hi ​@Fluffy 

These might help you

 

Mass Update for multiple rows | IFS Community

Hierarchy select with mass update | IFS Community

Conditionally execute perform_batch XML | IFS Community

 

Cheers!

Thanx for the response.

Those dont answer my question.

​@Fluffy 

Try changing the select to be the contract, not the contract link.

You do not need to provide the ID as XPath, it is derived from the select itself.

Cheers!

If I do that, it just returns the contract_id twice. The problem is I need the linked contract id. The contract table will only return the contract id.

​@Fluffy 

According to your join constraint, it is the same value.

      <constraint>
        <left_operand>contract.contract_id</left_operand>
        <operator>equi</operator>
        <right_operand>c_contract_link.contract_id</right_operand>
      </constraint>

And c_contract_link is used for the where constraint.

Not sure what the problem you are facing is.

Cheers!

​@Shneor Cheshin

Im just not sure how I should format the hierarchy select to get the linked contract id from the c_contract_link table so that I can mass update the contract id according to the results of that select. I want to use this in a BR triggered by the contract table.

​@Fluffy 

Try the following (Did not run this, so might be some syntax issues)

<mass_update_contract>
  <hierarchy_select>
    <primary_table>contract</primary_table>
    <attrs>
      <attr>contract.contract_id</attr>
    </attrs>
    <from>
      <table>contract</table>
      <table>c_contract_link</table>
    </from>
    <where>
      <data_constraint>
        <constraint>
          <left_operand>c_contract_link.cancel_with_primary</left_operand>
          <operator>eq</operator>
          <right_operand>Y</right_operand>
        </constraint>
        <constraint>
          <left_operand>contract.contract_id</left_operand>
          <operator>eq</operator>
          <right_operand>@contract_id</right_operand>
        </constraint>
      </data_constraint>
      <join_constraint>
        <constraint>
          <left_operand>contract.contract_id</left_operand>
          <operator>equi</operator>
          <right_operand>c_contract_link.contract_id</right_operand>
        </constraint>
      </join_constraint>
    </where>
  </hierarchy_select>
  <contract>
    <user_def5>@user_def5</user_def5>
    <contract_status>CANCEL</contract_status>
    <contract_version>1</contract_version>
    <cancelled_dt>@cancelled_dt</cancelled_dt>
    <update />
  </contract>
  <transaction_size>1</transaction_size>
  <synchronous>Y</synchronous>
</mass_update_contract>

Cheers!

​@Shneor Cheshin 

This simply updates the primary contract, the one that takes the @contract_id value.

Is there a way to maybe use mass insert but force it to update instead perhaps?

Ok, I found a solution that seems to work. I have to use perform_mass_insert instead:

<perform_mass_insert>
  <parameters>
    <select>
     <hierarchy_select>
  <primary_table>c_contract_link</primary_table>
  <attrs>
    <attr>c_contract_link.linked_contract_id</attr>
  </attrs>
  <from>
    <table>contract</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>c_contract_link.contract_id</left_operand>
        <operator>eq</operator>
        <right_operand>@contract_id</right_operand>
      </constraint>
      <constraint>
        <left_operand>c_contract_link.cancel_with_primary</left_operand>
        <operator>eq</operator>
        <right_operand>Y</right_operand>
      </constraint>
    </data_constraint>
    <join_constraint>
      <constraint>
        <left_operand>contract.contract_id</left_operand>
        <operator>equi</operator>
        <right_operand>c_contract_link.contract_id</right_operand>
      </constraint>
    </join_constraint>
  </where>
</hierarchy_select>
    </select>
    <update>
      <update_contract>
        <contract>
          <contract_id xpath="linked_contract_id" />
          <contract_version>1</contract_version>
          <user_def5>@user_def5</user_def5>
          <contract_status>CANCEL</contract_status>
          <cancelled_dt>@cancelled_dt</cancelled_dt>
          <insert_update />
        </contract>
      </update_contract>
    </update>
  </parameters>
</perform_mass_insert>

​@Shneor Cheshin 

This simply updates the primary contract, the one that takes the @contract_id value.

Is there a way to maybe use mass insert but force it to update instead perhaps?

​@Fluffy 

  • That is exactly what your original XML is doing.
  • Mass Insert is not a baseline MPM. Unless you have a customisation for that, it will not work.

Cheers!

Ah yea its a customisation. Sorry. Wont work for other people.

But for other people, could someone post an example of a working script for mass update that pulls data from one table and uses the results to update another table?

 

Reply


Terms & ConditionsCookie settings