KACE Product Support

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)),')')

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

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[[:>:]].*';
________________________________________________________

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!'

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

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

Got it, thanks again for your help!!!

John