Skip to main content

I want to update user_def13 with attachment_name. user_def13 is in the escalation table, but there is no direct relationship between the escalation and attachment tables. So, I created another table named c_escalation_extension.
I have written a business rule to insert esc_id, attachment_id, and attachment_name into c_escalation_extension. The insertion is working correctly. 

Update operation is not working. User_def13 is not updating with attachment_name

Below is the configuration I did for the update operation in business rule and custom process
 1- Primary table = ESCALATION
 2-execution type = all rule all value match
 3-execution point = after commit insert
 4- rule input 
     table = c_escalation_extension column = esc_id
             c_escalation_extension column = attachment_id
5-input parameter
    c_escalation_extension.esc_id(new) operator = equal value = blank
    c_escalation_extension.attacment_id(new) operator=equal value = blank
    
6- XML Query
<perform_exec_db_edit>
  <parameters>
    <sql_command><! CDATAs
      UPDATE escalation
      SET user_def13 = ce.attachment_name
      FROM escalation e
      INNER JOIN c_escalation_extension ce
        ON e.esc_id = ce.esc_id
      WHERE ce.attachment_id IS NOT NULL
        AND EXISTS (
          SELECT 1
          FROM attachment a
          WHERE a.attachment_id = ce.attachment_id
        )
    ]]></sql_command>
  </parameters>
</perform_exec_db_edit>

Please suggest if any changes required or suggest solution for this issue.

 

Find the attachment of business rule for your reference

Hi ​@aishwarya ,

 

I have a question, why are you trying to do the update using db edit? Why not just an update xml?  Have you confirmed that the business rule that is going to update ud13 is getting triggered? if it is getting triggered, does it evaluate as True?

Is there a reason you did not use a hierarchy select on the new table to find the value and then use an xpath in the xml to provide the value in an update message to update the escalation table?

But the first question of the day that needs to be answered is does the business rule get triggered and is it evaluating as True?

 

Regards,

Morris 

Hi ​@Morris ,

Thank you for your response

Yes, the business rule is triggering and returning a true value.

There is no reason not to use update_xml and hierarchy select. I have used them because I was referencing them from another XML query where update_db_edit was used.

Currently, when a new record is inserted, the user_def13 field of the previous record is being updated instead of the new record.

For example:
When data is inserted into the escalation table, an esc_id is generated, such as 123. For this record,user_def13 is not updated. However, when I perform another insert, a new record is generated, but the user_def13 field is updated in the previous record with esc_id 123 instead of the new one.

Please suggest if any changes required

Thanks in advance

​@aishwarya - which object is created first? I’m assuming the Escalation is in the database first and then when the attachment is created, you want to post that attachment name to a specific escalation id.

 

If so, I think you can do this without the middle table, and perhaps use an Xpath XML rule to locate the escalation id in the hierarchy select. If you know the criteria from which the attachment record needs to go and look, then a hierarchy select will get the escalation record.

 

Some assumptions here, but the escalation could be linked to a request or a task. So when the attachment is created, do you also get a record on request/task_attachment for example. If so you can use that entry to locate the escalation you need via the business rule and the hierarchy select should be able to get the escalation id to update.

I might have been able to word that better, but I hope it helps.

 

Ady

Reply


Terms & ConditionsCookie settings