/build/static/layout/Breadcrumb_cap_w.png

Service Desk Rule to run only once

Hi everyone,

I have been struggling with a custom rule to run only once per ticket.  The rule is meant to copy values from two fields on the parent ticket, and add this text to the title of the child ticket (separate queues).  I can get this to work correctly but each time the ticket is saved, the rule runs again and adds the text again.  I tried to set it so the rule checks for another custom field and only runs if the designated text is missing from it but I keep getting errors executing it.  


SELECT
PARENT.CUSTOM_FIELD_VALUE2,
PARENT.CUSTOM_FIELD_VALUE1,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.CUSTOM_FIELD_VALUE5 not like '%Title set by rule%'


UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE,' - ',PARENT.CUSTOM_FIELD_VALUE1,' ',PARENT.CUSTOM_FIELD_VALUE2)
HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Title set by rule'
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)


Custom 1 and 2 are First Name, Last Name and it works correctly without the dependency of Custom 5 which is an Owners Only field.  I would love to have the Custom 5 set as a checkbox but at this point anything that will work is great.

Thank you for taking the time to look at this.

Executing Select Query... 09/28/2021 08:23:43> selected 1 rows 09/28/2021 08:23:43> Executing Update Query... 09/28/2021 08:23:43> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Title set by rule' WHERE ((PARENT.ID = HD_TICKE' at line 6] in EXECUTE("UPDATE HD_TICKET PARENT, HD_TICKET SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE,' - ',PARENT.CUSTOM_FIELD_VALUE1,' ',PARENT.CUSTOM_FIELD_VALUE2) HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Title set by rule' WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = 1493)")


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: Hobbsy 2 years ago
Red Belt
0

The solution we use is simple, we refer to it as a switch field. Simply create a custom field with a default value, "1" usually works well. Then for the rule you want to run once include the criteria "Where the custom field is equal to one" and in the update statement, set the custom field value to "2". Using this the rule will only run once. 


Once you have configured the rules and they work, simply hide the custom field


Comments:
  • Thanks for the suggestion Hobbsy. I created the custom field and set it to 1 but there is something wrong with my code. Here is what my current SELECT and UPDATE queries look like:

    SELECT
    PARENT.CUSTOM_FIELD_VALUE2,
    PARENT.CUSTOM_FIELD_VALUE1,
    HD_TICKET.TITLE,
    HD_TICKET.ID
    FROM
    HD_TICKET PARENT, HD_TICKET
    WHERE
    PARENT.ID = HD_TICKET.PARENT_ID
    and HD_TICKET.CUSTOM_FIELD_VALUE5 = '1'
    and HD_TICKET.HD_QUEUE_ID = 1

    UPDATE
    HD_TICKET PARENT,
    HD_TICKET
    SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE,' - ',PARENT.CUSTOM_FIELD_VALUE1,' ',PARENT.CUSTOM_FIELD_VALUE2)
    SET ( HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' )
    WHERE
    ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

    And here is my error:
    10/06/2021 11:13:12> Starting: 10/06/2021 11:13:12 10/06/2021 11:13:12> Executing Select Query... 10/06/2021 11:13:12> selected 1 rows 10/06/2021 11:13:12> Executing Update Query... 10/06/2021 11:13:12> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET ( HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' ) WHERE ((PARENT.ID = HD_TICKET.PARENT' at line 5] in EXECUTE("UPDATE HD_TICKET PARENT, HD_TICKET SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE,' - ',PARENT.CUSTOM_FIELD_VALUE1,' ',PARENT.CUSTOM_FIELD_VALUE2) SET ( HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' ) WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = 1493) ") 10/06/2021 11:13:12> Ending: 10/06/2021 11:13:12

    I tested by manually changing the "switch" field to 2 and the rule works as expected as long as I don't have the line below. Removing it, completes the Rule but obviously doesn't change the value to 2.

    SET HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' - rbaranowicz 2 years ago
Posted by: Hobbsy 2 years ago
Red Belt
0

Top Answer

I’m not sure you can use SET twice in the update statement, try just using a , to set the second value or if not an AND like this 
SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE,' - ',PARENT.CUSTOM_FIELD_VALUE1,' ',PARENT.CUSTOM_FIELD_VALUE2) AND( HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' )

Comments:
  • Thank you! I ended up using the , and I removed () from ( HD_TICKET.CUSTOM_FIELD_VALUE5 = '2' ). - rbaranowicz 2 years ago
    • Good Work!! - Hobbsy 2 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ