I would like to use one of the UDF fields from the Request template and when running the ECO Generation it copy that field onto the Request. is this doable? if so what additional setup would be required as just adding the field to the UI for the request template did not do it.
Thanks,
Graeme
Hi Graeme,
You would also need to create a business rule that triggers on the ECO generation, inputs the UDF value and outputs it to the field on Request where you require it.
Generally speaking UDF fields are not hard coded to be copied across tables to retain the ability for these fields to be used for what ever purposes the user requires.
Kind regards,
Lee Pinchbeck
Thank you for the Reply
is there a standard business rule that runs off of the ECO Generation? if so which rule is it i didn’t see one? if not what type of Custom process would i create that would trigger off of the ECO Generation.
I am looking to Copy request_template.user_def8 to request.user_def8
Thanks in advance
Graeme
Hi Graeme,
There is no standard business rule that would perform this function, you would need to create a new one that would do this for you.
If you require assistance with Business rule creation I would recommend that you enquire for training on this area as it is an extremely versatile area of the system that would need a strong understanding of to ensure the rule functions as you expect.
Kind regards,
Lee Pinchbeck
I recommend using the METRIX_RUN_LOG table, and creating your custom business process in After Commit Update when the run_type = ‘ECO Generation’ and the run_status has changed to ‘COMPLETE’.
Thank you
For this business rule if i am running it off of the METRIX Run_log I will only have the Foregin Key that has the ECO_ID in it.
i can do a hierarchy_select against the REQUEST Table to get the Request Template and then provide back the REQUEST_TEMPLATE.USER_DEF8 field value but how would i update the all Requests now with that value.
Would i do another Hierachy_select against the request table for the ECO and do an update to the REQUEST.USER_DEF8 field? i have not used a Mass update before so not sure how i would set that up. so if you could provide some guidence that would be helpful.
if i try running the update statement manually in the XML Poster i get an error: “Attempt to update a record without providing all Primary Key information.”
<perform_batch>
<sequential_dependent>
<hierarchy_select max_rows="1">
<primary_table>request_template</primary_table>
<attrs>
<attr>request_template.user_def8</attr>
</attrs>
<from>
<table>request_template</table>
<table>request</table>
<table>metrix_run_log</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>metrix_run_log.run_type</left_operand>
<operator>eq</operator>
<right_operand>ECO Generation</right_operand>
</constraint>
<constraint>
<left_operand>metrix_run_log.foreign_key_char1</left_operand>
<operator>eq</operator>
<right_operand>@foreign_key_char1</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>request_template.request_template_id</left_operand>
<operator>equi</operator>
<right_operand>request.request_template_id</right_operand>
</constraint>
<constraint>
<left_operand>request.eco_id</left_operand>
<operator>equi</operator>
<right_operand>metrix_run_log.foreign_key_char1</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<Update_request regex_validated="true">
<request>
<update />
<eco_id>@foreign_key_char1</eco_id>
<user_def8>@request_template.user_def8</user_def8>
</request>
</Update_request>
</sequential_dependent>
</perform_batch>
You cannot use mixed case tags in XML messages for FSM. Your original <update/> tags had upper case ‘U’. In a standalone update, you need to provide the PK values. In your case, you would have needed a <request_id/> element to key the request you wanted to update. In a mass update, the PK values are not needed because there is an implicit loop iterating through the records in the result set.
You don’t need to query or qualify the metrix_run_log record because you already have it available from the transaction which triggered the custom business process.
Since I don’t have your data, I can’t really test this, but hopefully the following XML will help you through this step in your effort. I think you only need the one <mass_update/> message here.
Lastly, don’t forget to set force_select = Y for request.user_def8 in custom metadata if you haven’t already! This is important to prevent row concurrency (optimistic record locking) errors. You also might want to check the request table to see if there is an index on eco_id and consider adding one if there is not to prevent taxing the database with table scans of the request table, which I imagine is pretty large for you.
<mass_update_request>
<hierarchy_select result_name="ReqTemplateQuery01" max_rows="1">
<primary_table>request</primary_table>
<attrs>
<attr>request_template.user_def8</attr>
</attrs>
<from>
<table>request_template</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>eco_id</left_operand>
<operator>eq</operator>
<right_operand>@foreign_key_char1</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>request_template.request_template_id</left_operand>
<operator>equi</operator>
<right_operand>request.request_template_id</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<request>
<update/>
<user_def8 xpath_node="//request_template_hierarchy_select_resulti@result_name='ReqTemplateQuery01']/request/request_template/user_def8"/>
</request>
<transaction_size>10</transaction_size>
<synchronous>Y</synchronous>
</mass_update_request>
Hi
When i try to post this via the XML Poster it states that it updated a record but when i check the record it’s not update.
trying to debug further i get this when i do the Hierarchy select
<hierarchy_select result_name="ReqTemplateQuery01" max_rows="1">
<primary_table>request</primary_table>
<attrs>
<attr>request_template.user_def8</attr>
</attrs>
<from>
<table>request_template</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>request.eco_id</left_operand>
<operator>eq</operator>
<right_operand>767</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>request_template.request_template_id</left_operand>
<operator>equi</operator>
<right_operand>request.request_template_id</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<request_hierarchy_select_result result_name="ReqTemplateQuery01">
<request>
<metrix_row_num>1</metrix_row_num>
<request_id>227368</request_id>
<request_template_id>SCREENING SUPPORT</request_template_id>
<request_template>
<metrix_row_num>1</metrix_row_num>
<request_template_id>SCREENING SUPPORT</request_template_id>
<user_def8>F-01550</user_def8>
</request_template>
</request>
</request_hierarchy_select_result>
When i run it together with the mass update
<mass_update_request>
<hierarchy_select result_name="ReqTemplateQuery01" max_rows="1">
<primary_table>request</primary_table>
<attrs>
<attr>request_template.user_def8</attr>
</attrs>
<from>
<table>request_template</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>request.eco_id</left_operand>
<operator>eq</operator>
<right_operand>767</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>request_template.request_template_id</left_operand>
<operator>equi</operator>
<right_operand>request.request_template_id</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<request>
<update />
<user_def8 xpath_node="//request_hierarchy_select_resulte@result_name='ReqTemplateQuery01']/request/request_template/user_def8" />
</request>
<transaction_size>10</transaction_size>
<synchronous>Y</synchronous>
</mass_update_request>
<mass_update_request_result>
<response>
Mass update complete.
For update, 1 records were successfully updated and 0 were skipped because of errors.</response>
</mass_update_request_result>
What happens after the mass update when you run this SQL query?
SELECT
r.request_id,
r.eco_id,
r.user_def8
FROM
request r
WHERE
r.eco_id = ‘767’
Do you see the one (should there be more than one request for eco 767?) request record with user_def8 set to F-01550?
In this case there was only one Request that i was trying to creating with ECO Generation
And yes the user_def8 should be F-01550
| request_id | eco_id | user_def8 |
|---|---|---|
| 227368 | 767 | NULL |
Do you have user_def8 set force_select = ‘Y’ in custom metadata for the request table? I’m really not sure why the update shows it ran, but you don’t have the value in the record. I’d look for errors on the run log record for the mass update and/or in the server log.
yes it is set as force_select
Ok i will take a look at the Server logs
I managed to figure out what i had wrong in the business rule i needed to do 2 selects one to get the value from the request template and 1 to select the records to update.
Updated XML below.
<perform_batch>
<hierarchy_select result_name="ReqTemplateQuery01" max_rows="1">
<primary_table>request</primary_table>
<attrs>
<attr>request_template.user_def8</attr>
</attrs>
<from>
<table>request_template</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>request.eco_id</left_operand>
<operator>eq</operator>
<right_operand>@foreign_key_char1</right_operand>
</constraint>
</data_constraint>
<join_constraint>
<constraint>
<left_operand>request_template.request_template_id</left_operand>
<operator>equi</operator>
<right_operand>request.request_template_id</right_operand>
</constraint>
</join_constraint>
</where>
</hierarchy_select>
<mass_update_request>
<hierarchy_select>
<primary_table>request</primary_table>
<attrs>
<attr>request.user_def8</attr>
<attr>request.eco_id</attr>
<attr>request.request_id</attr>
</attrs>
<from>
<table>request</table>
</from>
<where>
<data_constraint>
<constraint>
<left_operand>request.eco_id</left_operand>
<operator>eq</operator>
<right_operand>@foreign_key_char1</right_operand>
</constraint>
</data_constraint>
</where>
</hierarchy_select>
<request>
<user_def8 xpath_node="//request_hierarchy_select_resultl@result_name='ReqTemplateQuery01']/request/request_template/user_def8" />
<update />
</request>
<transaction_size>100</transaction_size>
<synchronous>Y</synchronous>
</mass_update_request>
</perform_batch>
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.