Is there any way to run a ticket rule only once per ticket, either automatically or manually?

I'm asking because I'm looking to concatenate data from one ticket field into another (kind of a recursive concatenate), ala:

SET CHILD.TITLE = CONCAT(CHILD.TITLE, ' (', PARENT.CUSTOM_FIELD_VALUE2, ')')

and it works fine when I run the rule, but I need to prevent it from running multiple times on the same ticket. Schedules would imply multiple runs. Same for "on Ticket Save", if a ticket is updated and saved multiple times.
___________________________________________________

dchristian helped me with the Parent / Child syntax here:
http://itninja.com/question/silent-uninstall-oracle-8-cilent8912&mpage=1&key=𔢑

but I'm currently stuck on using these rules on ticket save (<TICKET_IDS> wants to reference HD_TICKET.ID, which isn't called), so I figured I'd move this along and add any fixes back in when possible.
___________________________________________________

Here's the full queries I'm using:

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', CHILD.CUSTOM_FIELD_VALUE0 AS 'Child Location'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE PARENT.ID = CHILD.PARENT_ID

Works just fine, albeit applying to all child tickets with each run.

Thanks for your help!!!

John
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
When CHILD.TITLE has been change is there anything that is contstant in that change? For example could you have a parent keyword?:

CHILD.TITLE=CONCAT(LEFT(CHILD.TITLE,215), ' (Parent:',LEFT(PARENT.CUSTOM_FIELD_VALUE2,LENGTH(LEFTCHILD.TITLE)-),')')


If so then you would just add this to your where clause:

and CHILD.TITLE NOT RLIKE '^.+ [[.(.]]Parent:.*'


If you do have a flag then here's some bonus code for your statement to make sure the length doesn't exceed 255

SET CHILD.TITLE=
CONCAT(LEFT(CHILD.TITLE,GREATEST(195,255/*maxtitle*/-LENGTH(PARENT.CUSTOM_FIELD_VALUE2)-10 /*fixed str length*/)),
' (Parent:',LEFT(PARENT.CUSTOM_FIELD_VALUE2,GREATEST(40,255-LENGTH(CHILD.TITLE)-10)),')')
Answered 10/25/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
GillySpy,

Thanks very much for your help! Based on what the information you provided I feel that I should be able to get it working, so I'll play with it and post the results.

Thanks again and have a good one!

John
Answered 10/25/2011 by: jverbosk
Red Belt

Please log in to comment
0
GillySpy,

OK, thanks to your help it's working now. Here's my notes for anyone else that might find them helpful - also included the fixes on the CHILD references so the OTS rule works (per the Parent-Child Ticket Flow post you and dchristian helped me with).

Thanks again for all of your help!!!

John
________________________________________________________

Parent Ticket Data (User Name) to Child Tickets
________________________________________________________

Create rule in Queue 1 (IT Helpdesk)

Title:
Autopopulate Child Ticket Fields (User Name)

Order:
12

Notes:
Copies specified data from Parent ticket (in queue 2) to Child ticket (in queue 1) on ticket save of Child ticket, NOT RLIKE included in WHERE statement so subsequent CONCATs do not occur.

AppDeploy forum note:
PARENT.CUSTOM_FIELD_VALUE2 is where the HR person types in the new user's full name in the Parent ticket. This rule tacks this on the end of the Child ticket's title field on ticket save. Child tickets setup with titles for network add, computer setup & ERP system setup.

Frequency:
on Ticket Save
________________________________________________________

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE2 AS 'Parent New User Name', HD_TICKET.TITLE AS 'Child Title Field', HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE, ' (', PARENT.CUSTOM_FIELD_VALUE2, ')')
WHERE (((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.TITLE NOT RLIKE '^.+ [[.(.]].*') AND HD_TICKET.ID = <TICKET_IDS>)
________________________________________________________

Original queries - keep to show Parent & Child aliases:

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE2 AS 'Parent New User Name', CHILD.TITLE AS 'Child Title Field', CHILD.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET CHILD
WHERE PARENT.ID = CHILD.PARENT_ID

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET CHILD
SET CHILD.TITLE = CONCAT(CHILD.TITLE, ' (', PARENT.CUSTOM_FIELD_VALUE2, ')')
WHERE ((PARENT.ID = CHILD.PARENT_ID) AND CHILD.ID = <TICKET_IDS>)

* note - these will not work, just keeping them for reference
________________________________________________________

GillySpy's question:
When CHILD.TITLE has been changed is there anything that is contstant in that change? For example could you have a parent keyword?:

My answer:
Yes - constant characters would be "(" + "% User's Full Name %" + ")"

I'm always adding " (" to Child Title field, so can reference this, i.e.:

and CHILD.TITLE NOT RLIKE '^.+ [[.(.]].*'
________________________________________________________

Notes:

Compare mine & Gilly's examples:
SET CHILD.TITLE = CONCAT(CHILD.TITLE, ' (', PARENT.CUSTOM_FIELD_VALUE2, ')')
SET CHILD.TITLE = CONCAT(LEFT(CHILD.TITLE,215), ' (Parent:', LEFT(PARENT.CUSTOM_FIELD_VALUE2,LENGTH(LEFTCHILD.TITLE)-), ')')

LEFT -> refers to where to place CONCAT'd data ?
215 -> refers to length ?
' (Parent:' -> parent keyword
________________________________________________________

Researched RLIKE and found two similar example expressions:

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

If you are searching for literal parentheses, you have to enclose each parenthesis in brackets; otherwise, mySQL thinks they're part of the regular expression syntax. For instance:

WHERE phone REGEXP '(435)';
would return any phone numbers that have the sequence 435 in any part of the string, such as "1(801)555-4351". However:

WHERE phone REGEXP '[(]435[)]';
would return only phone numbers with (435), such as "1(435)555-5555".
________________________________________________________

http://www.webdeveloper.com/forum/showthread.php?t=90203

How does one do LIKE '% word %' query in RLIKE for some number of words?

SELECT * FROM `table` WHERE `column` RLIKE '.*[[:<:]]word[[:>:]].*';
________________________________________________________
Answered 10/27/2011 by: jverbosk
Red Belt

Please log in to comment
0
if you were looking for " word " exactly as you described (note the spaces) anywhere in a string then it would be:
RLIKE ' word '
Another way to write the same thing:
RLIKE '^.* word .*$'
But, if the intention of the spaces is that you wanted to find "word" but not if it was embedded in another word then could define that to mean where it is not preceeded nor followed by a letter but could be at the beginning or ending of the string
RLIKE '(^|[^A-z])word([^A-z]|$)'

e.g. here are some things that would fail with your example but work with this last one:
'mark my word'
'"word" is my favourite word' - wouldn't find either
'word to my brutha'
' word!'
Answered 10/27/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thanks for the info!

I used your expressions (below) as I wasn't sure what ^.+ did. I tested the rule and it is working as expected.

HD_TICKET.TITLE NOT RLIKE '^.+ [[.(.]].*'

If you don't mind clarifying what ^.+ does, I would appreciate it. I'm assuming it's something like "everything before ....". While researching, I noticed there are a lot of possible variations that could work, but wanted to use what you provided and that worked fine so I left it at that.

Thanks again!

John
Answered 10/27/2011 by: jverbosk
Red Belt

Please log in to comment
0
It can have two meanings. Search this for "^" for the definitive answer. I can't say it any better then they can.
http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Answered 10/27/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Got it, thanks again for your help!!!

John
Answered 10/27/2011 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity