Ticket Rule- auto populate name field broke after update
We have a ticket rule where, upon save, the requester name field is auto populated and after we updated from 6.4 to 7.1 (or it might have been the 7.0 update since they were done so close together) it quit working. The update query no longer shows any errors and says executed but it always updates 0 rows even though it shows 1 row selected. Here is the update query and the last run log.
Update Query
Update
HD_TICKET
JOIN USER ON USER.ID = HD_TICKET.CUSTOM_FIELD_VALUE9
JOIN USER_FIELD_VALUE USER_CUSTOM_1 on USER.ID = USER_CUSTOM_1.USER_ID
and USER_CUSTOM_1.FIELD_ID = 1
SET
HD_TICKET.CUSTOM_FIELD_VALUE9 = USER_CUSTOM_1.FIELD_VALUE,
HD_TICKET.SUBMITTER_ID = USER.ID
where (HD_TICKET.ID in (<TICKET_IDS>))
and HD_TICKET.CUSTOM_FIELD_VALUE9 is not null
and USER.ID = HD_TICKET.CUSTOM_FIELD_VALUE8
Last Run Log
06/29/2017 09:09:36> Starting: 06/29/2017 09:09:36 06/29/2017 09:09:36> Executing Select Query... 06/29/2017 09:09:36> selected 1 rows 06/29/2017 09:09:36> Executing Update Query... 06/29/2017 09:09:36> updated 0 rows 06/29/2017 09:09:36> Ending: 06/29/2017 09:09:36
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
I'm not sure what you are trying to accomplish here. You join based on the value in HD_TICKET.CUSTOM_FIELD_VALUE9 but then you change that value. Later in the where statement you limit to tickets where USER.ID = HD_TICKET.CUSTOM_FIELD_VALUE8. Do both custom fields 8 and 9 contain the user id for the requestor?
Comments:
-
the where statement only refers to values within the table not the form fields. The issue, I believe, is within the set section, because it finds and selects the information but it does not set the value on the form. But to answer your question value9 holds the user's full name on the K100 Ticket table and value8 holds the user's user name on the K100 Ticket table. This update is supposed to auto-populate the full name from the K1000 table to the corresponding field on the Ticket form. This ticket rule worked prior to updating to 7.1, but after would error. Now with the sql changes that were made there are no longer errors but now it just will not update the field. - cockrila 7 years ago
-
In the database USER.ID is the row ID of the user and you aren't matching on that. If you are trying to set CUSTOM_FIELD_VALUE9 to the user's full name, based on the user's user name in CUSTOM_FIELD_VALUE8 then your update statement can look like this:
UPDATE HD_TICKET
SET CUSTOM_FIELD_VALUE9 =
(SELECT FULL_NAME FROM USER WHERE USER_NAME = CUSTOM_FIELD_VALUE8)
WHERE HD_TICKET.ID in (<TICKET_IDS>)
You shouldn't need to add additional where conditions on the update statement, unless there is something wrong with your select statement and it is returning tickets that don't need the field set. - chucksteel 7 years ago-
but you are still using a where statement just in a different way? plus the one you are using is not checking to make sure value9 isn't empty or that the submitter id and value8 match. but regardless on how it is done i do believe it is something in the set section because it does show where it selects the information but it does not update it.
selected 1 rows 06/29/2017 09:09:36> Executing Update Query... 06/29/2017 09:09:36> updated 0 rows - cockrila 7 years ago -
Your ticket rule has a select query that determines which tickets will be acted upon, so there is no need to duplicate that check in the update query.
Your current update statement probably isn't updating anything because
a) Your join statement to the USER table won't match anything because USER.ID will be a number and CUSTOM_FIELD_VALUE9 contains a user's full name (and it hasn't been set yet)
b) the where clause only matches rows where CUSTOM_FIELD_VALUE9 is NOT NULL - chucksteel 7 years ago
